PostgreSQL Prepared Statements Hurt Your Biggest Tenant

PostgreSQL prepared statements choosing a generic plan for a large tenant

PostgreSQL prepared statements can quietly give your largest tenant a query plan built for your smallest ones. A series of tests on PostgreSQL 18.7 shows how it happens, why the plan’s cost number hides the problem, and how to restructure the query so it stops mattering.

The trigger was a paginated endpoint in a multi-tenant app. Its plan looked cheap, with a cost of a few hundred and an estimate of one row. Yet pg_stat_statements in production showed the same query reading thousands of buffers per call to return about eight rows.

The test setup

The test database models sensor readings with supporting tables for vendors, models, units, sites and sensors. It holds 2,000 sites. A typical one has around 15 sensors and 1,000 readings spanning two years. Site 1 is the outlier, with 500 sensors and 200,000 readings, about 200 times larger. Rows arrive in random time order, and an index on site and time supports lookups.

The query behind a dashboard tile returns the 50 highest readings for one site within a time window, joined to four lookup tables. Since no index delivers rows sorted by value, any plan must read the full window and sort it. Plans differ in how much effort they spend on rows that miss the top 50: a few for a small site, nearly all of the work for site 1.

How a prepared statement picks its plan

For the first five executions, PostgreSQL plans each call with the actual values. On the sixth it builds a single generic plan that treats the parameters as unknown. It then adopts that plan whenever its estimated cost undercuts the average of the custom plans, which carry a small surcharge for planning time.

Dashboards open on ordinary sites much more often than on site 1. In the test, an ordinary site’s custom plan cost about 135 including the surcharge, and the generic plan cost 82. After the switch, no further custom plans are made, so the comparison average stays frozen. Site 1 then runs on the generic plan without being planned at all.

The 82 is arithmetic on defaults. The planner assumes an average site of 1,100 rows and a 0.5% time range, which gives 5.5 rows. Six rows through four primary-key lookups is cheap, and no real parameter reaches that math.

Each connection makes this choice separately, depending on which tenants reached it first. Checking pg_prepared_statements, which has generic and custom plan counters since PostgreSQL 14, shows which way a connection went.

What the bad plan costs

Pasting values into EXPLAIN ANALYZE won’t reproduce it, because literals give the planner site 1’s real numbers. To see the application’s plan, repeat the warm-up: prepare, run five ordinary sites, then explain site 1. Setting plan_cache_mode to force_generic_plan skips the warm-up.

For site 1 over one week, the generic plan expects 6 rows but meets 1,983. It performs four lookups per reading and then drops all but 50, touching 21,823 buffers. With a warm cache that runs about as fast as the custom plan. A one-month window means 8,080 readings, 88,909 buffer accesses and 24 ms.

From a cold cache the gap widens. The generic plan took 16.8 ms against 11.7 for the custom one, with read time of 9 ms against 3. The custom plan uses a bitmap heap scan, which lets PostgreSQL 18 read pages ahead. The generic plain index scan asks for one page and waits. For the month, cold, it was 43 ms against 11.

Why forcing custom plans is only a partial fix

Forced to plan site 1 itself, the planner estimates 1,888 rows and picks a plan for volume that runs in about 6 ms. The rejected runner-up was faster at about 3 ms, because it sorts the window first and joins only the top 50. It costs just 0.4% more, and exists only thanks to parallel workers. With parallelism off, the month’s custom plan takes 8 ms.

Setting plan_cache_mode to force_custom_plan, or skipping preparation, costs about 0.3 ms of planning per call. If SQL comes from an ORM, that is a reasonable fix: the month drops from 24 ms to 4 or 8. But site 1’s speed then rests on a cost margin under one percent and a free parallel worker. Tom Lane has said in a 2022 thread that comparing a generic plan’s cost to a custom plan’s isn’t logically valid, because they rest on different statistics.

Move the LIMIT below the joins

A sturdier fix for PostgreSQL prepared statements is a deferred join. A subquery selects the top 50 readings first, and only then are the lookup tables joined. PostgreSQL won’t flatten a subquery containing a LIMIT, and the outer ordering needs no extra sort while joins are nested loops. Results stay identical because every join uses a primary key and validated foreign keys on NOT NULL columns, so no reading can vanish or repeat.

Under the generic plan, a week now costs 2,493 buffer accesses, and the lookup part no longer grows with tenant size. A covering index on site and time that includes value, sensor and id removes heap visits. Then the custom and generic plans land within half a millisecond of each other, and the slowest cell is 2 ms.

The index isn’t free. It is 123 MB, against 66 MB for the existing index and 126 MB for the table, and it adds write and WAL load. It can replace the old index, a net difference of 57 MB. Index-only scans also depend on the visibility map, which lags on the newest pages, the ones dashboards ask about most.

What to check in your own system

Look at pg_prepared_statements on a production connection to see whether the generic plan has taken over, then test your biggest tenant under both plan modes.

Source: boringSQL | Supercharge your SQL & PostgreSQL powers

Written by

Saksham Bhargava

Saksham Bhargava runs Alteroid and has been writing about consumer technology for over five years, covering iPhone, Android, Windows, AI tools and gaming. With an MCA and six years of hands-on web development experience, he tests the features he writes about on his own devices and focuses on clear, practical guides that solve real problems. Away from the screen, he enjoys travelling to places most tourists skip.