Query Optimization & EXPLAIN ANALYZE

Mental model

The PostgreSQL Query Planner translates SQL declarations into optimal execution node trees (Seq Scan, Index Scan, Bitmap Index Scan, Nested Loop, Hash Join, Merge Join) based on cost estimations (random_page_cost, seq_page_cost, table statistics).

Theory

Using EXPLAIN (ANALYZE, BUFFERS) executes the query and reports actual runtime duration, row counts, and shared memory buffer hits/reads (Buffers: shared hit=42 read=5).

Alternatives and trade-offs

• Sequential Scan: Reads every page in the table heap. Fast for small tables; catastrophic for multi-million row tables missing indexes.
• Index Scan: Reads index pages first, then fetches heap pages. Best for high selectivity.
• Bitmap Index Scan: Collects matching tuple locations into a memory bitmap before fetching heap pages in physical disk order, optimizing random I/O.

Failure modes and misconceptions

1. Stale Table Statistics: Outdated pg_statistic data leads the planner to miscalculate row estimates by orders of magnitude, choosing slow Nested Loops instead of Hash Joins.
2. Ignoring Buffers: shared read: Relying solely on execution time hides cold cache I/O latency problems. Always inspect shared read counts.

Decision scenario

When EXPLAIN ANALYZE shows a large disparity between rows=1 (estimated) and rows=50000 (actual), execute ANALYZE table_name to refresh statistics and correct planner estimations.

Learning outcomes

• Interpret EXPLAIN (ANALYZE, BUFFERS) execution plan trees.
• Distinguish between Sequential Scan, Index Scan, and Bitmap Index Scan.
• Resolve cardinality estimation errors by updating database table statistics.

Trade-offs

EXPLAIN (ANALYZE) provides exact execution metrics by running the query live, but mutates state if executed on UPDATE or DELETE statements without a rolling transaction ROLLBACK.