Indexes Deep Dive

-- See index usage statistics
SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan AS times_used,
  idx_tup_read AS rows_read,
  idx_tup_fetch AS rows_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes (candidates for removal)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index on (user_id, created_at, status)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC, status);

-- ✅ Uses index (filters on leading column)
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2024-01-01';
SELECT * FROM orders WHERE user_id = 42 AND created_at > '2024-01-01' AND status = 'pending';

-- ❌ Cannot use this index (skips leading column)
SELECT * FROM orders WHERE created_at > '2024-01-01';
SELECT * FROM orders WHERE status = 'pending';

-- ✅ Index-only scan if all needed columns are in the index
SELECT user_id, created_at, status FROM orders WHERE user_id = 42;

-- Hash index for pure equality lookups on UUID
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);

-- Only useful for:
SELECT * FROM sessions WHERE session_token = 'abc123...';

-- NOT useful for:
-- SELECT * FROM sessions WHERE session_token > 'abc'; -- won't use hash

-- Full-text search
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgresql & mvcc');

-- jsonb containment
CREATE INDEX idx_events_payload ON events USING GIN (payload);
SELECT * FROM events WHERE payload @> '{"type": "purchase", "amount_gt": 100}';

-- Array containment
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

-- Trigram (pg_trgm) — enables LIKE '%substring%' with index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%john%';  -- uses the GIN index!

-- PostGIS: find restaurants within 5km
CREATE INDEX idx_restaurants_location ON restaurants USING GIST (location);
SELECT name, ST_Distance(location, ST_MakePoint(-73.99, 40.73)::geography) AS dist
FROM restaurants
WHERE ST_DWithin(location, ST_MakePoint(-73.99, 40.73)::geography, 5000)
ORDER BY location <-> ST_MakePoint(-73.99, 40.73)::geography;

-- Range exclusion constraint (prevent overlapping bookings)
CREATE TABLE bookings (
  id serial PRIMARY KEY,
  room_id int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- inet containment
CREATE INDEX idx_logs_ip ON access_logs USING GIST (client_ip inet_ops);
SELECT * FROM access_logs WHERE client_ip << '192.168.1.0/24';

-- Perfect use case: append-only events table
-- Data is inserted chronologically, so created_at correlates with physical order
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at)
  WITH (pages_per_range = 128);

-- Index size comparison on 100M row table:
-- B-tree on created_at: ~2 GB
-- BRIN on created_at:   ~100 KB

-- Query uses BRIN to skip irrelevant block ranges
SELECT * FROM events
WHERE created_at BETWEEN '2024-03-01' AND '2024-03-02';

-- Check correlation (1.0 = perfect physical ordering)
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
-- correlation close to 1.0 or -1.0 → BRIN will work well

-- Only index active orders (90% of queries filter on status = 'pending')
CREATE INDEX idx_orders_pending ON orders(user_id, created_at)
  WHERE status = 'pending';

-- Only index non-deleted records (soft delete pattern)
CREATE INDEX idx_users_active ON users(email)
  WHERE deleted_at IS NULL;

-- Partial unique: email must be unique among active users only
CREATE UNIQUE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;
-- Allows: same email in deleted records (re-registration)

-- Query MUST include the partial index condition to use it:
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';  -- ✅ uses index
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';  -- ❌ cannot use index

-- Case-insensitive email lookup
CREATE INDEX idx_users_email_lower ON users(lower(email));
-- Query must use lower():
SELECT * FROM users WHERE lower(email) = 'user@example.com';  -- ✅ uses index
SELECT * FROM users WHERE email = 'user@example.com';          -- ❌ different expression

-- Index on extracted JSON field
CREATE INDEX idx_events_type ON events((payload->>'type'));
SELECT * FROM events WHERE payload->>'type' = 'purchase';

-- Index on date part of timestamp
CREATE INDEX idx_orders_date ON orders(date(created_at));
SELECT * FROM orders WHERE date(created_at) = '2024-03-15';

-- Without INCLUDE: must visit heap to get 'status' and 'total'
CREATE INDEX idx_orders_user ON orders(user_id);
SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Index scan + heap fetch for status and total

-- With INCLUDE: index-only scan — no heap access needed
CREATE INDEX idx_orders_user_covering ON orders(user_id) INCLUDE (status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 42;
-- Index-only scan — all data in the index

-- INCLUDE columns are NOT usable for search predicates:
SELECT * FROM orders WHERE status = 'pending';  -- ❌ cannot use INCLUDE column for filtering

-- Basic unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Multi-column unique (combination must be unique)
CREATE UNIQUE INDEX idx_subscriptions_user_plan ON subscriptions(user_id, plan_id);

-- Partial unique: unique only among non-deleted records
CREATE UNIQUE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;
-- Allows multiple deleted users with same email, but only one active

-- Build index without blocking writes (takes longer but no downtime)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- Rebuild a bloated index without lock
REINDEX INDEX CONCURRENTLY idx_orders_status;

-- Check index bloat
SELECT
  schemaname, tablename, indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;