PostgreSQL query is fast in psql but slow from the app: generic plans and plan_cache_mode

#Symptom

• The same query takes 3 ms in psql, and 30 seconds in the application.
• It's fast just after a connection opens, and then it slows down for that connection.
• Restarting the app or the pool helps for a short while.
• Only some values, such as one large tenant, are slow.

The likely cause is a prepared statement that has switched to a generic plan.

#How it works

When you pass values through bind parameters (WHERE tenant_id = $1), PostgreSQL can plan in one of two ways:

Under the default plan_cache_mode = auto, the first five executions use custom plans. From the sixth, the planner compares the estimated cost of a generic plan with the average cost of those custom plans. If the generic one doesn't look more expensive, it switches, and it may keep using that plan.

The problem comes from skewed data. Five executions with small tenants produce a cheap average, so a generic plan looks fine. The sixth call is for a tenant with 40% of the table.

psql usually sends literal values, so it never runs into this. That's why your test looks fine.

#Step 1: reproduce it

Copy the query, and use a value that's slow in production:

PREPARE q(bigint) AS
  SELECT * FROM orders
  WHERE tenant_id = $1 AND status = 'OPEN'
  ORDER BY created_at DESC
  LIMIT 50;

-- Run 5 times so the planner has its average
EXECUTE q(1001);
EXECUTE q(1001);
EXECUTE q(1001);
EXECUTE q(1001);
EXECUTE q(1001);

EXPLAIN (ANALYZE, BUFFERS) EXECUTE q(1001);

Compare the plans directly by forcing each kind:

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q(1001);

SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q(1001);

RESET plan_cache_mode;
DEALLOCATE q;

How to read this: a generic plan shows $1 in its conditions, and a custom plan shows the actual value, 1001.

Index Cond: (tenant_id = $1)     -- generic
Index Cond: (tenant_id = 1001)   -- custom

On PostgreSQL 16 and later, you can also view a generic plan without values:

EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE tenant_id = $1;

See reading EXPLAIN ANALYZE for more help.

Run it during a quiet period, because EXPLAIN ANALYZE actually executes the statement, and wrap any write in a transaction that you roll back.

#Step 2: confirm it in production

Check a session's statements, from that session. On PostgreSQL 14 and later, this view has plan counters:

SELECT name, statement, generic_plans, custom_plans
FROM pg_prepared_statements;

To catch the bad plan as it happens, use auto_explain:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '2s';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

That example is per session. For the app, set it through session_preload_libraries or your provider's parameters. log_analyze adds overhead, so don't leave it on with a low threshold.

Also compare the mean and maximum in pg_stat_statements. A big gap suggests that the same statement has good and bad plans:

SELECT calls, mean_exec_time, max_exec_time, stddev_exec_time, left(query, 100) AS query
FROM pg_stat_statements
ORDER BY stddev_exec_time DESC
LIMIT 10;

#Step 3: check how your driver prepares

Most drivers prepare statements automatically, and each one has its own rules. Defaults can change, so check the version you're using.

With a threshold of 5 in the driver, plus 5 custom executions in the server, you'd see the problem around the 10th or 11th execution on each connection.

PgBouncer adds another layer. In transaction mode, statements were historically not safe to use across connections. Version 1.21 and later can track protocol-level prepared statements using max_prepared_statements. See PgBouncer pooling.

#Step 4: choose a fix

Start with the narrowest fix that works.

#Fix A: improve the estimates

A generic plan is built from average selectivity. Better statistics can make the right plan look right for all values:

ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 1000;

CREATE STATISTICS orders_tenant_status (dependencies, mcv)
  ON tenant_id, status FROM orders;

ANALYZE orders;

See planner statistics. This may not be enough with very skewed data, but it's cheap to try.

#Fix B: add a better index

A good composite index can make one plan safe for everyone:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

With this, tenant_id = $1 AND status = 'OPEN' ORDER BY created_at DESC LIMIT 50 reads only a few rows for any tenant. Often, this is the best fix of all. If the build fails, see invalid indexes.

#Fix C: force custom plans where needed

plan_cache_mode can be set at several levels. Start narrowly:

-- Only this transaction
BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;
-- run the query
COMMIT;

-- One reporting role
ALTER ROLE reporting_app SET plan_cache_mode = force_custom_plan;

-- One function
ALTER FUNCTION get_orders(bigint) SET plan_cache_mode = force_custom_plan;

New sessions pick up role settings, so recycle the pool afterward. In your pool, also check that SET statements won't leak to other clients. Use SET LOCAL with PgBouncer in transaction mode.

Warning

Don't set force_custom_plan across the whole server without measuring it. A busy OLTP system gains real CPU savings from generic plans. Forcing custom plans everywhere can raise CPU use and latency on every query. Set it on the role or function that needs it.

#Fix D: send literal values or disable preparing

For one query, the driver may be able to skip preparing:

# psycopg 3
cur.execute(sql, params, prepare=False)

# JDBC, for the whole connection
jdbc:postgresql://db01/app?prepareThreshold=0

A connection-wide change affects every query on it, so test it first. Don't build SQL by string concatenation to get literal values, as that opens the door to SQL injection.

#Fix E: split the path

For a known large tenant, a separate query or role can have its own settings. It's more code, so keep it as the last resort.

#Partitioned tables

Generic plans can't prune partitions at planning time, because they don't know the value. PostgreSQL can usually still prune at execution time, shown as Subplans Removed in the plan. Planning and locking costs can remain, though, particularly with many partitions. See declarative partitioning.

#Verify the fix

• Run EXECUTE more than five times, and confirm that the plan stays good.
• In the app, watch pg_stat_statements for lower max time and standard deviation. Reset the stats first if that's acceptable.
• Test with your largest tenants and your smallest.
• Monitor CPU if you've forced custom plans.

#Prevent it next time

• Test queries with bind parameters, not literals, and run them more than 10 times on one connection.
• Test using skewed data that looks like production.
• Add composite indexes that match your filters and sort order.
• Use auto_explain with a sensible threshold, so you have plans during incidents. Also see the slow performance triage runbook.