Staff Prep 03: Postgres Indexing Strategy — What, When, and in What Order

-- B-tree supports these efficiently
SELECT * FROM users WHERE email = 'user@example.com';       -- equality
SELECT * FROM orders WHERE created_at > '2026-01-01';       -- range
SELECT * FROM products WHERE name LIKE 'Apple%';            -- prefix

-- B-tree does NOT help with these
SELECT * FROM products WHERE name LIKE '%phone';             -- suffix match
SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- without expression index

-- Fix: expression index for case-insensitive email lookup
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com'; -- now uses index

-- Query pattern: filter by status (equality), then range on date, then sort
SELECT * FROM orders
WHERE status = 'pending'          -- equality
  AND created_at > '2026-01-01'   -- range
ORDER BY created_at DESC          -- sort
LIMIT 20;

-- Wrong index order (range before sort)
CREATE INDEX idx_wrong ON orders (created_at, status);
-- This index cannot be used for the ORDER BY without a sort step

-- Correct index order
CREATE INDEX idx_orders_status_date
    ON orders (status, created_at DESC);
-- Equality (status) first, then range+sort (created_at)
-- Postgres can scan this index in order and avoid a Sort node

EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01'
ORDER BY created_at DESC LIMIT 20;
-- -> Index Scan using idx_orders_status_date (no Sort node!)

-- Full index: indexes ALL 10M rows
CREATE INDEX idx_orders_status_full ON orders (status, created_at);

-- Partial index: indexes only the 200k pending rows
CREATE INDEX idx_orders_pending ON orders (created_at DESC)
    WHERE status = 'pending';

-- Query that benefits from partial index
SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
-- Uses idx_orders_pending — 50x smaller, faster scan, fits in shared_buffers

-- Another common use: non-null partial index
CREATE INDEX idx_users_unverified_email ON users (email)
    WHERE verified_at IS NULL;
-- Only indexes users who haven't verified — a small subset

-- Without INCLUDE: index scan + heap fetch for every row
CREATE INDEX idx_orders_user ON orders (user_id);

SELECT user_id, amount, created_at FROM orders WHERE user_id = 42;
-- -> Index Scan using idx_orders_user
-- -> Heap fetches for every row (to get amount, created_at)

-- With INCLUDE: index only scan, no heap fetch
CREATE INDEX idx_orders_user_covering
    ON orders (user_id)
    INCLUDE (amount, created_at);

SELECT user_id, amount, created_at FROM orders WHERE user_id = 42;
-- -> Index Only Scan using idx_orders_user_covering
-- -> Zero heap fetches (amount and created_at are in the index)

-- Check visibility map for Index Only Scan efficiency
VACUUM orders;   -- updates visibility map so index only scan is fully effective

-- Find unused indexes (from pg_stat_user_indexes)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,       -- number of times this index was used
    idx_tup_read,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop indexes with 0 scans (after confirming they are not used elsewhere)
-- Note: reset stats after major traffic pattern changes with pg_stat_reset()

-- CONCURRENTLY: builds index without locking writes
-- Takes longer but does not block INSERT/UPDATE/DELETE
CREATE INDEX CONCURRENTLY idx_orders_user_covering
    ON orders (user_id)
    INCLUDE (amount, created_at);

-- Note: CONCURRENTLY cannot be run inside a transaction block
-- If it fails, it leaves an INVALID index — check and clean up:
SELECT indexname, pg_get_indexdef(indexrelid)
FROM pg_indexes
WHERE tablename = 'orders';

-- Drop and retry if invalid
DROP INDEX CONCURRENTLY idx_orders_user_covering;