Prepared Statements and Partition Pruning #

A query against a table partitioned by day runs in two milliseconds from psql and forty from the application. The plan from psql touches one partition; the application’s touches all one thousand and four, then discards them at runtime. Same query, same data — the difference is that one was planned with a value and the other with a parameter.

Plan caching generally is covered in prepared statement plan pinning, and pruning in partition pruning analysis. This page is where the two meet.

The Condition #

PostgreSQL prunes partitions at two different times:

A generic plan is by definition built without knowing the parameter values, so it can never prune at plan time. It relies on run-time pruning, which works — but the plan itself still contains every partition, and that has costs:

Run-time pruning has been steadily improved and is efficient in modern versions, so the generic plan is frequently fine. The cases where it is not are those with very high partition counts or very short executions, where the fixed overhead is comparable to the work.

Where the pruning happens With a custom plan the parameter value is known at planning time, so only matching partitions appear in the plan and the Append node has one child. With a generic plan every partition appears in the plan and the executor removes the non-matching subplans at run time, reporting Subplans Removed. custom plan value known when planning Append has 1 child small plan, few locks replanned per execution generic plan value unknown when planning Append has all 1,004 children Subplans Removed: 1003 planned once, reused both return the same rows; the overhead differs

Annotated Evidence #

The custom plan, with a literal:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM events WHERE occurred_at = '2026-09-16 10:00:00+00' AND device_id = 4821;
Index Scan using events_2026_09_16_device_idx on events_2026_09_16  (actual rows=1 loops=1)
  Buffers: shared hit=4
Planning Time: 0.412 ms
Execution Time: 0.061 ms
-- one partition in the plan: pruning happened while planning

The generic plan, through a prepared statement:

PREPARE q(timestamptz, int) AS
  SELECT id, payload FROM events WHERE occurred_at = $1 AND device_id = $2;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q('2026-09-16 10:00:00+00', 4821);
Append  (actual time=0.028..0.031 rows=1 loops=1)
  Subplans Removed: 1003
  ->  Index Scan using events_2026_09_16_device_idx on events_2026_09_16 events_1  (actual rows=1 loops=1)
        Buffers: shared hit=4
Planning Time: 0.031 ms
Execution Time: 0.082 ms
-- all 1,004 partitions are in the plan; 1,003 removed at run time
-- planning is now nearly free, execution slightly more expensive

And the plan cache’s own view:

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
 name | generic_plans | custom_plans
------+---------------+--------------
 q    |           412 |            5
-- after five custom plans the generic plan was judged no worse and took over
Three lines that tell you which Three items are annotated. Subplans Removed appearing in the plan indicates run-time pruning, which means a generic plan. An Append node with a single child and no Subplans Removed line indicates plan-time pruning. Planning time near zero with a large plan confirms a cached generic plan is being reused. Subplans Removed: 1003 run-time pruning: generic plan Append with one child, no removal line plan-time pruning: custom plan Planning Time 0.031 ms on a 1,004-way Append cached plan reused both prune correctly; the cost profile differs

Step-by-Step Resolution #

  1. Identify which you are getting. Subplans Removed means run-time pruning and a generic plan. Its absence with a single-partition Append means plan-time pruning.

  2. Measure whether it matters. Compare EXECUTE timings with the equivalent literal query. For most workloads the difference is microseconds and no action is warranted.

  3. Where it does matter, force a custom plan for that statement:

    SET plan_cache_mode = force_custom_plan;      -- session, or SET LOCAL per transaction

    Planning cost returns, so this is a trade: planning time against execution overhead and estimate quality.

  4. Reduce the partition count where it is the real problem. A thousand daily partitions covering three years is usually better served by monthly partitions for older data — and that helps both plan shapes.

  5. Check that run-time pruning is actually happening. It requires the partition key to be compared with the parameter directly; wrapping it in a function or an expression that is not immutable defeats it, and the plan then scans every partition for real.

  6. Watch planner memory with EXPLAIN (MEMORY) on the generic plan. If it is in the hundreds of megabytes, the partition count is the problem regardless of pruning.

  7. Re-measure after any partitioning change, since both plan shapes change with the partition count.

What run-time pruning costs as partitions grow For a point lookup, a generic plan over 12 partitions adds about 0.01 milliseconds over a custom plan. Over 365 partitions it adds 0.02. Over 1,004 partitions it adds 0.05. Over 5,000 partitions it adds 0.3 milliseconds, at which point it is comparable to the query itself. 12 partitions +0.01 ms 365 partitions +0.02 ms 1,004 partitions +0.05 ms 5,000 partitions +0.3 ms the query itself takes 0.06 ms; the last row doubles it several times over

Before and After #

-- BEFORE: generic plan over 5,000 partitions
Append  Subplans Removed: 4999   Execution Time: 0.382 ms   Planning Time: 0.03 ms

-- AFTER: monthly partitions for archive data, 220 partitions total
Append  Subplans Removed: 219    Execution Time: 0.074 ms   Planning Time: 0.03 ms

When forcing a custom plan is the right call #

force_custom_plan is worth the planning cost in two situations. The first is a skewed partition key, where the average selectivity a generic plan assumes is wrong for most actual values — a partitioned table where one partition holds ninety per cent of the rows makes the generic plan’s estimates wrong for every query against the other partitions and for the big one alike.

The second is a statement executed rarely but expensively. A report run four times a day gains nothing from a cached plan and can lose a great deal from a generic one; the planning time is irrelevant next to the execution.

Neither applies to the common case — a frequent point lookup — where the generic plan is both cheaper and adequate. Setting plan_cache_mode globally is almost always wrong for that reason; it belongs on the statement or the role that needs it, through SET LOCAL in the transaction or a role default for a reporting role.

Common Pitfalls #

Assuming a cached plan cannot prune. Run-time pruning handles it. Diagnostic signal: worry about Append width with Subplans Removed present. Fix: measure before acting.

Defeating run-time pruning with an expression. Wrapping the partition key in a non-immutable function prevents it. Diagnostic signal: no Subplans Removed line and every partition scanned. Fix: compare the key directly.

Setting plan_cache_mode globally. Every statement pays planning cost. Diagnostic signal: total_plan_time rising across the workload. Fix: scope it to the statement or role.

Treating partition count as free. It costs planning memory, locks and executor setup. Diagnostic signal: planner memory in the hundreds of megabytes. Fix: coarser partitions for cold data.

Frequently Asked Questions #

Do prepared statements prevent partition pruning? #

No. A generic plan cannot prune while planning, because the values are unknown, but the executor prunes at run time and reports it as Subplans Removed. The plan is larger and setup is slightly more expensive; the partitions are still skipped.

What does Subplans Removed mean in EXPLAIN output? #

It reports run-time partition pruning: the number of partition subplans the executor discarded once parameter values were known. Its presence indicates the plan was built without those values, typically a generic cached plan.

When should I force a custom plan for a partitioned table? #

When the partition key is skewed enough that average selectivity misleads the planner, or when the statement is rare and expensive so planning cost is irrelevant. Scope it with SET LOCAL or a role default rather than globally.

Up: Prepared Statement Plan Pinning