Why Your Query Is Fast in psql but Slow From the App: Postgres Plan Caching Explained

If a query is instant when you paste it into psql but slow when your application runs it, the plan is almost never the problem you think it is. The most common cause is that your driver sent it as a prepared statement, and after a few executions Postgres switched from a plan tuned to your actual parameter values to a generic plan built without them. The fix is usually a one-line setting, not a new index.

I lost the better part of a day to this on a table with heavily skewed data. Same database, same user, same query text, 300x difference in latency depending on who was asking.

What the symptom actually looks like

The tell is that the two environments disagree while everything you can see is identical:

-- in psql: 2.8 ms
SELECT id, status, created_at FROM events
WHERE tenant_id = 'acme' AND status = 'pending'
ORDER BY created_at DESC LIMIT 50;

The application logs the same statement at 800–1200ms. There's an index on (tenant_id, status, created_at DESC). EXPLAIN in psql shows a clean index scan. Nothing in the app is doing anything exotic — one query, 50 rows.

What differs is how the statement reaches the server. In psql you sent literal values, so the planner saw tenant_id = 'acme' and could use the column statistics for that specific value. Your driver sent WHERE tenant_id = $1 with the value bound separately. Postgres plans that once and may reuse the plan.

Takeaway: when psql and the app disagree on speed for the same SQL, stop looking at indexes and start looking at parameter binding.

How does Postgres decide to reuse a plan?

For a prepared statement, Postgres builds a custom plan (re-planned with the actual parameters) for the first executions, tracks their estimated cost, and compares that average against the cost of a generic plan built with no knowledge of the values. From the sixth execution onward, if the generic plan doesn't look more expensive on average, it locks in and stops re-planning. That threshold is a fixed number in the source, not a knob you tune.

This is a good trade when data is uniform. It is destructive when it isn't. If 98% of your rows are tenant_id = 'bigcorp', the generic plan is built around the average selectivity across all tenants, and the planner concludes a sequential scan or a bitmap heap scan is reasonable. For acme — 400 rows out of 40 million — that plan is catastrophic, and it will be reused for the entire life of that connection.

Two more things that only bite the parameterized version:

• Partial indexes stop matching. An index defined WHERE status = 'pending' can be matched against a literal status = 'pending' but not against status = $2, because at generic-plan time the planner doesn't know $2 is 'pending'.
• LIKE prefix optimization disappears. LIKE 'abc%' can be rewritten into a range scan; LIKE $1 cannot, without the value.

Takeaway: a generic plan is planned for your average row, so any column with skewed values is a landmine.

How do I prove this is what's happening?

Don't guess from application timings. Reproduce it in a single psql session — the behavior is per-connection, and you can drive it by hand:

PREPARE q(text, text) AS
  SELECT id, status, created_at FROM events
  WHERE tenant_id = $1 AND status = $2
  ORDER BY created_at DESC LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS) EXECUTE q('acme', 'pending');  -- run this 6+ times

Run it repeatedly and watch the plan. If execution 1 shows an Index Scan and execution 6 flips to a Seq Scan or a Bitmap Heap Scan, you have your answer — and you've reproduced it without touching the app.

On Postgres 16 and later you can skip the ritual and ask directly:

EXPLAIN (GENERIC_PLAN)
  SELECT id FROM events WHERE tenant_id = $1 AND status = $2
  ORDER BY created_at DESC LIMIT 50;

To catch it in production instead of on your laptop, enable auto_explain and have the slow plans logged as they happen:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '200ms';
SET auto_explain.log_analyze = on;

Loading it per-session like this is the safe way to test; making it permanent means adding it to shared_preload_libraries and a restart, and log_analyze adds real per-query overhead, so keep the duration threshold high on a busy server. If you want to know which statements to point it at first, pg_stat_statements is the extension that tells you where the time actually goes — it aggregates by normalized query text, so the parameterized version shows up as one row with its own mean and max.

Takeaway: PREPARE plus six EXECUTEs in one psql session reproduces the bug in under a minute, which is faster than any amount of reading application logs.

What do I actually change?

Situation	Fix	Cost of the fix
Skewed column, plan flips to generic	plan_cache_mode = force_custom_plan	Re-plans every execution (planning is ~µs–low ms)
Only one or two queries affected	Set plan_cache_mode on that session/transaction only	Needs a code path to scope it
Driver prepares everything by default	Lower or disable the driver's prepare threshold	Loses prepared-statement parse savings
PgBouncer in transaction mode	Configure prepared-statement support, or stop using named statements	Version-dependent; see below
Plan is fine, stats are stale	ANALYZE, raise default_statistics_target on that column	More planning time, better estimates

The blunt instrument, available since Postgres 12:

-- per session, or per role/database via ALTER ROLE ... SET
SET plan_cache_mode = force_custom_plan;

I default to scoping this narrowly. Setting it database-wide fixes the skewed queries and quietly taxes every other prepared statement with re-planning forever. ALTER ROLE app_worker SET plan_cache_mode = 'force_custom_plan' on the specific worker role that runs the tenant-scoped queries is usually the right blast radius.

Where the driver sits matters as much as the server setting:

• node-postgres (pg) only creates a named prepared statement when you pass a name in the query config. Plain client.query(text, values) is unnamed — parsed each time, planned with the values, immune to this problem.
• The PostgreSQL JDBC driver switches to a server-side prepared statement after a handful of executions of the same statement, controlled by prepareThreshold. Setting it to 0 disables server-side preparation entirely.
• ORMs on top of connection poolers are where this gets ugly. If you run PgBouncer in transaction pooling mode, named prepared statements historically broke outright because the server connection underneath you changes between transactions. PgBouncer added support for protocol-level prepared statements in 1.21 (released late 2023), gated behind max_prepared_statements, which defaults to 0 — meaning off unless you turned it on. If you want a pooler where this isn't a running concern, PgBouncer with max_prepared_statements configured explicitly is the setup that keeps prepared statements and transaction pooling working together instead of forcing you to choose.

Takeaway: fix it at the narrowest scope that works — one role or one query beats a database-wide setting you'll forget you set.

What if it isn't the plan cache?

Before you go changing planner settings, rule out the other reasons psql and your app disagree. In rough order of how often I've actually hit them:

1. You're not measuring the same thing. App timing usually includes connection acquisition, TLS, row serialization, and ORM hydration. A query that's 3ms at the server and 900ms in your log may be spending 890ms turning rows into objects.
2. Different search_path or role. The app connects as a different role and hits a different schema — often an unindexed copy in a test schema.
3. Session GUCs. work_mem set per-role changes whether a sort spills to disk. statement_timeout masks the real duration.
4. Connection acquisition, not query time. If the pool is exhausted, every query looks slow. pg_stat_activity will show sessions waiting, not running.
5. Actual row counts differ. Your app pages through 50,000 rows; you tested LIMIT 50.

The clean way to separate these: log the server-side duration with log_min_duration_statement and compare it against the duration your application recorded. If the server says 3ms and your app says 900ms, the database is innocent.

Takeaway: confirm the server itself is slow before you tune the server.

FAQ

Why is my query fast in pgAdmin but slow in my application?
Almost always because your application sends the query as a parameterized prepared statement and the GUI tool sends literal values. After roughly five executions Postgres may switch that prepared statement to a generic plan built without your parameter values, which performs badly on columns with skewed data. Reproduce it with PREPARE and repeated EXECUTE in psql.

How do I stop Postgres from using a generic plan?
Set plan_cache_mode = force_custom_plan, available since Postgres 12. Scope it to the session, transaction, or role that runs the affected queries rather than setting it database-wide, since it forces re-planning on every execution.

Does PgBouncer break prepared statements?
In transaction pooling mode it did, because the underlying server connection changes between transactions. PgBouncer 1.21 added support for protocol-level prepared statements via the max_prepared_statements setting, which is disabled by default — you have to set it to a non-zero value explicitly.

Bottom line

If psql is fast and your app is slow on identical SQL, check parameter binding before you add an index. Reproduce it with PREPARE plus six EXECUTEs, and if the plan flips, set plan_cache_mode = force_custom_plan on the narrowest scope that covers the offending queries — a role, ideally, not the whole database. If the plan doesn't flip, the problem is on your side of the wire: measure server-side duration with log_min_duration_statement and compare, because ORM hydration and pool waits both look exactly like a slow query from the application's point of view.

Related reading

• When Is GitHub Copilot Actually Worth the Subscription? A Break-Even Analysis
• Add Full-Text Search to Your App Before Reaching for Elasticsearch
• When Should You Move Off Managed Hosting to Kubernetes? The Thresholds That Actually Matter

Comments

[Mads Hansen]

The single-session reproduction is excellent because it preserves the connection-local state that app-vs-psql tests usually erase.

I would add two controls before forcing custom plans. First, replay the real parameter distribution—not only one small tenant—because PostgreSQL's choice is based on average estimated custom-plan cost versus the generic plan. A fix that helps the long tail can add planning cost to the dominant workload. Second, capture pg_prepared_statements counters (custom_plans/generic_plans) plus EXPLAIN (ANALYZE, BUFFERS, SETTINGS) for representative selectivity buckets.

Also diff the app session contract: role, search_path, row-security state, time zone, and planner-related GUCs. Those can make “same SQL” mean a different relation or policy surface.

A good regression test warms one connection past the plan switch, runs low/medium/high-selectivity fixtures, and asserts latency, rows read, buffer hits, and plan family. That catches the issue again when tenant distribution or statistics change.

[Libme]

The point about the planner comparing average estimated custom-plan cost against the generic plan is the one people miss most — it explains why a fix tuned on one skewed tenant can quietly regress the dominant workload, and it's exactly why replaying the real parameter distribution matters more than picking a "bad" case. One thing I'd pin down alongside your session contract diff: the prepared-statement lifecycle at the driver and pooler layer. The five-execution warmup only happens if the statement stays named and alive on the same backend, so a driver that uses the unnamed statement, re-prepares per call, or sits behind transaction-mode pooling may never reach the generic plan at all — which means the same SQL can show the symptom in one deployment and not another, and a regression test that warms its own connection can pass while production never gets there. Checking that the statement actually persists (and that nothing is issuing DEALLOCATE ALL between calls) is worth doing before you conclude anything from the counters. Your fixture design with low/medium/high-selectivity buckets asserting plan family alongside latency is the part I'd steal — asserting on the plan shape rather than just timing is what keeps the test from going green on a fast machine.