This one started with RegreSQL. Since The tests passed. The plan didn't. I've been loading production statistics into empty databases to see how much the plans alone can tell about a real workload. One paginated endpoint in a multi-tenant app wouldn't line up. Its plan was cheap, a cost of a few hundred and an estimate of one row, while pg_stat_statements in production had the same query reading thousands of buffers per call to return about eight rows.
RegreSQL compares plans by cost, and so do I. The first thing my eyes go to in a plan is the cost on the top line: small number, cheap query, move on. This query's cost couldn't see its own problem, so before deciding what RegreSQL should do about it I rebuilt the case on a synthetic schema where every row can be accounted for. Two ordinary things cause it, one tenant much bigger than the others and a query sent as a prepared statement, and between them PostgreSQL ends up planning the big tenant's query from the small tenants' runs and keeping that plan for as long as the connection lives.
Everything was captured on PostgreSQL 18.7 (the postgres:18 image) with pageinspect. The scripts are setup.sql, query.sql, late.sql and experiments.sql, which replays every plan here. setup.sql doesn't seed random(), so your week of site 1 won't be exactly 1,983 readings, but every count below is arithmetic you can redo on your own number (each reading costs the generic plan ten lookup accesses plus its scan), and the plans the planner picks come out the same. Timings are medians of EXPLAIN (ANALYZE, TIMING OFF) on my laptop, warm cache unless I say otherwise; anything closer than a tenth of a millisecond is noise.
One tenant two hundred times bigger
The lab is sensor readings with the usual lookup tables hanging off them. A site is the tenant, and readings stands in for whichever tenant-scoped table grows fastest in your schema:
CREATE TABLE vendors (id int PRIMARY KEY, name text NOT NULL); CREATE TABLE models (id int PRIMARY KEY, vendor_id int NOT NULL REFERENCES vendors, name text NOT NULL); CREATE TABLE units (id int PRIMARY KEY, symbol text NOT NULL); CREATE TABLE sites (id int PRIMARY KEY, name text NOT NULL); CREATE TABLE sensors ( id int PRIMARY KEY, site_id int NOT NULL REFERENCES sites, model_id int NOT NULL REFERENCES models, unit_id int NOT NULL REFERENCES units, label text NOT NULL ); CREATE TABLE readings ( id bigint PRIMARY KEY, site_id int NOT NULL REFERENCES sites, sensor_id int NOT NULL REFERENCES sensors, taken_at timestamptz NOT NULL, value double precision NOT NULL ); CREATE INDEX readings_site_time ON readings (site_id, taken_at); There are 2,000 sites. Most have about 15 sensors and 1,000 readings over two years, and site 1 has 500 sensors and 200,000 readings. Every multi-tenant table I've worked with has had a site 1. Readings are inserted in random time order, so a week of any site is scattered across the heap, and readings carries site_id as a deliberate denormalization so a site's readings can be found through one index without going through sensors.
The query is a dashboard tile, the 50 highest readings at a site in a time window:
PREPARE top(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 readings 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 WHERE r.site_id = $1 AND r.taken_at >= $2 AND r.taken_at < $3 ORDER BY r.value DESC, r.id LIMIT 50; No index returns readings in value order, so any plan reads the whole window and sorts it. Plans differ in how much work they put into rows that end up outside the top 50. For a small site that's a handful of rows. For site 1 it's most of the query.
It's a PREPARE because that's how it reaches the server from most applications. The production endpoint went through pgx, which prepares by default (so does the Rails adapter); pgJDBC and psycopg 3 switch to server-side prepared statements after a few executions.
How a prepared statement picks its plan
A prepared statement exists to skip planning on repeat calls. On data like this a plan made without the parameters has to guess them, so PostgreSQL doesn't commit straight away. The first five executions each get a custom plan built with the actual values. From the sixth on it also builds a generic plan, once, with $1..$3 unknown, and choose_custom_plan() in plancache.c switches to it whenever its cost is below the average of the custom plans so far. Each custom plan is charged a little extra in that comparison for the planning it costs.
The dashboard opens on ordinary sites far more often than on site 1, so I ran it for six of them and asked pg_prepared_statements which plans it had used:
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements; name | generic_plans | custom_plans ------+---------------+-------------- top | 1 | 5 Each ordinary site's custom plan cost about 135 with the planning charge, and the generic plan costs 82. Once the generic plan is in use no new custom plans get built, so the average it's compared against never moves again, and when site 1 arrives it gets the generic plan without being planned at all.
Prepared statements live in one backend, so behind a connection pool each connection makes this choice separately, from whichever tenants reached it first. Running that query on a production connection is the quickest way to see which way it went. (The two counters arrived in PostgreSQL 14; before that you had to infer the switch from a query getting slower around its sixth run.)
The 82 itself is arithmetic on defaults. For site_id = $1 the planner assumes the average site, 2.2 million rows over 2,000 sites, 1,100 rows. For the taken_at range against two unknowns, clauselist_selectivity falls back to DEFAULT_RANGE_INEQ_SEL, 0.5%. That's 5.5 rows, and six rows through four nested-loop lookups on primary keys is cheap. No parameter reaches that arithmetic, so the window doesn't either.
Seeing the plan production runs
Copying the query from the log, pasting in the values and running EXPLAIN ANALYZE won't show any of this. With literals the planner sees site 1 and a real date range, and you get a custom plan. To get what the application gets, repeat the warm-up: PREPARE, execute it five times with ordinary sites, then EXPLAIN (ANALYZE) EXECUTE top(1, ...). SET plan_cache_mode = force_generic_plan skips the warm-up, and since PostgreSQL 16 EXPLAIN (GENERIC_PLAN) shows the generic plan's estimates without running it.
Site 1, one week, under the generic plan:
Limit (cost=81.55..81.57 rows=6 width=59) (actual rows=50.00 loops=1) Buffers: shared hit=21823 -> Sort (cost=81.55..81.57 rows=6 width=59) (actual rows=50.00 loops=1) Sort Key: r.value DESC, r.id -> Nested Loop (cost=1.28..81.48 rows=6 width=59) (actual rows=1983.00 loops=1) -> Nested Loop (cost=1.14..80.54 rows=6 width=60) (actual rows=1983.00 loops=1) -> Nested Loop (cost=1.00..79.52 rows=6 width=54) (actual rows=1983.00 loops=1) -> Nested Loop (cost=0.72..77.73 rows=6 width=44) (actual rows=1983.00 loops=1) -> Index Scan using readings_site_time on readings r (cost=0.43..27.90 rows=6 width=28) (actual rows=1983.00 loops=1) Buffers: shared hit=1993 -> Index Scan using sensors_pkey on sensors s (cost=0.29..8.30 rows=1 width=24) (actual rows=1.00 loops=1983) Buffers: shared hit=5949 -> Index Scan using models_pkey on models m (cost=0.28..0.30 rows=1 width=18) (actual rows=1.00 loops=1983) Buffers: shared hit=5949 -> Index Scan using vendors_pkey on vendors v (cost=0.14..0.17 rows=1 width=14) (actual rows=1.00 loops=1983) Buffers: shared hit=3966 -> Index Scan using units_pkey on units u (cost=0.14..0.16 rows=1 width=7) (actual rows=1.00 loops=1983) Buffers: shared hit=3966 Sorting after the joins is the right call for the six rows the planner expected, but with 1,983 every reading gets its four lookups before all but 50 are thrown away. Warm, a week of this runs about as fast as the custom plan. A month is 8,080 readings, 88,909 buffer accesses and 24 ms, at a cost of 81.57.
What the buffer count counts
21,823 looks like a lot for a dashboard tile. It adds up exactly once you know how tall each index is, which pageinspect reads from the metapage:
SELECT c.relname, (bt_metap(c.relname)).level FROM pg_class c WHERE c.relname IN ('readings_site_time', 'sensors_pkey', 'models_pkey', 'vendors_pkey', 'units_pkey'); relname | level --------------------+------- units_pkey | 0 sensors_pkey | 1 vendors_pkey | 0 models_pkey | 1 readings_site_time | 2 As in the B-tree chapter, level counts the levels above the leaves. A lookup in one of these unique indexes touches level + 1 index pages plus one heap page, 3 for sensors and models and 2 for vendors and units, which is 19,830 across all the lookups. The readings scan is the other 1,993: 11 index pages and 1,982 heap pages for 1,983 rows. The counter only ticks when a scan moves to a different buffer, and only once in this week do two consecutive rows share a heap page.
Most of those accesses go back to pages that were just visited; the root of sensors_pkey alone is in the count 1,983 times. When the page is already in shared buffers, an access is a hash lookup to find it and a pin (a reference count that keeps the page from being evicted while someone's using it). Cheap, both of them. The custom plan for the same week makes 1,362 accesses and takes the same 6 ms.
From a cold cache the time does split, though the buffer counts still don't show it. I emptied shared buffers with pg_buffercache_evict_all() (new in 18) and dropped the Docker VM's page cache before each run. The two week plans then read almost the same number of pages from disk, 1,111 for the generic plan and 1,323 for the custom one, since a second visit to a page is a hit either way. The generic plan took 16.8 ms and the custom one 11.7, and track_io_timing puts the gap in read time, 9 ms against 3.
The custom plan uses a bitmap heap scan, which collects the matching page numbers from the index first and then visits them in block order. Knowing its pages up front, it hands them in 18 to a read stream that requests them ahead of the page being processed. The generic plan's plain index scan asks for one page, waits for it, and only then learns which page it needs next. (That 9 ms for 1,111 reads is about 8 µs a page, faster than any SSD serves a random read, so dropping the VM's cache still left my laptop's own page cache warm underneath. Even at that speed the one-page-at-a-time scan waits three times as long; on real disks or network storage, where a random read costs tens to hundreds of microseconds, the gap grows with every read.) Only read= together with the I/O timing shows any of this. For the month, cold, it's 43 ms against 11.
When the planner sees site 1
Forced to plan site 1 for a week, the planner gets the estimate close (1,888 rows against 1,983) and picks a plan for volume: the bitmap scan over the window, sensors, models and vendors read whole into hash tables, and units, with only 20 distinct keys, behind a Memoize node. Its cost is 6,536 and it takes about 6 ms, a good part of it spent hashing 30,000 sensor rows to use 1,983 of them.
The plan it rejected is faster. With enable_hashjoin and enable_mergejoin off the runner-up shows up: it sorts the window first and joins only the top 50. It costs 6,563, 0.4% more, and runs in about 3 ms, half the time of the plan that beat it.
It only exists because of parallel workers. It scans the window in two processes, sorts each half, and Gather Merge combines the two sorted streams in order before the joins. For the month the same shape wins outright, 4 ms against the generic plan's 24. The planner puts a sort for the query's ORDER BY below the joins only when it's building sorted inputs for Gather Merge (generate_useful_gather_paths() in allpaths.c); with max_parallel_workers_per_gather = 0 the month's custom plan hash-joins all 8,080 rows first and takes 8 ms.
plan_cache_mode = force_custom_plan, or not preparing the statement, gets site 1 its own estimate for about 0.3 ms of planning per call. When the SQL comes from an ORM and can't be changed, that's a good fix: the month goes from 24 ms to 4 or 8. I wouldn't stop there, though, because site 1's speed then rests on a cost margin under one percent and on a parallel worker being free. Tom Lane put it plainly in the 2022 thread behind BUG #17540: comparing the generic plan's cost to a custom plan's "is not really logically valid, because those estimates are founded on different statistics. I don't know how to fix that :-(."
Moving the LIMIT below the joins
Every fast plan above picked the top 50 from readings before joining anything. The query can ask for that order directly, which is usually called a deferred join:
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; A subquery with a LIMIT isn't pulled up into the outer join (is_simple_subquery() in prepjointree.c refuses it), so the planner can't flatten it back into the original. The outer ORDER BY needs no second sort as long as the joins are nested loops, which keep the subquery's order. It's the same query only because no join can drop or duplicate a reading: each is on a primary key, and validated foreign keys on NOT NULL columns guarantee every reading has its sensor, model, vendor and unit. experiments.sql checks three windows for byte-identical output.
Under the generic plan the week now costs 1,993 accesses for the scan plus 50 × (3 + 3 + 2 + 2) for the lookups, 2,493 in all, and the lookup part no longer grows with the tenant. The scan still does, fetching a heap page per row, one at a time when cold. An index that carries every column the inner query reads takes the heap out too:
CREATE INDEX readings_site_time_cov ON readings (site_id, taken_at) INCLUDE (value, sensor_id, id); Site 1, warm and cold, under every plan the cache could choose:
| site 1, ms (warm / cold) | custom, week | generic, week | custom, month | generic, month |
|---|---|---|---|---|
| original | 5.9 / 11.7 | 5.7 / 16.8 | 4.0 / 10.9 | 24 / 43 |
| deferred join | 0.9 / 5.6 | 0.8 / 10.2 | 2.3 / 9.1 | 2.6 / 15.2 |
| deferred join, covering index | 0.4 / 0.8 | 0.4 / 0.8 | 1.1 / 2.0 | 1.1 / 1.6 |
In the bottom row the custom and generic plans run within half a millisecond of each other, and the slowest cell is 2 ms, so it no longer matters which tenant warmed up the connection. The four plans in that row are costed anywhere from 58 to 1,095.
The covering index has a price. It's 123 MB, against 66 MB for readings_site_time and 126 MB for the table, and every new reading writes its value, sensor and id into it, plus the WAL. With the same leading columns it can replace readings_site_time, which makes the difference 57 MB. Its Heap Fetches: 0 also depends on the visibility map, a bit per heap page saying every row on it is visible to all transactions, which lets an index-only scan skip the heap. setup.sql's closing VACUUM set those bits. On a table taking a steady stream of inserts, the newest pages, the ones a dashboard asks about, are the ones vacuum hasn't marked yet, and for those the index-only scan goes back to the heap.
PS. PostgreSQL 19's pg_plan_advice won't fix this either. Pinning site 1's plan pins it for every small tenant too, and pins come with problems of their own. More on that in a separate future:wq post.

