Cheap execution plan on your laptop can get expensive on your biggest tenant
A PostgreSQL case study shows how the prepared statement cache can plan the giant tenant's query based on the behavior of small tenants, and what to do when that happens.

- Reading
- 8 min
- Tables
- 1
- Code
- 2
A PostgreSQL case study shows how the prepared statement cache can plan the giant tenant's query based on the behavior of small tenants, and what to do when that happens.
The symptom: low cost in EXPLAIN, real slowness in production
The author of the boringSQL blog describes a case that any DBA of a multi-tenant application has lived through without quite understanding. A paginated endpoint had an execution plan with a cost of a few hundred and an estimate of one row returned. In practice, pg_stat_statements showed the same query reading thousands of buffers per call to return about eight rows. The plan couldn't see its own problem.
The author rebuilt the case in a synthetic sensor-readings schema, with 2,000 sites (the tenants). Most have about 15 sensors and 1,000 readings over two years; a single site, site 1, has 500 sensors and 200,000 readings, two hundred times the average. It's the kind of asymmetry that shows up in practically every multi-tenant table that grows for real: there's always one customer much bigger than the others.
Two mundane ingredients that together break the plan
The query is a classic dashboard tile: the 50 largest readings for a site within a time window, with joins to sensor, model, manufacturer, and unit, ordered by value and limited to 50 rows. No index returns the readings already ordered by value, so any plan has to read the entire time window and sort afterward. The difference between the plans lies in how much work they do with the rows that end up discarded.
The second ingredient is PREPARE. That's how the query reaches the server in most real-world stacks: the pgx driver prepares by default, so does the Rails adapter, and pgJDBC and psycopg 3 migrate to server-side prepared statements after a few executions. This matters because PostgreSQL treats a prepared query differently from a query with literal values.
How the planner chooses between a custom and a generic plan
The first five executions of a PREPARE each get a custom plan, built with the actual values from that call. From the sixth execution on, PostgreSQL also builds a generic plan, just once, without knowing the parameters. The choose_custom_plan() function, in plancache.c, then starts using the generic plan whenever its cost falls below the average of the custom plans seen so far.
The problem is statistical, not a bug. For the ordinary sites in the experiment, each custom plan cost about 135 (planning penalty included), and the generic plan cost 82. As soon as the generic plan starts being used, no new custom plan gets built, the comparison average stops moving, and when site 1 finally reaches that connection, it gets the generic plan without ever having been planned for real.
In short: the generic plan was calibrated on the small tenants that arrived first on that connection, and the large tenant inherits that plan with no chance of replanning, because prepared statements stay tied to a single backend. Behind a connection pooler, each connection makes this choice independently, depending only on the order in which tenants arrive.
What the cost of 82 hides
The generic plan's cost of 82 comes from arithmetic over average statistics: for site_id = $1 the planner assumes the average site (2.2 million rows divided by 2,000 sites, 1,100 rows), and for the taken_at window with two unknown parameters it falls back on DEFAULT_RANGE_INEQ_SEL, a fixed selectivity of 0.5%. The result is an estimate of six rows, cheap enough to justify nested loops with primary-key lookups on each join.
On site 1, the same real week brings 1,983 rows, not six. Under the generic plan, each of those rows pays four index lookups before 1,933 of them get discarded by LIMIT 50. The author used pageinspect to count the height of each index and arrive at the exact number: 21,823 buffer accesses to return 50 rows in a dashboard tile. A full month for the same site climbs to 88,909 accesses and 24 ms, with a warm cache.
Why copying the query from the log and running EXPLAIN reveals none of this
Here's the operational trap: taking the query from the log, pasting the values in as literals, and running EXPLAIN ANALYZE doesn't reproduce the problem. With literals, the planner sees the real site 1 and the actual date range, so it always generates a correct, custom plan. To see what the application actually gets, you have to reproduce the warm-up: PREPARE, five executions with ordinary sites, and only then EXPLAIN (ANALYZE) EXECUTE with the large tenant's parameters.
Two shortcuts help with diagnosis. SET plan_cache_mode = force_generic_plan skips the warm-up and forces the generic plan directly. Since PostgreSQL 16, EXPLAIN (GENERIC_PLAN) shows the generic plan's estimates without needing to execute it. The generic_plans and custom_plans counters in pg_prepared_statements, available since PostgreSQL 14, tell you which plan a production connection is already using right now.
The structural fix: pushing the LIMIT below the joins
Forcing plan_cache_mode = force_custom_plan solves it, but leaves the large tenant's speed resting on a cost margin of less than 1% between two plans and on the availability of a free parallel worker. As Tom Lane, PostgreSQL core developer, himself wrote in a 2022 thread about BUG #17540:
It's not really logically valid, because those estimates are founded on different statistics. I don't know how to fix that :-(.
Tom Lane, PostgreSQL core developer
The proposed fix changes the shape of the query, not the planner's configuration. Instead of doing all the joins and only then sorting and limiting, it rewrites the query as a deferred join: a subquery with LIMIT fetches the 50 largest readings from readings alone, and the joins happen afterward, on that already-small result.
PREPARE top_late(int, timestamptz, timestamptz) AS
SELECT r.id, r.taken_at, r.value, s.label, m.name AS model, v.name AS vendor, u.symbol AS unit
FROM (
SELECT id, sensor_id, taken_at, value
FROM readings
WHERE site_id = $1
AND taken_at >= $2 AND taken_at < $3
ORDER BY value DESC, id
LIMIT 50
) r
JOIN sensors s ON s.id = r.sensor_id
JOIN models m ON m.id = s.model_id
JOIN vendors v ON v.id = m.vendor_id
JOIN units u ON u.id = s.unit_id
ORDER BY r.value DESC, r.id;The subquery with LIMIT isn't folded back into the outer join (is_simple_subquery(), in prepjointree.c, refuses that merge), which is why the planner can't undo the separation. Equivalence is guaranteed by the schema's referential integrity: each reading is linked by primary key to exactly one sensor, model, manufacturer, and unit, and foreign keys validated on NOT NULL columns guarantee that no row gets lost along the way.
The covering index and the price it charges
Under the generic plan, the deferred join already brings the query's cost down to 2,493 accesses (1,993 for the window scan, plus 50 times the four lookups), because the costly part stops growing with the tenant's size. The window scan still fetches one heap page per row. An index that carries every column the inner subquery uses eliminates even that trip to the heap:
CREATE INDEX readings_site_time_cov ON readings (site_id, taken_at)
INCLUDE (value, sensor_id, id);With this index, site 1 runs in less than 1 ms to 2 ms across every scenario tested in the experiment, warm or cold, week or month, custom or generic plan. The table below summarizes the measured times (medians from EXPLAIN (ANALYZE, TIMING OFF), warm and cold cache):
| Plan | Week, custom (warm/cold) | Week, generic (warm/cold) | Month, custom (warm/cold) | Month, generic (warm/cold) |
|---|---|---|---|---|
| Original | 5.9 / 11.7 ms | 5.7 / 16.8 ms | 4.0 / 10.9 ms | 24 / 43 ms |
| Deferred join | 0.9 / 5.6 ms | 0.8 / 10.2 ms | 2.3 / 9.1 ms | 2.6 / 15.2 ms |
| Deferred join + covering index | 0.4 / 0.8 ms | 0.4 / 0.8 ms | 1.1 / 2.0 ms | 1.1 / 1.6 ms |
This index doesn't come for free. In the experiment, it takes up 123 MB against 66 MB for the original index and 126 MB for the table itself, and every new reading writes value, sensor, and id into it, plus the corresponding WAL. With the same leading columns, it can replace the old index, which cuts the net difference to 57 MB, but the write cost still exists and needs to be factored in before creating the index in production.
What's still left open
The benefit of Heap Fetches: 0 from the covering index depends on the visibility map, the per-heap-page bit that marks rows visible to all transactions. In tables with constant inserts, the newest pages (exactly the ones a dashboard tends to query) haven't been marked by VACUUM yet, and on those the index-only scan goes back to the heap anyway.
The author also notes that pg_plan_advice, a feature planned for PostgreSQL 19, doesn't fix the problem at the root: pinning the large tenant's plan pins the same plan for the small tenants, trading one distortion for another. For anyone administering a multi-tenant schema today, the practical lesson is not to trust the cost of an EXPLAIN run with literals, and to check pg_prepared_statements to see which plan each production connection is actually using.
Translated from the Brazilian Portuguese original · Read the original
PostgreSQL in Docker: the five container defaults that bring down production
A guide by engineer Aleksei Aleinikov shows why poorly mounted volumes, 64 MB of shared memory, and a 10-second shutdown timeout account for most incidents with containerized PostgreSQL, not Docker itself.





