PostgreSQL Query Planner Internals: From EXPLAIN to Expert Tuning

The classic Postgres-stale-stats incident. A reporting query runs in seconds for months. Then on a Monday morning, after a weekend bulk import, the same query takes minutes. Nothing changed in the application. Autovacuum hadn't run yet against the changed partition; the planner's stale statistics estimated tens of thousands of rows when there were millions. It picked a nested-loop join that was fine at the smaller estimate and catastrophic at the actual size. We've debugged variants of this on most Postgres-backed services.

graph LR
    SQL[SQL text] --> Parse[Parser:<br/>SQL → AST]
    Parse --> Analyze[Analyzer:<br/>bind names + types]
    Analyze --> Rewrite[Rewriter:<br/>views, RLS rules]
    Rewrite --> Planner[Planner:<br/>enumerate strategies<br/>estimate costs<br/>pick cheapest]
    Stats[(Table statistics<br/>pg_statistic)] -.->|stale stats →<br/>wrong estimate| Planner
    Planner --> Executor[Executor:<br/>scan, join, sort, aggregate]
    Executor --> Result[Result rows]
    style Planner fill:#eef
    style Stats fill:#fee
    style Executor fill:#efe

The diagram shows where stats matter: only the Planner stage uses them, but a wrong estimate there cascades into the wrong join algorithm at the Executor — and that's where the catastrophic slowdown lives. Refresh stats with ANALYZE before debugging anything else.

The Query Pipeline and Planner Stages

Every SQL statement passes through: Parser → Analyzer → Rewriter → Planner → Executor^{[PostgreSQL Docs]}. The planner (the focus here) doesn't run the query — it enumerates possible strategies, estimates cost for each using table statistics, and picks the lowest-cost one.

graph LR
    SQL[SQL text] --> Parse[Parser<br/>tokens to AST]
    Parse --> Analyze[Analyzer<br/>bind names<br/>type-check]
    Analyze --> Rewrite[Rewriter<br/>view expansion<br/>RLS policies]
    Rewrite --> Plan[Planner<br/>enumerate join orders<br/>cost each via stats]
    Plan --> Choose{Cheapest<br/>plan?}
    Choose -->|seq_page_cost = 1<br/>random_page_cost = 4 default| Selected[Selected plan<br/>tree of nodes]
    Selected --> Exec[Executor<br/>walk tree<br/>scan, join, sort, aggregate]
    Exec --> Rows[Result rows]
    Plan -.->|stats stale?<br/>row estimate >10x off| Bad[Wrong plan picked<br/>fix: ANALYZE + raise<br/>default_statistics_target]
    style Bad fill:#fdd
    style Selected fill:#dfd

The diagram is the entire planner-tuning story: stale statistics make the planner pick the wrong join order; ANALYZE refreshes stats; raising default_statistics_target from 100 → 500 helps the planner on skewed columns where the default sample is too small.

Cost is not wall-clock time; it's an arbitrary unit where seq_page_cost = 1.0 (baseline disk page read) and random_page_cost = 4.0 (random access — tunable)^{[PostgreSQL Docs]}. On NVMe, random access is much cheaper; teams often set random_page_cost = 1.1 to reflect this.

Reading EXPLAIN ANALYZE Output

Always run EXPLAIN (ANALYZE, BUFFERS) to see estimated vs actual rows. Read bottom-up (executor pulls from leaf nodes up):

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-06-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

Limit  (cost=3842.15..3842.18 rows=10 width=40)
       (actual time=48.231..48.234 rows=10 loops=1)
  ->  Sort  (cost=3842.15..3842.65 rows=200 width=40)
            (actual time=48.229..48.230 rows=10 loops=1)
        ->  HashAggregate  (cost=3831.65..3833.65 rows=200 width=40)
                           (actual time=48.190..48.210 rows=200 loops=1)
              Batches: 1  Memory Usage: 56kB
              ->  Hash Join  (cost=185.00..3806.65 rows=5000 width=36)
                             (actual time=2.105..43.010 rows=45000 loops=1)
                    Buffers: shared hit=1204 read=892
                    ->  Seq Scan on orders o  (cost=0.00..3100.00 rows=100000 width=8)
                                              (actual time=0.012..15.500 rows=100000 loops=1)
                    ->  Hash  (cost=178.50..178.50 rows=520 width=36)
                               (actual time=1.980..1.981 rows=520 loops=1)
                          ->  Seq Scan on users u  (cost=0.00..178.50 rows=520 width=36)
                                Filter: (created_at > '2024-06-01')
                                Rows Removed by Filter: 4480

Key red flags:

• Estimated rows ≠ actual rows → Stale statistics (run ANALYZE)
• Rows Removed by Filter is high → Missing index on filter column
• Batches: N (N > 1) → Hash spilled to disk; increase work_mem
• read=N is large → I/O bound; consider covering index

The Three Join Algorithms

The planner picks the cheapest of three algorithms based on row counts and available indexes:

Nested Loop Join — For each row in outer table, look up matching rows in inner table via index. Cost: outer_rows × index_lookup. Optimal when outer table is small and inner table has an index on the join key.

Hash Join — Build a hash table from smaller table in memory, probe with larger table. Cost: build_cost(smaller) + probe_cost(larger). Best when neither table has join-key index or both are large. Requires work_mem; if it doesn't fit, spills to disk (slow).

Merge Join — Both tables sorted on join key (or via index scan); scan in lock-step. Cost: sort_cost(outer) + sort_cost(inner) + scan_cost. Memory-efficient for large, pre-sorted data.

Our Monday incident: stale statistics said orders had 50K rows. Planner chose nested loop with orders as outer (small, per stats). Actual 2.1M rows × 520 user lookups = catastrophic.

Check for hash join batching (disk spill):

EXPLAIN (ANALYZE) SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Look for "Batches: 4" — increase work_mem to fit in RAM
SET work_mem = '128MB';

Statistics, Cost Model, and Plan Selection

The planner relies on ANALYZE-collected statistics from pg_statistic (via pg_stats). It sees:

• null_frac — fraction of NULLs
• n_distinct — distinct value count (negative = fraction of rows)
• most_common_vals / most_common_freqs — frequent values and their frequencies
• histogram_bounds — equal-frequency buckets for the remaining values

If WHERE status = 'completed' matches 92% of rows, sequential scan is cheaper than index scan. For WHERE status = 'refunded' (0.1%), index scan wins. ^{[Postgres B-tree]}

Manual ANALYZE after bulk changes:

ANALYZE orders;
SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders';

Increase statistics target for skewed columns (more sample values = better estimates, slower ANALYZE):

ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE orders;

Multi-column statistics capture correlations. By default, independent column stats are multiplied, causing underestimates if columns are correlated:

CREATE STATISTICS orders_user_status (dependencies) ON user_id, status FROM orders;
ANALYZE orders;

Indexes That Change Plan Decisions

Covering indexes (Index-Only Scans) — Include SELECT columns in the index so the planner can return results from the index alone (zero heap fetches):

-- Without INCLUDE: index scan on created_at, then heap fetches for name, email
CREATE INDEX idx_users_created_at_covering
    ON users(created_at)
    INCLUDE (name, email);
 
-- Now: "Index Only Scan" with "Heap Fetches: 0"
EXPLAIN (ANALYZE) SELECT name, email FROM users WHERE created_at > '2024-01-01';

Partial indexes — Index only rows you query. If WHERE status = 'pending' is your common filter, index only pending orders:

CREATE INDEX idx_orders_pending_customer ON orders(customer_id)
    WHERE status = 'pending';
 
-- The planner uses this only when WHERE includes status = 'pending'
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending'; -- uses partial index
SELECT * FROM orders WHERE customer_id = 123 AND status = 'completed'; -- doesn't use it

Production Checklist

1. Find slow queries — pg_stat_statements ordered by total_exec_time
2. Run EXPLAIN (ANALYZE, BUFFERS) — compare estimated vs actual rows, check Rows Removed by Filter
3. Check statistics freshness — SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'orders'
4. Fix stale stats first — ANALYZE table_name after bulk changes, then re-run EXPLAIN
5. Add covering indexes for high-filter-removal scans — CREATE INDEX ... INCLUDE (...) to eliminate heap fetches
6. Use partial indexes — WHERE status = 'pending' — for selective filters
7. Increase STATISTICS target for skewed columns — ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500; ANALYZE;
8. Check hash join batching — if Batches > 1, increase work_mem to fit in RAM
9. Verify cost parameters — consider random_page_cost = 1.1 on SSD workloads
10. Monitor continuously — pg_stat_statements weekly; watch for regressions

Diagnostic queries you actually run during incidents

Two queries that surface 90% of plan-related production issues — paste them into psql while the latency graph is still spiking, not after: ^{[PostgreSQL Docs]}

-- 1. Top 10 queries by cumulative time. The biggest CPU/IO offender on the
--    instance, regardless of how often you run it.
SELECT
    substr(query, 1, 80)                                         AS query_prefix,
    calls,
    round(total_exec_time::numeric, 2)                           AS total_ms,
    round((total_exec_time / calls)::numeric, 2)                 AS avg_ms,
    round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2)
                                                                 AS pct_total
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 10;
 
-- 2. Estimate-vs-actual row-count drift for the top-50 user tables. Anything
--    where last_analyze is hours old AND n_mod_since_analyze > 10% of n_live_tup
--    is a candidate for an immediate ANALYZE — that's where the planner is
--    using stale statistics and picking the wrong join order.
SELECT
    relname,
    n_live_tup,
    n_mod_since_analyze,
    round(100.0 * n_mod_since_analyze / GREATEST(n_live_tup, 1), 1) AS pct_changed,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
  AND n_mod_since_analyze > 0.1 * n_live_tup
ORDER BY n_mod_since_analyze DESC
LIMIT 50;
``` <Cite id="postgres-docs" />
 
The second query catches the failure mode behind most "the planner suddenly picked a sequential scan" incidents: a bulk import or DELETE drifted statistics far enough that cardinality estimates went sideways, and autovacuum hasn't caught up yet. Run `ANALYZE` on each offender, then re-EXPLAIN the slow query before reaching for `pg_hint_plan` or query rewrites.
 
## Parallel Query Tuning: When the Planner Splits Work
 
PostgreSQL parallel query splits a scan or aggregate across multiple worker processes, then reassembles the result through a `Gather` or `Gather Merge` node<Cite id="postgres-docs" />. The planner only considers parallel plans when the estimated cost crosses `min_parallel_table_scan_size` (default 8MB) and `min_parallel_index_scan_size` (default 512kB), and it caps degree of parallelism at `max_parallel_workers_per_gather` (default 2 in stock builds, 4 on most managed services).
 
The common mistake is leaving these defaults on hardware that can clearly absorb more parallelism. On a 16-vCPU instance running an analytical workload, capping each query at two workers means a sequential scan of a 50GB fact table peaks at three cores out of sixteen — the leader plus two helpers. Tune for the slowest aggregate query you actually run, not the cheapest one. Set the per-gather cap to roughly half your available vCPUs, leave the cluster-wide `max_parallel_workers` at the full count, and bias `parallel_setup_cost` and `parallel_tuple_cost` downward on a fast interconnect:
 
```sql
-- Baseline parallel tuning for an analytics replica with 16 vCPUs.
ALTER SYSTEM SET max_parallel_workers_per_gather = 8;
ALTER SYSTEM SET max_parallel_workers           = 16;
ALTER SYSTEM SET parallel_setup_cost            = 100;   -- default 1000
ALTER SYSTEM SET parallel_tuple_cost            = 0.01;  -- default 0.1
ALTER SYSTEM SET min_parallel_table_scan_size   = '4MB'; -- favor parallel sooner
SELECT pg_reload_conf();
 
-- Force per-session parallelism for a heavy reporting query without
-- changing the cluster default.
SET max_parallel_workers_per_gather = 8;
SET parallel_leader_participation   = on;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS orders, SUM(total_cents)
FROM orders
WHERE created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 1;

Look for Workers Planned: N and Workers Launched: N in the plan. If Launched is consistently lower than Planned, the cluster is hitting max_parallel_workers because too many concurrent queries each want their own pool — that is the signal to either lower the per-gather cap or scale the instance. If you see Gather Merge over a Parallel Index Scan, ordered output is being preserved across workers; if you see plain Gather over a Parallel Seq Scan, the leader is doing the merge in arbitrary order. Avoid forcing parallelism on OLTP workloads — short queries pay the worker startup cost without amortizing it, and you will see the leader spending more time on parallel_setup_cost than on actual work.

Partition Pruning Gotchas

Declarative partitioning is a planner feature, not a runtime one. Pruning happens in two phases: at plan time when the predicate is a literal, and at execution time when the predicate references a parameter or a function the planner cannot fold during planning^{[PostgreSQL Docs]}. Stale assumptions about which phase you are in cause most "why is it scanning every partition" incidents.

The classic trap is wrapping the partition key in a function. A partition on created_at with a RANGE strategy prunes when the predicate is created_at >= '2026-01-01', but degrades to a full scan on every child when the predicate is date_trunc('month', created_at) = '2026-01-01' — the planner cannot prove which partitions match because the function is opaque to range exclusion. The fix is to keep predicates in terms of the raw partition key and let the planner do the bound-checking:

-- BAD: function on partition key disables pruning, every partition scanned.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM events
WHERE date_trunc('month', created_at) = '2026-01-01';
 
-- GOOD: predicate matches the RANGE bounds, only the January partition reads.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT COUNT(*) FROM events
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';
 
-- Confirm runtime pruning for prepared statements with bound params.
EXPLAIN (ANALYZE, COSTS OFF)
EXECUTE events_in_window('2026-03-15'::timestamptz, '2026-03-22'::timestamptz);
-- Look for "Subplans Removed: N" and "Partitions: 1 of 12" in the output.

The other failure mode is enable_partition_pruning = off slipping into a session GUC during a migration and never getting unset, or constraint_exclusion = off on legacy inheritance-based partitioning. Always validate by reading the plan: pruning is in effect when you see either Subplans Removed: N (run-time) or only the matching child partition appearing under the Append node (plan-time). If every partition appears, something disabled pruning — check SHOW enable_partition_pruning and the predicate shape before tuning anything else.

Extended Statistics for Correlated Columns

Single-column statistics treat predicates as independent and multiply their selectivities. When country = 'US' matches 65% of rows and state = 'CA' matches 12% of rows, the planner estimates country = 'US' AND state = 'CA' at 65% × 12% = 7.8% — but the actual selectivity is closer to 12%, because state = 'CA' already implies country = 'US'. The result: a hash join planned for 7.8% of the table is run on 12%, and work_mem blows past its budget into a disk-spilled hash^{[PostgreSQL Docs]}.

CREATE STATISTICS fixes this by teaching the planner about correlations across columns. The three modes — dependencies, ndistinct, and mcv — each address a different bias. Use dependencies when one column functionally implies another, ndistinct when a GROUP BY over multiple columns under-estimates distinct combinations, and mcv when most-common-value combinations dominate the distribution:

-- Functional dependencies: country implies state, state implies city.
CREATE STATISTICS users_geo_deps (dependencies)
    ON country, state, city
    FROM users;
 
-- Distinct combinations: GROUP BY (region, product_line) is wildly off
-- because the planner multiplies n_distinct(region) * n_distinct(product_line).
CREATE STATISTICS sales_grouping_ndistinct (ndistinct)
    ON region, product_line
    FROM sales_facts;
 
-- Most-common values: skewed pairs like (US, iPhone) appear far more
-- often than independent column stats predict.
CREATE STATISTICS orders_country_device_mcv (mcv)
    ON shipping_country, device_type
    FROM orders;
 
ANALYZE users;
ANALYZE sales_facts;
ANALYZE orders;
 
-- Verify the planner is using the new object.
SELECT statistics_name, dependencies, n_distinct
FROM pg_stats_ext
WHERE schemaname = 'public'
ORDER BY statistics_name;

After running ANALYZE, re-EXPLAIN the query and check that the Hash Join row estimate is now within 2× of the actual count. Extended statistics are not free — every additional object slows ANALYZE and consumes memory in pg_statistic_ext_data — so add them surgically against queries you have already proven are mis-estimated, not preemptively against every multi-column predicate.

Index Fitness Analysis with pg_stat_user_indexes

Adding indexes is easy; pruning the ones nobody uses is the hard part. Every index pays a write tax on INSERT, UPDATE, and DELETE, plus a backup-size tax, plus a vacuum-cost tax. The view pg_stat_user_indexes exposes per-index scan counters that, joined with pg_index and pg_class, give an index-fitness scorecard you can run weekly:

-- Index usage scorecard: scans, size, and write-amplification cost.
SELECT
    s.schemaname,
    s.relname                                           AS table_name,
    s.indexrelname                                      AS index_name,
    pg_size_pretty(pg_relation_size(s.indexrelid))      AS index_size,
    s.idx_scan                                          AS scans,
    s.idx_tup_read                                      AS tuples_read,
    s.idx_tup_fetch                                     AS tuples_fetched,
    CASE
        WHEN s.idx_scan = 0                              THEN 'unused — drop candidate'
        WHEN s.idx_scan < 50 AND
             pg_relation_size(s.indexrelid) > 100*1024*1024 THEN 'rarely used + large — review'
        WHEN s.idx_tup_read > 0 AND
             s.idx_tup_fetch::float / s.idx_tup_read < 0.10 THEN 'low fetch ratio — likely covering miss'
        ELSE 'healthy'
    END                                                 AS verdict
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique          -- never auto-drop unique constraints
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 50;
 
-- Cross-reference with bloat: a low-scan index that is also bloated
-- is the highest-priority drop, since it's costing both writes AND vacuum.
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 50 * 1024 * 1024
ORDER BY pg_relation_size(indexrelid) DESC;

Two caveats before dropping anything. First, idx_scan resets when the stats collector restarts or when you explicitly call pg_stat_reset(); verify you have at least a full business cycle of data — a month is a reasonable floor. Second, indexes that exist only to enforce uniqueness or to back foreign keys must stay even if idx_scan = 0, which is why the query above filters indisunique and indisprimary. For the rest, the standard playbook is to mark the index INVALID first via UPDATE pg_index SET indisvalid = false in a maintenance window, observe latency for 24 hours, and only then DROP INDEX CONCURRENTLY. That two-stage rollout catches the rare query that uses the index once a day for a Monday-morning report, without the irreversibility of a hard drop.

Frequently Asked Questions

Keep Reading

• Database Indexing Strategies — B-tree internals, composite index ordering, GIN/GiST selection, and the operational patterns that prevent index bloat
• Zero-Downtime Database Migrations — The expand-contract pattern, CREATE INDEX CONCURRENTLY, and online schema changes that keep the planner happy
• Scaling Redis for High Throughput — When a caching layer is the right complement to planner tuning, and how to operate Redis at scale