The Query Planner

THE QUERY PLANNER (06) -- the gambler inside the engine SQL says WHAT, never HOW. for one 3-table join there are thousands of legal HOWs, and the spread between best and worst is 10000x. the planner bets on one, using statistics instead of your data. when a query is mysteriously slow, you are almost always losing THIS bet. 1 . WHAT THE PLANNER KNOWS (pg_statistic, via ANALYZE) per column, from a random sample: n_distinct how many different values most_common_vals + their frequencies histogram_bounds equal-height buckets correlation physical vs logical order it never reads your table to plan -- only this sketch. selectivity = the guess WHERE plan='pro' -> mcv says 4% -> ~40k rows WHERE created > $1 -> histogram -> ~2% rows WHERE a=1 AND b=2 -> 4% x 3% (INDEPENDENCE ASSUMED -- often the bug!) correlated columns fool it: CREATE STATISTICS fixes. stale stats = bad bets bulk-loaded a million rows and the planner still thinks the table is tiny? seq scan of doom. ANALYZE after bulk loads; autovacuum usually covers the rest. (the DBA's 'have you tried ANALYZE' = our 'have you tried turning it off and on') 2 . THE MENU: SCANS SEQ SCAN read every page, in order. wins when you want >5-10% of the table. INDEX SCAN b-tree walk -> heap hop per row. wins for FEW rows; random I/O tax. BITMAP SCAN the compromise: collect ctids from index(es), sort by page, sweep heap in page order. the crossover is COST math: seq_page_cost=1 vs random_page_cost=4 (a spinning-disk relic! on shop's NVMe set it ~1.1, or the planner will irrationally fear indexes). 3 . THE MENU: JOINS NESTED LOOP for each outer row, probe inner (ideally via index). unbeatable for TINY outer sides. HASH JOIN build hash on small side, probe with big. the OLTP workhorse. needs work_mem! MERGE JOIN both sides sorted -> zip. wins when sort order is free (index, DSA-07 callback). join ORDER matters more: (A x B) x C vs A x (B x C) -- the planner explores the orders via dynamic programming (DSA-11!), genetic search (GEQO) past 12 tables. 4 . READING THE BET SLIP: EXPLAIN (ANALYZE, BUFFERS) Hash Join (cost=1.2..905 rows=210 width=48) (actual time=0.1..48 rows=180200 loops=1) -> Seq Scan on orders (cost=0..612 rows=42000) (actual rows=42011) -> Hash (cost=1.1..1.1 rows=10) Buckets: 1024 Memory: 9kB -> Seq Scan on plans (rows=10) (actual rows=10) Buffers: shared hit=3120 read=904 Execution Time: 49.8 ms the ONLY line that matters first: estimated rows=210 vs ACTUAL rows=180200. an 858x miss -- every choice downstream of a bad estimate is built on sand. the debug loop: 1. find the deepest node where estimate and actual diverge wildly 2. ask WHY the stats are wrong (stale? correlated columns? skew? param sniffing?) 3. fix the INPUT (analyze, extended stats, rewrite), not the planner's choice. pg has no query hints ON PURPOSE -- a 25-year-old philosophical fork (Oracle/SQL Server chose hints). THE ONE THING the planner is a gambler that never sees the cards -- only a statistical sketch of your table. slow queries are almost never 'postgres is slow'; they are 'the sketch was wrong'. learn to read estimate-vs-actual and you can fix in minutes what others tune around for weeks.