Planning Overhead with Many Partitions #

A point lookup on a daily-partitioned table with four years of history takes 38 ms, of which 37 ms is planning. The scan itself reads four buffers from one partition. Pruning works perfectly — and the planner still had to consider 1,460 partitions, lock their catalog entries, and prune them, for every execution of a query that returns one row.

Pruning mechanics are covered in partition pruning analysis. This page is about the cost that scales with the number of partitions rather than with the data.

The Condition #

Partitioned tables cost planning work proportional to the partition count, in several places:

The practical consequences: statements that plan on every execution — anything not using prepared statements, and generic plans that are re-planned after invalidation — pay this cost every time. On an OLTP workload issuing thousands of queries a second against a heavily partitioned table, planning can dominate CPU.

The mitigations are limiting the count, enabling plan reuse so planning is amortised, and turning off partitionwise features when they are not needed.

Planning time by partition count For the same one-row lookup, planning takes 0.2 milliseconds with 12 partitions, 1.1 milliseconds with 120, 9.4 milliseconds with 730 and 37 milliseconds with 1,460. Execution stays at 0.05 milliseconds throughout. 12 partitions 0.2 ms planning 120 partitions 1.1 ms 730 partitions 9.4 ms 1,460 partitions 37 ms execution time is 0.05 ms in every case

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, SUMMARY, MEMORY)
SELECT id, payload FROM events WHERE occurred_at = '2026-09-16 10:00:00+00' AND device_id = 4821;
Append  (actual time=0.03..0.04 rows=1 loops=1)
  ->  Index Scan using events_2026_09_16_device_idx on events_2026_09_16 events_1
        (actual rows=1 loops=1)
        Index Cond: ((device_id = 4821) AND (occurred_at = '2026-09-16 10:00:00+00'::timestamptz))
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=18402
  Memory: used=148204kB  allocated=152064kB
Planning Time: 37.104 ms
Execution Time: 0.061 ms
-- planning touched 18,402 catalog buffers and 148 MB for a four-buffer query

After consolidating to monthly partitions:

Planning:
  Buffers: shared hit=412
  Memory: used=3204kB  allocated=3328kB
Planning Time: 0.402 ms
Execution Time: 0.058 ms

Lock counts confirm the same story:

SELECT count(*) FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'relation';
-- daily partitions:   1,494
-- monthly partitions:    52
Where the partition count shows up Three items are annotated. Planning time far above execution time. Planning buffers in the thousands from catalog lookups. Relation locks numbering more than the partitions scanned, because every partition is locked during planning. Planning Time: 37.1 ms vs 0.06 ms execution planning dominates Planning: Buffers: shared hit=18402 catalog lookups per partition 1,494 relation locks for one row every partition locked EXPLAIN (MEMORY) is available from PostgreSQL 17

Step-by-Step Resolution #

  1. Measure planning separately with EXPLAIN (ANALYZE, SUMMARY); compare Planning Time with Execution Time for the workload’s most frequent statements.

  2. Count the partitions and locks:

    SELECT count(*) FROM pg_inherits WHERE inhparent = 'events'::regclass;

  3. Choose a coarser granularity. Monthly instead of daily reduces the count twelve-fold; the pruning benefit is usually unchanged because queries span days, not hours.

  4. Use prepared statements so planning is amortised across executions. A generic plan for a partitioned table is planned once and reused — with the caveat that it prunes at execution time rather than plan time.

  5. Disable partitionwise features when unused:

    SET enable_partitionwise_join = off;
SET enable_partitionwise_aggregate = off;

    They multiply planning work and pay off only when join or grouping keys align with the partition key, as described in partitionwise join and aggregate plans.

  6. Detach and archive old partitions rather than keeping years of them attached; a detached partition costs nothing to plan.

  7. Re-measure planning time after each change; it responds immediately.

Four levers, in order Four steps. Measure planning time against execution time. Reduce the partition count by coarser granularity or by detaching old partitions. Reuse plans through prepared statements. Disable partitionwise planning features when they are not used. measure planning vs execution fewer partitions coarser or detached reuse plans prepared statements disable partitionwise if unused the first lever is usually the largest

Before and After #

-- BEFORE: 1,460 daily partitions
Planning Time: 37.104 ms   Execution Time: 0.061 ms   1,494 relation locks

-- AFTER: 52 monthly partitions
Planning Time: 0.402 ms    Execution Time: 0.058 ms      52 relation locks

The lock dimension #

Every partition examined during planning is locked, and those locks are held until the transaction ends. Three consequences follow. First, max_locks_per_transaction must be large enough: a query touching thousands of partitions inside a transaction that also touches other tables can exhaust the lock table, producing “out of shared memory” errors that look unrelated to partitioning. Second, the locks are visible to DDL: an ALTER TABLE on the parent must wait behind any transaction holding locks on its partitions. Third, taking thousands of locks has a cost of its own, which is part of the planning time measured above.

That makes the partition count an operational parameter, not only a performance one. A design with ten thousand partitions can work for analytical queries planned rarely, and be unusable for an OLTP workload issuing thousands of statements a second — the same schema, two very different outcomes, decided by how often planning happens.

Where a high count is unavoidable, prepared statements and connection reuse become essential rather than optional, because they turn a per-query cost into a per-connection one.

Choosing granularity from the numbers #

The granularity decision has two constraints pulling in opposite directions, and both can be measured. From below, a partition should be large enough that scanning one is a meaningful unit of work: if a typical query reads a whole partition and that takes a few milliseconds, the partition is too small to justify its share of planning cost. From above, the total count should keep planning time an order of magnitude below execution time for the workload’s most frequent statement.

A practical procedure: take the most frequent statement, measure its execution time, and pick the coarsest granularity whose planning time stays under about a tenth of it. For a one-millisecond lookup that means keeping planning near 0.1 ms, which the measurements above put at roughly a hundred partitions. For an analytical workload whose queries run for seconds, a thousand partitions is affordable.

Retention requirements set a floor on granularity too: partitions are the unit of dropping, so keeping data for four years with quarterly deletion means at least sixteen partitions, and monthly deletion means forty-eight. Choosing the coarsest granularity that satisfies retention is usually the right starting point.

Common Pitfalls #

Daily partitions by default. The count grows quickly and rarely buys anything over monthly. Diagnostic signal: hundreds of partitions after a year. Fix: coarser granularity.

Keeping detached data attached. Old partitions still cost planning. Diagnostic signal: partition count growing without bound. Fix: detach and archive.

Partitionwise features left on. They multiply planning work. Diagnostic signal: planning time dropping sharply when they are disabled. Fix: enable them only for workloads that benefit.

Ignoring lock limits. Thousands of partitions per transaction exhaust the lock table. Diagnostic signal: out-of-shared-memory errors. Fix: fewer partitions, or a larger max_locks_per_transaction.

Frequently Asked Questions #

Why is planning slow on a partitioned table? #

The planner examines and locks every partition’s catalog entry, builds candidate paths for the surviving partitions, and prunes the rest. That work scales with the partition count, independently of how much data the query reads.

How many partitions are too many? #

There is no hard limit, but planning cost and lock counts grow linearly. Tens to low hundreds are comfortable for most workloads; thousands are workable only when plans are reused and queries are infrequent.

Do prepared statements help with partitioned tables? #

Yes. A cached generic plan is planned once per session and reused, turning a per-query planning cost into a per-connection one, at the price of execution-time rather than plan-time pruning.

Up: Partition Pruning Analysis