A single fast query on a single tenant does not prove you fixed a Postgres plan-caching problem. Postgres decides between a custom plan and a cached generic plan by comparing average estimated cost across executions, so a change that rescues your smallest tenant can quietly add planning work to the tenant that generates 95% of your traffic. The test that actually settles it warms one connection past the plan switch, replays low, medium, and high selectivity parameters, and asserts latency, rows, buffers, and plan shape — not just wall-clock on the one case that hurt.
A reader made this point on an earlier post of mine about prepared statements, and it's the part I had underbuilt. Below is the harness I use now.
Why doesn't one fast query prove the fix?
Postgres builds a custom plan (re-planned with your actual parameter values) for the first five executions of a prepared statement, averages their estimated cost, and from the sixth execution onward compares that average against a generic plan built with no knowledge of the values. If the generic plan doesn't look more expensive, it locks in.
Two consequences fall out of that mechanism, and both break naive tests:
- The decision is made on an average. Whether a generic plan wins depends on which parameters warmed the statement. Warm it with your rare, highly selective tenant and you may get a different outcome than production, where 95% of executions carry the dominant value.
-
Forcing custom plans is not free.
plan_cache_mode = force_custom_planmeans re-planning on every execution. For a query planned in 0.4 ms and executed in 900 ms, that's noise. For a short OLTP query planned in 3 ms and executed in 1.2 ms, you just tripled its cost — for the workload that dominates your CPU.
Takeaway: the fix and the regression are the same change viewed from two different parameter distributions, so the test has to carry both.
How do you make a test session use the cached generic plan?
The hard part is that plan caching is connection-local state. Open a fresh connection, run the query once, and you measure a custom plan — the exact thing that never reproduces the bug. You have to warm past the switch inside the same session, using the parameter distribution production actually sends.
The most direct way to do that is a SQL-level prepared statement, which puts the counters somewhere you can read:
PREPARE q(text) AS
SELECT id, status, created_at
FROM events
WHERE tenant_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;
-- warm with the DOMINANT parameter, not the pathological one
EXECUTE q('bigcorp'); -- x6
SELECT name, custom_plans, generic_plans
FROM pg_prepared_statements WHERE name = 'q';
pg_prepared_statements exposes custom_plans and generic_plans counters in PostgreSQL 14 and later, and they are the only honest answer to "did my warmup take?" If generic_plans is still 0 after warmup, your test is measuring a custom plan and every assertion below it is meaningless.
What tripped me up the first time: the plan cache is keyed to the statement, and EXPLAIN (ANALYZE) SELECT … is a different statement from SELECT …. If you warm the raw query and then inspect with an EXPLAIN-wrapped copy, you are inspecting a freshly planned custom plan and will conclude, wrongly, that everything is fine. Prepare once, then EXPLAIN the EXECUTE.
Takeaway: assert on generic_plans > 0 before you assert on anything else, because a warmup that silently failed produces a green test that proves nothing.
Building fixtures from the real parameter distribution
Three buckets is usually enough, drawn from your actual data rather than invented:
| Bucket | How to pick it | What it catches |
|---|---|---|
| High selectivity (rare value) | A parameter matching a tiny fraction of rows | The original pathology — generic plan sequential-scans for a 400-row answer |
| Median | The value nearest the median row count per key | Drift in the middle of the distribution as data grows |
| Dominant (hot value) | The value your logs show most often, usually also the largest | Regression from the fix — added planning cost on the hot path |
Pull them with one query rather than guessing:
SELECT tenant_id, count(*) AS rows
FROM events
GROUP BY tenant_id
ORDER BY rows DESC;
This only works against a database whose statistics resemble production. A uniformly seeded test dataset cannot reproduce a skew bug, because with uniform data the generic plan is genuinely correct. If you can't run against a production-shaped copy, this whole class of test is theater — restore a sanitized dump instead.
Takeaway: fixtures chosen from count(*) GROUP BY are evidence; fixtures chosen because they looked representative are assumptions.
What should the test assert besides latency?
Latency alone is flaky under CI noise. Assert the physical work and the plan shape too — those are stable enough to gate a merge on.
# plan_stability_test.py (psycopg 3)
import psycopg
from psycopg import sql
DSN = "postgresql:///app" # same role and database as the app
STMT = """
SELECT id, status, created_at
FROM events
WHERE tenant_id = $1 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50
"""
# label, parameter, max_ms, max_shared_blocks, expected plan family
BUCKETS = [
("rare", "acme", 15.0, 400, "Limit[IndexScan(events_tenant_status_created_idx)]"),
("median", "midco", 60.0, 5000, "Limit[IndexScan(events_tenant_status_created_idx)]"),
("dominant", "bigcorp", 250.0, 60000, "Limit[IndexScan(events_tenant_status_created_idx)]"),
]
def plan_family(node):
"""Node-type fingerprint: ignores row counts, catches plan-shape changes."""
label = node["Node Type"].replace(" ", "")
if "Index Name" in node:
label += f"({node['Index Name']})"
kids = ",".join(plan_family(k) for k in node.get("Plans", []))
return f"{label}[{kids}]" if kids else label
def explain_execute(cur, param):
q = sql.SQL("EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON) EXECUTE q({})")
cur.execute(q.format(sql.Literal(param)))
return cur.fetchone()[0][0]
def main():
failures = []
with psycopg.connect(DSN, autocommit=True) as conn, conn.cursor() as cur:
cur.execute("PREPARE q(text) AS " + STMT)
# warm past the switch using the dominant parameter
for _ in range(6):
cur.execute(sql.SQL("EXECUTE q({})").format(sql.Literal("bigcorp")))
cur.fetchall()
cur.execute("SELECT custom_plans, generic_plans "
"FROM pg_prepared_statements WHERE name = 'q'")
custom, generic = cur.fetchone()
print(f"warmup: custom_plans={custom} generic_plans={generic}")
for label, param, max_ms, max_blocks, expected in BUCKETS:
runs = [explain_execute(cur, param) for _ in range(5)]
plans = [r["Plan"] for r in runs]
ms = sorted(r["Execution Time"] for r in runs)[len(runs) // 2]
blocks = plans[0]["Shared Hit Blocks"] + plans[0]["Shared Read Blocks"]
family = plan_family(plans[0])
print(f"{label:9} {ms:8.2f} ms {blocks:7} blocks {family}")
if ms > max_ms:
failures.append(f"{label}: {ms:.1f}ms > {max_ms}ms")
if blocks > max_blocks:
failures.append(f"{label}: {blocks} blocks > {max_blocks}")
if family != expected:
failures.append(f"{label}: plan family {family} != {expected}")
if failures:
raise SystemExit("PLAN REGRESSION\n " + "\n ".join(failures))
print("ok")
if __name__ == "__main__":
main()
The fingerprint matters more than it looks. Asserting on raw EXPLAIN text fails every time a row estimate shifts; asserting on node types plus index name fails only when the plan genuinely changes shape — which is exactly the event you want a build to stop for. If EXPLAIN ANALYZE's per-row timing overhead distorts your latency numbers, run the same harness with TIMING OFF and gate on buffers and plan family alone.
Takeaway: buffers and node types are the assertions that survive a noisy CI runner; latency is the one you keep loose.
Which knob do you actually reach for?
| Option | What it does | Cost | Reach for it when |
|---|---|---|---|
plan_cache_mode = auto (default) |
Five custom plans, then compares | None | Data is roughly uniform across the parameter |
plan_cache_mode = force_custom_plan |
Re-plans every execution | Planning time on every call | Skew is severe and execution dominates planning |
Set it per-statement via SET LOCAL
|
Scopes the override to one transaction | Same, but bounded | Only one or two queries are pathological |
| Disable server-side prepares in the driver | No plan cache at all | Loses parse-time reuse everywhere | You need a fast, blunt rollback |
Scope beats globals here. SET LOCAL plan_cache_mode = force_custom_plan inside the transaction that runs the skewed query fixes that query and leaves the rest of your workload on the default — and it shows up in EXPLAIN (SETTINGS) output, so your harness can verify the setting was actually in effect rather than assume it.
For continuous coverage between CI runs, the in-tree auto_explain module logs real plans for slow executions with no extra infrastructure, at the price of log volume you have to manage. If you want plan history retained and compared over time without building that yourself, pganalyze is the managed option that tracks per-query plan changes, with the usual trade-off of another vendor in your data path.
Takeaway: a per-transaction SET LOCAL you can verify is safer than a global GUC you have to remember.
When "same SQL" isn't the same query
The last thing to diff is the session contract, because two connections can run byte-identical SQL against different relations:
SELECT current_user, session_user, current_setting('search_path') AS search_path;
SELECT name, setting FROM pg_settings
WHERE name IN ('plan_cache_mode','row_security','work_mem','TimeZone',
'random_page_cost','effective_cache_size','jit','enable_seqscan')
ORDER BY name;
Three of these bite regularly. A different search_path resolves an unqualified table name to a different schema. Row-level security applies to the app role but is bypassed by the table owner, so a test run as owner sees a table with no policy predicates attached — a different plan by construction. And TimeZone changes what a date_trunc or range predicate actually matches, which changes selectivity.
Run the harness as the application role, against the application database, or you are testing a query that doesn't exist in production.
Takeaway: connect as the app role, or your plan test is measuring a query production never runs.
FAQ
How do I check if Postgres is using a generic or custom plan?
Query pg_prepared_statements for the custom_plans and generic_plans columns, available in PostgreSQL 14 and later. For a statement you prepared with PREPARE, EXPLAIN (ANALYZE) EXECUTE stmt(...) shows the plan being used; a generic plan displays $1 in place of the parameter value.
Does plan_cache_mode = force_custom_plan hurt performance?
Yes, for short queries. It re-plans on every execution, so a query that plans in 3 ms and runs in 1 ms becomes roughly four times more expensive. Use SET LOCAL to scope it to the transaction running the skewed query instead of setting it globally.
Why is my query fast in psql but slow from the application?
psql sends literal values, so the planner uses column statistics for that specific value. Your driver sends the query as a prepared statement with bound parameters, and after five executions Postgres may switch to a generic plan built without knowledge of those values — which is catastrophic when your data is skewed.
Bottom line
If you're about to change a plan-caching setting, spend the extra hour on the harness rather than the extra hour re-reading EXPLAIN output. Warm one connection past the switch with the dominant parameter, replay rare/median/dominant fixtures pulled from a real GROUP BY, and assert on plan family and buffers with a loose latency bound. Verify generic_plans > 0 before trusting a single number below it, and run as the application role so RLS and search_path match production. Teams with one pathological query should reach for SET LOCAL plan_cache_mode; teams that keep rediscovering this should keep the harness in CI, because tenant distributions drift and the bug comes back on its own.
Top comments (0)