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 of each month Cartwheel's checkout latency climbs from 40 ms into whole seconds, six of them on the morning of Saturday the 1st of August, and recovers hours later with nobody touching anything. Nothing was deployed. No host is short of CPU. The Saturday after carried the same traffic and behaved perfectly. 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 the statistics have never seen: rows past the histogram's last boundary, or a value missing from the most-common list. 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, and the business counts months in Berlin time. The query joins orders to the order_statuses lookup for a readable label, filters on one customer_id, and compares the order's calendar month with the current one, each computed by date_trunc with the zone pinned inside the call, exactly as Chapter 8 advised, so that an expression index, orders_placed_month_idx, could be built on it. It returns twenty-one rows. On the morning of the 1st of August it took 6.3 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. ANALYZE collects statistics for the expression an index is built on, and a calendar month over four years of orders has 48 distinct values, few enough that every one of them goes into the most-common list. The list covers the whole table. now() is stable, so the current month is folded to a constant while the statement is planned, and on the 1st of August that constant is a month the list has never seen, because the last autoanalyze ran at 23:00 on the 31st of July. An equality against a value not in the list gets whatever the list leaves over, divided among the values it does not name; this list leaves nothing, so the estimate drops to the floor of one row. One row makes orders_placed_month_idx the cheapest path in the query at a cost of 4.47, against about 174 for walking the customer's orders through orders_customer_history_idx, an index-only scan that still has to visit the heap for every one of them because the scattered writes keep the visibility map clear. So the planner takes the month index and applies customer_id as a filter afterwards. In reality that index scan walks the entire month, all 300,482 orders for every customer, fetching each one from the heap. New rows land wherever vacuum has left free space, so the walk reads 196,155 pages, none of them already in a 128 MB shared_buffers, and 196,155 device reads at about thirty-two microseconds each is the 6.3 seconds.

 Nested Loop  (cost=0.56..174.88 rows=1 width=42)
              (actual time=0.010..0.104 rows=21.00 loops=1)
   Join Filter: (s.status = o.status)
   Rows Removed by Join Filter: 52
   Buffers: shared hit=97
   ->  Index Only Scan using orders_customer_history_idx on orders o
         (cost=0.56..173.72 rows=1 width=39)
         (actual time=0.007..0.084 rows=21.00 loops=1)
         Index Cond: (customer_id = 88213)
         Filter: (date_trunc('month'::text, placed_at, 'Europe/Berlin'::text)
                = date_trunc('month'::text, now(), 'Europe/Berlin'::text))
         Rows Removed by Filter: 51
         Heap Fetches: 72
         Index Searches: 1
         Buffers: shared hit=76
   ->  Seq Scan on order_statuses s  (cost=0.00..1.07 rows=7 width=21)
         (actual time=0.000..0.000 rows=3.48 loops=21)
         Buffers: shared hit=21
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.063 ms
 Execution Time: 0.110 ms

Nothing about the query changed. With August in the list at its real share, 0.75% of the table, the month index would have to walk some 300,000 entries, so the planner goes through orders_customer_history_idx instead: it walks the customer's 72 orders in the index, visits the heap for each, drops the 51 from earlier months with the filter, and returns the twenty-one. The node still estimates one row, because a customer's share of a month's share comes out below one, and that no longer matters: the price of this path comes from the customer's index entries, not from that final number. Ninety-seven buffer hits and a tenth of a millisecond, or 7.4 milliseconds the first time, with most pages coming off the device. The difference came from a statistic, not from a rewrite and not from an index that did not already exist.

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

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 first autoanalyze of the new month finds it thousands of orders deep and lists it at its real share, long before the morning's checkouts. An ANALYZE scheduled for a minute after midnight would not do it. At that point the month holds a few hundred of forty million rows, a 30,000-row sample almost never draws one, and a value drawn only once is not listed. The longer-term fix is the other half of Chapter 8's advice: write the month as a range on the bare column, placed_at >= date_trunc('month', now(), 'Europe/Berlin'). Then orders_customer_history_idx takes both predicates as index conditions, the range is estimated from the real newest value at the end of orders_placed_at_idx instead of a list of months, and the 1st stops being special. Chapter 1's loose ends close here as well. It always started on the 1st because that is when the month changes. It lasted until autoanalyze next ran, which at a few hundred orders a minute takes the best part of two days and on the 1st of August took the Saturday burst a morning. And nothing in the application, a restart included, could have fixed it.

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.