Diff v1 → v2

  # Postgres Query Planning & EXPLAIN
  
  > **When to use it:** a query is slow and you need to know *why* — reading an `EXPLAIN`
  > plan, fixing bad row estimates, `work_mem` spills, a plan that ignores your index, or an
- > occasionally-slow prepared statement. Companion to [[Push Compute to the Datastore]]
+ > occasionally-slow prepared statement. Companion to [[push-compute-to-datastore|Push Compute to the Datastore]]
  > ("verify with EXPLAIN"); this is how.
  
  Offloading only wins if the planner actually does the work efficiently. **Fix the estimate
  before you touch indexes or join strategy** — most "wrong plan" bugs are really wrong row
  counts. GUC defaults below are for PG16/17; version-gated features are flagged.[^explain]
  
  ## 1. Reading `EXPLAIN (ANALYZE, BUFFERS)`
  
  **The #1 misread: `actual time` and `rows` are per-loop averages.** True cost of a node =
  `actual_time × loops`. A 0.003ms node at `loops=50000` can dominate. Parent times/buffers
  include children.
  
  **Scans:**
  - **Seq Scan** — whole table. Fine for small tables or when most rows match.
  - **Index Scan** — one index, random heap I/O per match. Wins when `pg_stats.correlation` is
    high (rows physically clustered).
  - **Bitmap Index + Bitmap Heap Scan** — the middle ground: build a bitmap of matching pages,
    visit them in *physical order* (random → sequential I/O). The planner picks this on low
    correlation (e.g. a UUID PK) or when combining two single-column indexes (`BitmapAnd`/
    `BitmapOr`). A bitmap exceeding `work_mem` degrades to **lossy** (whole-page recheck) —
    watch `Lossy Heap Blocks` / `Rows Removed by Index Recheck`.
  - **Index Only Scan** — skips the heap via the visibility map. `Heap Fetches: 0` = good;
    rising = page churn outrunning autovacuum's VM maintenance (tune autovacuum, not the index).
  
  **Joins:**
  - **Nested Loop** — cheap when one side is small *or* there's an index on the inner join key
    (becomes a per-row index scan). Degrades O(outer × inner) when both are large/unindexed.
  - **Hash Join** — builds a hash on the smaller side. `Batches: 1` = in-memory; `Batches > 1`
    = spilled to disk (slower — raise `work_mem`/`hash_mem_multiplier`).
  - **Merge Join** — needs both inputs sorted; free if an index already delivers the order.
  - **Memoize** (PG14+) — caches inner results of a nested loop; watch `Hits`/`Misses`/
    `Evictions` (high evictions + low hits = thrashing). Costing bug in 14.0–14.3, fixed 14.4.
  
  **Aggregation/sort:** Hash Aggregate (unsorted input, spills to disk PG13+ — watch `Batches`/
  `Disk Usage`) vs Group Aggregate (sorted input). **Incremental Sort** (PG13+) sorts only the
  non-presorted suffix when input arrives partly ordered — big with `LIMIT`.
  
  **Key fields:** `rows` estimate-vs-actual skew of 10×+ → **stats problem, fix that first**.
  `Rows Removed by Filter` ≫ returned → a more selective/composite index could push the
  predicate into the index. `BUFFERS`: `shared hit`=cache (cheap), `read`=disk.
  
  ## 2. Statistics — the root of most bad plans
  
  `ANALYZE` *samples*, it doesn't scan everything, and autoanalyze fires on a **row-count**
  trigger (`50 + 0.1×reltuples`), asynchronously. **Do run `ANALYZE` manually** right after: a
  bulk load/`COPY` (a query before autoanalyze fires can get a catastrophic plan), a
  `CREATE STATISTICS` (empty until analyzed), and on **partitioned-table parents** (autovacuum
  does *not* maintain their rolled-up stats).[^stats]
  
  Read `pg_stats` (not superuser-only `pg_statistic`): `n_distinct` (positive = fixed category
  count; negative = fraction scaling with size, `-1` = unique), `most_common_vals`,
  `histogram_bounds`, `correlation`. Raise granularity **per column, not globally**:
  `ALTER TABLE t ALTER COLUMN status SET STATISTICS 500; ANALYZE t;`
  
  **Extended statistics — the column-independence fix.** The planner assumes WHERE clauses are
  independent and *multiplies* selectivities, badly under-estimating correlated columns.
  `WHERE city='Lyon' AND country='France'`: est 19, actual 5816 → wrong-shaped plan. After
  `CREATE STATISTICS s (dependencies) ON country, city FROM t; ANALYZE t;` the estimate is
  correct and the query went **1840ms → 19ms** in one documented case.[^extstats]
  
  | Kind | Since | Use for |
  |---|---|---|
  | `dependencies` | PG10 | combined WHERE selectivity, **equality/IN only** |
  | `ndistinct` | PG10 | multi-column `GROUP BY`/`DISTINCT` cardinality |
  | `mcv` | PG12 | specific over/under-represented value *combinations*, some ranges |
  
  Extended stats are **single-table only** — they do **not** help join estimates.
  
  ## 3. work_mem — in-memory vs disk spill
  
  `work_mem` gates each sort/hash/hash-agg/Memoize node before it spills. EXPLAIN tell:
  `Sort Method: quicksort Memory:` (good) vs `external merge Disk:` (spilled, much slower).
  **Do NOT raise it globally** — it's a per-node × per-connection × per-parallel-worker budget,
  so a global bump multiplies into OOM. Scope it: `SET LOCAL work_mem='256MB'` around the one
  heavy reporting query. For hash-specific spills prefer raising `hash_mem_multiplier` (default
  2.0, PG13+) — it targets only the hash budget.[^workmem]
  
  ## 4. Cost GUCs worth touching (and the ones that aren't)
  
  - **`random_page_cost`** (default 4.0, tuned for spinning disks) → lower to **~1.1 for
    SSD/NVMe**. Single biggest cost-model win; keep `≥ seq_page_cost`.
  - **`effective_cache_size`** (default 4GB) → set to ~50–75% of RAM. Pure planner hint (reserves
    nothing); too low makes the planner over-favor seq scans.
  - **Leave `cpu_*_cost` and `seq_page_cost` alone** — no well-documented reason to hand-tune.
  
  ## 5. Parallel query & JIT
  
  Parallelism needs the scan to exceed `min_parallel_table_scan_size` (8MB); workers scale
  logarithmically. Raise the **three nested caps together** — `max_parallel_workers_per_gather`
  (default 2), `max_parallel_workers`, `max_worker_processes` — or concurrent queries starve.
  Disabled by: parallel-unsafe functions (writes, `nextval()`), cursors, `FOR UPDATE`. Each
  worker gets its own `work_mem`.[^parallel]
  
  **JIT** (`jit_above_cost` 100000) helps long analytical queries but *hurts* short OLTP ones —
  a bad row estimate can push a cheap query over the threshold and add tens–hundreds of ms of
  compile time. On OLTP-heavy DBs, set `jit=off` or raise `jit_above_cost` well above normal
  query cost. Check the `JIT:` block in EXPLAIN after any major-version upgrade.
  
  ## 6. Prepared statements: the generic-plan trap
  
  Postgres runs the first 5 executions as **custom plans** (real params), then compares a
  **generic plan** (no param knowledge) by *estimated* cost and, if not much worse, locks in the
  generic plan **permanently — a one-way ratchet**.[^prepare] On a skewed column this bites: if
  early executions query the common value, the generic plan may pick an Index Scan calibrated for
  ~50% selectivity and keep using it for the 99.9%-match value forever (documented ~9× regressions).
  
  - **Detect (PG16+):** `EXPLAIN (GENERIC_PLAN) SELECT ... WHERE col = $1;` — no PREPARE/EXECUTE
    loop needed. Pre-16: high `stddev_exec_time/mean_exec_time` in `pg_stat_statements`, or
    toggle `SET plan_cache_mode=force_custom_plan` and see if the spike vanishes.
  - **Fix:** `SET LOCAL plan_cache_mode = force_custom_plan` scoped to the sensitive query.
  - **Watch out:** drivers (pgJDBC/Npgsql/psycopg3) auto-prepare after ~5 identical executions,
    so the trap fires without any explicit `PREPARE`; and PgBouncer transaction pooling needs
    `max_prepared_statements>0` (PgBouncer ≥1.21) for server-side prepared statements at all.
  
  ## 7. Tooling
  
  - **`pg_stat_statements`**: rank by coefficient of variation (`stddev/mean`) to find
    plan-unstable queries, not just `mean`. `temp_blks_written`>0 = a query spilling (right-size
    `work_mem` from real data). `shared_blks_read` (absolute I/O) beats hit-ratio alone.
  - **`auto_explain`**: `shared_preload_libraries='auto_explain'`, `log_min_duration=1000`;
    `log_analyze` adds real timing but has overhead — temporary use.
  - **EXPLAIN combo:** `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)` (+`WAL` for write queries).
    `SETTINGS` (PG12+) shows stray GUC overrides when a prod plan won't reproduce.
  - **`HypoPG`**: hypothetical indexes — `EXPLAIN` (not ANALYZE) shows if the planner *would* use
    a proposed index, zero disk/lock, before you build it `CONCURRENTLY`.
  - **`pg_hint_plan`: last resort.** Escalate stats → cost GUCs → query rewrite → *then* a
    targeted, temporary, ticketed hint. Core Postgres ships no hints on purpose (they rot after
    an upgrade).
  
  ## 8. Failure mode → fix
  
  | EXPLAIN symptom | Cause | Fix |
  |---|---|---|
  | estimate ≪/≫ actual `rows` | stale/thin stats | `ANALYZE`; `SET STATISTICS 500` on the column |
  | combined-predicate estimate off | correlated columns | `CREATE STATISTICS (dependencies|mcv) …; ANALYZE` |
  | index on `col` ignored | `WHERE fn(col)=…` | expression index `ON t(lower(col))` + `ANALYZE` |
  | OR → Seq Scan | can't combine indexes | check for `BitmapOr`; else `UNION ALL` of indexed SELECTs |
  | `external merge Disk:` | `work_mem` too low for this node | `SET LOCAL work_mem`; raise `hash_mem_multiplier` |
  | fast avg, occasional spike on skewed col after >5 execs | generic-plan lock-in | `plan_cache_mode=force_custom_plan` |
  
  ## See also
- - [[Push Compute to the Datastore]] — the ladder these plans serve.
- - [[Postgres Concurrency and Throughput]] — bloat/dead tuples also wreck plans.
+ - [[push-compute-to-datastore|Push Compute to the Datastore]] — the ladder these plans serve.
+ - [[postgres-concurrency-and-throughput|Postgres Concurrency & Throughput]] — bloat/dead tuples also wreck plans.
  
  [^explain]: Using EXPLAIN — <https://www.postgresql.org/docs/current/using-explain.html>.
  [^stats]: ANALYZE / populate — <https://www.postgresql.org/docs/current/populate.html>.
  [^extstats]: Extended statistics — <https://www.postgresql.org/docs/current/multivariate-statistics-examples.html>;
      Crunchy Data documented the 1840ms→19ms city/country case.
  [^workmem]: Resource config — <https://www.postgresql.org/docs/current/runtime-config-resource.html>.
  [^parallel]: Parallel query — <https://www.postgresql.org/docs/current/how-parallel-query-works.html>.
  [^prepare]: PREPARE notes / `plan_cache_mode` — <https://www.postgresql.org/docs/current/sql-prepare.html>.
      `EXPLAIN (GENERIC_PLAN)` is PG16+.