Taming Prepared Statement Re‑Planning with plan_cache_mode in YugabyteDB

Have you ever noticed a prepared statement in PostgreSQL (or YugabyteDB YSQL) that runs quickly at first but then, after a handful of executions, suddenly slows down?

This isn’t your imagination. It happens because PostgreSQL automatically decides, after a few runs, whether to keep optimizing a plan based on parameter values (custom plans) or to switch to a one‑size‑fits‑all generic plan.

Sometimes that “switch” works in your favor, but other times it makes performance worse.

With YugabyteDB 2025.1, which brings PostgreSQL 15 compatibility, you now have direct control over this behavior via the plan_cache_mode setting. You can force the database to always generate parameter‑sensitive custom plans, or always stick with a generic plan for predictable performance.

Before this release, you couldn’t control this at the server level… you had to rely on client/driver settings to work around it.

In today’s tip, we’ll walk through an example where the plan changes after the fifth execution, show you how to detect it, and demonstrate how to lock the behavior to avoid nasty surprises in production.

Why plans “flip” after ~5 executions

By default, (plan_cache_mode = auto) PostgreSQL/YugabyteDB uses a heuristic:

• For the first few runs, it builds custom plans tailored to each parameter value.
• After about five executions, it compares the cost of those custom plans against a generic plan.
• If the generic plan looks competitive, the server switches to it for subsequent executions.

That switch is often the cause of sudden performance regressions.

Note:

				
					yugabyte=# \x
Expanded display is on.
yugabyte=# SELECT setting, short_desc, extra_desc FROM pg_settings WHERE name = 'plan_cache_mode';
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------
setting    | auto
short_desc | Controls the planner's selection of custom or generic plan.
extra_desc | Prepared statements can have custom and generic plans, and the planner will attempt to choose which is better.  This can be set to override the default behavior.

Demo: watch the plan change, then lock it down

1) Setup

				
					-- Clean slate
DROP TABLE IF EXISTS demo_plan;
CREATE TABLE demo_plan (
id BIGSERIAL PRIMARY KEY,
k INT NOT NULL,
pad TEXT
);

-- Skewed distribution: many rows where k in [1..1000], but only a handful with k=999999
INSERT INTO demo_plan(k, pad)
SELECT (1 + (random()*999)::int), repeat('x', 50)
FROM generate_series(1, 200000);

-- Rare key (very selective)
INSERT INTO demo_plan(k, pad)
SELECT 999999, repeat('x', 50)
FROM generate_series(1, 20);

CREATE INDEX ON demo_plan(k);
ANALYZE demo_plan;

2) Observe default behavior (plan_cache_mode = auto)

				
					SET plan_cache_mode = auto;
PREPARE s(int) AS SELECT * FROM demo_plan WHERE k = $1;

Run a few custom‑plan‑friendly executions (the rare value), and a few generic‑friendly ones (common values).

Use EXPLAIN so we can see exactly which plan is used.

				
					-- Likely an Index Scan (custom plan considers the selective bind)
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(999999);

-- Common values (generic plan tends to be Seq Scan)
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(10);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(20);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(30);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(40);

After ~5 executions, PostgreSQL may decide the generic plan is good enough overall and start reusing it.

A quick tell:

• Custom plan output shows concrete values (e.g., k = 999999).
• Generic plan output retains placeholders (e.g., k = $1).

3) Prevent the flip: force a single plan policy

• Always generic (stable latency across values)

				
					SET plan_cache_mode = force_generic_plan;
DEALLOCATE s;
PREPARE s(int) AS SELECT * FROM demo_plan WHERE k = $1;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(999999);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(10);

You’ll see the same generic plan reused for all parameter values.

• Always custom (parameter‑sensitive, best per‑value performance)

				
					SET plan_cache_mode = force_custom_plan;
DEALLOCATE s;
PREPARE s(int) AS SELECT * FROM demo_plan WHERE k = $1;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(999999);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
EXECUTE s(10);

You’ll see planning happen per execution, with plans tailored to the current bind value.

Tip: Use ALTER ROLE/ALTER DATABASE ... SET plan_cache_mode = ... to make this stick for your app user(s) or database.

How to tell which plan you got

• EXPLAIN EXECUTE hint: if you see $1 in the filter (k = $1), that’s the generic plan. If you see a literal (k = 999999), that’s a custom plan.
• auto_explain: enable it to log plans that changed or exceeded a threshold. Great for catching plan flips in production (use with care).
• EXPLAIN (GENERIC_PLAN): Postgres 16+ adds this, but YugabyteDB 2025.1 (PG15) won’t have it yet. Use the $1 vs literal trick above.

When to choose which

• force_generic_plan: many different parameter values with similar selectivity; you prefer predictable performance and no per‑exec planning overhead.
• force_custom_plan: highly skewed data where rare values benefit from indexes and common values don’t; you want the best per‑value plan even if planning costs a bit.
• auto (default): safe baseline if you’re not seeing regressions, but be aware of possible plan flips under mixed workloads.

What to do before 2025.1

If your YugabyteDB build predates PostgreSQL 15 compatibility and does not expose plan_cache_mode, you can’t control this on the server. Instead, disable or defer server‑side preparing in your client/driver so the server never reaches the “flip to generic plan” logic:

• JDBC (pgjdbc): set prepareThreshold=0 (never create a server‑side prepared statement) or preferQueryMode=simple (simple protocol).
• psycopg 3: set prepare_threshold=None on the connection.
• Go pgx: set PreferSimpleProtocol=true (or disable the statement cache).
• libpq / others: use parameterized queries without long‑lived prepared statements (unnamed / one‑shot statements), or rely on the simple query protocol where appropriate.

These avoid the server plan cache and the generic‑plan switch entirely.

Takeaways

• YugabyteDB 2025.1 (PG15) brings plan_cache_mode, which lets you freeze the policy: generic‑only or custom‑only.
• If you’re on an earlier build, use driver flags to avoid long‑lived prepared statements on the server.
• Watch for $1 vs literals in EXPLAIN EXECUTE to tell which plan is active.