PostgreSQL deep dive — EXPLAIN · indexes

Slow SELECTs are mostly an index problem. Reading EXPLAIN solves half.

1. EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

Index Scan — index used ✓
Seq Scan — full table scan ✗
actual time — real time

2. Indexes that silently don't work

WHERE LOWER(email) = 'a@b.c'         -- no. Use functional index
WHERE name ILIKE '%kim%'             -- no. Use pg_trgm GIN
WHERE user_id::text = '123'          -- no. Match the type
WHERE a = 1 OR b = 2                 -- use UNION or composite

3. Composite indexes — column order

CREATE INDEX ON posts (user_id, created_at DESC);

Left-to-right prefix matters:

WHERE user_id = 1                         -- ✓
WHERE user_id = 1 AND created_at > ...    -- ✓
WHERE created_at > ...                    -- ✗

Prefix the equality / high-selectivity column.

4. Covering index

CREATE INDEX ON posts (user_id, created_at DESC) INCLUDE (title);

Enables Index Only Scan.

5. Partial index

CREATE INDEX idx_published_posts ON posts (created_at DESC)
WHERE published = true;

If 95% is published = true, smaller and faster.

6. GIN / GiST

Index	For
btree	default (eq · range · sort)
hash	equality only
GIN	arrays · JSONB · trigram · tsvector
GiST	PostGIS · ranges
BRIN	huge time-series tables

CREATE INDEX ON posts USING gin (tags);
CREATE INDEX ON posts USING gin (content gin_trgm_ops);

7. ANALYZE

Runs via autovacuum, but manual after large changes:

ANALYZE posts;

8. Slow query log

postgresql.conf:

log_min_duration_statement = 1000

Measure before tuning.

9. pg_stat_statements

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

Find the worst offenders.

10. Gotchas

Too many indexes → slow writes
UUID btree → page fragmentation (consider UUID v7)
Nullable columns with mostly NULL → partial index
Skipping VACUUM → stale stats, bad plans

Closing

"Slow query → EXPLAIN → check index" is the tuning mantra. Measure, don't guess.

02-multi-pool-orchestration