When the Planner Gets It Wrong

The planner is a cost model, and a cost model is only as good as what it is told. Almost every story that begins "the planner is stupid" ends in one of five places: a statistic that was never collected, a statistic that is stale, two predicates estimated as independent when they are not, a cost constant describing hardware nobody has run since 2005, or a generic plan built without the parameter value that would have changed it. None of those is a planner defect, and all of them are findable in under ten minutes.

This topic is the procedure for finding them, and it ends by closing the book's first wound. On the first Saturday of each month Cartwheel's checkout latency goes from 40 ms to about 6 seconds, holds there for roughly twenty minutes, and recovers with nobody touching anything. Nothing was deployed. No host is short of CPU. Restarting the API makes it look fixed. All of that is true, all of it is consistent with one mechanism, and the evidence has been printable since Chapter 1.

The Procedure

Run EXPLAIN (ANALYZE) on the statement, wrapping it in BEGIN … ROLLBACK if it writes. Then walk the tree from the bottom and find the lowest node where the estimated row count and the actual row count diverge sharply. That node is the problem. Everything above it was priced from that node's output: the join type, the join order, the sort that spilled, the aggregate that took four seconds. Every one of those choices is a consequence rather than a cause.

This ordering is the whole method, not a style preference. Time is reported at the top of the plan, so the top is where attention goes, and the top is almost never where the fault is. Fix the estimate and the plan usually reassembles itself; fix something above it and you have improved a plan that should never have been chosen. A node whose estimate is off by a factor of three hundred thousand hands a false number to every decision above it.

The Five Usual Causes

Once you have the divergent node, the cause is nearly always in a short list. Stale or absent statistics: the table was bulk-loaded, restored, or has simply changed more than autovacuum's threshold has noticed. Correlated predicates: two columns filtered together whose selectivities were multiplied as if independent, fixed with a CREATE STATISTICS object. A value beyond the histogram: the ascending-key case, where the predicate selects rows the statistics have never seen. An expression the planner cannot estimate: a function call or computed value in the WHERE clause, for which there is no distribution at all, fixed with an expression index or expression statistics. And a cost model that does not match the hardware: random_page_cost at 4.0 on NVMe and effective_cache_size at its 4 GB default on a 64 GB machine, which together tell the planner that index scans are several times more expensive than they are.

Keep the fifth separate from the other four. It is a configuration problem, it applies to every query on the server equally, it is fixed once, and it belongs in the baseline Chapter 10 builds rather than in a conversation about one slow query.

Parameterized Plans

A prepared statement is planned with its actual parameter values for the first five executions. Postgres averages the cost of those five custom plans, then builds one generic plan with the parameters left as placeholders and compares. If the generic plan's estimated cost is not much worse than the average custom cost, it is used from then on and no further planning happens. That is a good trade for most statements and a trap for any statement whose parameter values differ wildly in selectivity — a customer with eleven orders and a customer with 400,000 get the same plan, and one of them is badly served by it.

The tell is a query that is "randomly" slow in a pattern nobody can reproduce by hand, because psql sends literals and gets a custom plan every time. SET plan_cache_mode = force_custom_plan, per session or per role, makes Postgres replan on every execution: it costs planning time and buys correct plans. EXPLAIN (GENERIC_PLAN) on the statement text with $1 in place shows the generic plan without executing anything. And since most drivers prepare statements automatically, this happens in applications that never wrote the word PREPARE. What a pooler does to those prepared statements is Chapter 10's problem.

Solving the Saturday

Cartwheel's checkout renders the customer's orders for the current month. The query joins orders to the order_statuses lookup for a readable label, filters on one customer_id and on placed_at >= date_trunc('month', now()), and returns twenty-one rows. On the first Saturday of August it took 6.1 seconds, and the plan from the diagnostics topic says why in one line: the outer scan was estimated at rows=1 and reported Rows Removed by Filter: 300461.

Now the mechanism, end to end. date_trunc('month', now()) is stable, so it is folded to a constant while the statement is planned, and in August that constant is midnight on the 1st. The histogram on orders.placed_at has its last boundary at 22:58 on the 31st of July, because the most recent autoanalyze ran before the month turned and ANALYZE fires only after 4,000,050 changed tuples on a 40-million-row table. The constant therefore lies past the end of the histogram, in territory the statistics describe not at all, and the estimate collapses to the smallest value the planner will produce: one row. One row makes orders_placed_at_idx the cheapest access path in the query, so the planner takes it and applies customer_id as a filter afterwards, and one row makes a nested loop into the status table the obvious join. In reality that index scan walks the entire month, all 300,482 rows for every customer, fetching each one from the heap individually. With correlation on placed_at down at 0.312 those fetches scatter across 196,204 pages that are not in shared_buffers, and the plan's own buffer counts price it — 196,204 device reads is five seconds.

 Hash Join  (cost=1.16..92.44 rows=21 width=34)
            (actual time=0.041..0.318 rows=21.00 loops=1)
   Hash Cond: (o.status = s.status)
   Buffers: shared hit=27
   ->  Index Scan using orders_customer_history_idx on orders o
         (cost=0.57..91.13 rows=21 width=28)
         (actual time=0.023..0.271 rows=21.00 loops=1)
         Index Cond: ((customer_id = 88213)
                  AND (placed_at >= date_trunc('month'::text, now())))
         Index Searches: 1
         Buffers: shared hit=25
   ->  Hash  (cost=1.07..1.07 rows=7 width=14)
             (actual time=0.009..0.010 rows=7.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on order_statuses s  (cost=0.00..1.07 rows=7 width=14)
 Execution Time: 0.361 ms

Nothing about the query changed. With the month predicate estimated realistically, the composite index on (customer_id, placed_at) is obviously cheaper than the date index, so both predicates become index conditions and twenty-one rows come back from twenty-five buffer accesses. The join flips to a hash join because building a hash from seven rows costs 1.07 and probing it twenty-one times costs nothing, which beats twenty-one index descents. Estimated 21, actual 21, 0.36 milliseconds. The 17,000-fold improvement came from a statistic, not from a rewrite and not from an index that did not already exist.

ALTER TABLE orders ALTER COLUMN placed_at SET STATISTICS 1000;

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.002,
                        autovacuum_analyze_threshold    = 5000);

ANALYZE orders (placed_at, status);   -- and hourly, from cron

The raised target gives placed_at a thousand histogram buckets instead of a hundred, so the last boundary is always far closer to the present and a new month is a shorter walk past the end. The per-table override drops the autoanalyze trigger from 4,000,050 changed tuples to 85,000, which at Cartwheel's volume is minutes rather than days. The scheduled ANALYZE on the two columns that matter is the belt to that pair of braces, and it costs a few seconds. Two loose ends from Chapter 1 close here as well. The incident lasted twenty minutes because that is how long it took the Saturday write burst to push orders over its old four-million threshold and trigger the autoanalyze that ended it. And restarting the API "fixed" it because the pooled connections were holding cached generic plans — throwing the connections away threw the bad plan away, at roughly the same moment the autoanalyze landed, which is how two engineers spent three months certain it was an application bug.

Rewrites That Help the Planner

Some queries are hard to estimate because of how they are written, and four rewrites come up often enough to be worth memorizing. Replacing NOT IN (SELECT …) with NOT EXISTS lets the planner use an anti-join instead of a construct whose null semantics block it. Moving a function call off the column, so placed_at >= '2026-08-01' rather than date_trunc('day', placed_at) = '2026-08-01', restores both the index and the statistics, because the planner has a distribution for the column and none for the computed value. Splitting an OR across two different columns into a UNION ALL of two queries gives each branch its own index. And a LIMIT the planner can push down into a scan turns "sort ten million rows and take twenty" into "read twenty".

These are a different lever from hinting. A hint tells the planner to ignore what it computed; a rewrite changes what it computes, by supplying a statistic it can use or opening a path it could not otherwise take, and the result keeps adapting as the data does.

The Escape Hatches, and Their Price

The enable_* settings are diagnostics, not fixes. SET enable_nestloop = off in a session is the fastest way to prove that the nested loop is what is costing you the six seconds, because the planner will produce its next-best plan and you can time it. Written into postgresql.conf, the same setting distorts every plan on the server to avoid a node type that is correct most of the time, and it hides the estimate error that caused the original problem — which will now express itself somewhere else.

pg_hint_plan is the honest version of the same instinct: real directives in the query, pinning a join method or an index. Under time pressure, with users watching, both are legitimate, and both freeze a decision that was correct against one day's data distribution. A hint left in place after its cause is fixed is a query that can no longer benefit from anything you do later. Use them to stop the bleeding, write the real cause in the same commit, and give the workaround a removal date with a name against it.