Partition Pruning

Pruning is the whole performance case for partitioning. A query that says something about occurred_at touches only the partitions whose bounds could hold a matching row; the other eleven are never opened, never read and never counted. Nothing else about splitting a table makes a query faster, and a partitioned table whose queries do not prune is strictly worse than the single table it replaced.

It is also the part people assume rather than check. Pruning is driven only by the partition bounds, not by any index, and it happens in two quite different places: in the planner when the value is a constant, and in the executor when it is not. Each leaves a different mark in EXPLAIN, and one of the two marks is a single line you have to know to look for.

Plan-Time Pruning

With a literal, or an expression the planner can fold to a constant before execution, the planner compares the predicate against each partition's bounds and discards the ones that cannot contribute. Those partitions are gone from the plan, not marked and not skipped at run time but simply absent, so the plan itself is the proof. The setting that governs this is enable_partition_pruning, on by default; the only reason to name it is that turning it off is how you produce the unpruned plan to compare against.

EXPLAIN SELECT event_type, count(*) FROM delivery_events
 WHERE occurred_at >= '2026-07-01' AND occurred_at < '2026-09-01'
 GROUP BY event_type;

 HashAggregate
   ->  Append
         ->  Seq Scan on delivery_events_2026_07 delivery_events_1
         ->  Seq Scan on delivery_events_2026_08 delivery_events_2

Two scan nodes under the Append, for a table with twelve partitions. That count is the diagnostic, and it is the one to build into a review habit: read the number of scan nodes and compare it to the number of months the predicate covers. The predicate here is a half-open range written against the raw column, greater-or-equal on the lower bound and strictly-less on the upper, which is exactly how the partition bounds themselves are declared.

Execution-Time Pruning

Application queries rarely carry literals. They arrive as prepared statements with $1, or the partition key comes from a subquery, or it comes from the outer side of a nested loop. The planner cannot compare an unknown value against the bounds, so since 11 Postgres prunes during execution instead, at two distinct moments. The first is plan initialization, when parameters that are already known get compared against the bounds; partitions removed at that point do not appear in EXPLAIN or EXPLAIN ANALYZE at all, and the only trace they leave is a count.

PREPARE ev(timestamptz, timestamptz) AS
  SELECT count(*) FROM delivery_events
   WHERE occurred_at >= $1 AND occurred_at < $2;

EXPLAIN (ANALYZE) EXECUTE ev('2026-08-01', '2026-09-01');

 Aggregate (actual time=812.4..812.4 rows=1 loops=1)
   ->  Append (actual time=0.031..655.2 rows=118942317 loops=1)
         Subplans Removed: 11                -- ← the proof
         ->  Seq Scan on delivery_events_2026_08 …

The second moment is during execution proper, when the value changes as the query runs — a parameterized nested loop re-prunes on every new outer row. There is no summary line for that case. You read the loops count on each partition's scan node instead, and partitions that were pruned on every iteration show up as (never executed). One caveat worth carrying: partitions removed during initialization are still locked at the start of execution, so pruning saves I/O and CPU rather than lock acquisition, which is another reason a partition count in the thousands is not free even when queries prune well.

What Defeats Pruning

There are four predicates that turn a pruned query back into a scan of everything, and the first is by far the most common. Wrapping the key in a function hides it: date_trunc('month', occurred_at) is not occurred_at, and the planner has no way to invert it into a range of bounds. The fix is to state the range the function was standing in for. The second is a type that has to be converted before it can be compared — a timestamp value against a timestamptz key, where the conversion depends on the session's TimeZone and therefore is not something the planner can fold into a constant. Chapter 2 argued for timestamptz on correctness grounds; this is the operational bill for having ignored it.

-- scans every partition: the key is inside a function call
SELECT count(*) FROM delivery_events
 WHERE date_trunc('month', occurred_at) = '2026-08-01';

-- prunes to one: the same window stated against the raw column
SELECT count(*) FROM delivery_events
 WHERE occurred_at >= '2026-08-01' AND occurred_at < '2026-09-01';

The third is an OR that reaches outside the key: WHERE occurred_at >= '2026-08-01' OR event_type = 'failed' cannot exclude any partition, because a failed event in 2024 satisfies the query. The fourth is the plain case of a query that never mentions the partition key at all: a report grouped by event_type over all history, which was always going to read the whole table and now reads it through twelve scan nodes instead of one. All four live in the query text, and all four are fixable there.

Ordering and Merge Append

Range partitions are stored in key order, and the planner knows it. A query for the newest events sorted by occurred_at can therefore be answered by reading the partitions in reverse bound order, taking an ordered index scan from each, and emitting rows that are already in the requested order — no sort node, and no need to have read anything from the older partitions. With a LIMIT 20 the executor stops as soon as the newest partition has produced twenty rows, and every other partition's scan node reports (never executed).

A Merge Append appears when the requested ordering does not line up with the partition ordering, sorting by order_id for instance, where matching rows can come from any month and the node has to interleave streams from all of them. The distinction matters because the two look similar in a plan and behave very differently: one opens a single partition and stops, the other opens all twelve. This section is also the honest exception to this chapter's framing. A "latest events" query genuinely gets faster from partitioning, rather than merely becoming easier to maintain.

Partitionwise Join and Aggregate

When two tables are partitioned the same way on the same key, a join between them can be done partition by partition instead of over the whole set, and an aggregate can be computed per partition and combined. Postgres will do both, and does neither by default: enable_partitionwise_join and enable_partitionwise_aggregate are off. The reason is in the documentation and it is a real one. Turning them on multiplies the number of plan nodes whose memory is bounded by work_mem, linearly with the partition count, so a query that was allowed 4 MB per hash node can now ask for that much per partition per node, and planning itself gets significantly more expensive in both CPU and memory.

Partitionwise join also has conditions: the join conditions must include all the partition keys, the keys must be of the same type, and the two tables must have one-to-one matching sets of partitions. Version 18 widened the cases where it applies and cut its memory use, which makes it more attractive than it was without changing the shape of the decision. Turn it on for the session or the role that runs the analytics workload, measure the plan and the memory, and leave the checkout path alone: Cartwheel's dashboard on pg-replica-a runs a handful of long queries, and the API runs 200,000 short ones an hour.

Planning Cost Grows With Partitions

The planner has to consider each partition in order to prune it, so partition count is never entirely free. The documentation's position is specific: hierarchies of up to a few thousand partitions are handled well provided typical queries prune all but a small number, and both planning time and memory rise with how many partitions survive pruning. Version 18 improved the efficiency of planning against many partitions, which moves the ceiling rather than removing it. There is a second cost that appears on no plan: each session that touches a partition loads that partition's metadata into its own local memory, so a hundred backends touching a thousand partitions each carry a hundred thousand copies of it between them.

This is the concrete reason Cartwheel's partitions are monthly. Twelve relations cost nothing measurable on a query that prunes to one, while the same twelve-month window at daily granularity is 365 relations: still inside "a few thousand", still working, and now paying planning time on every one of the API's short queries to support a retention rule that is expressed in months anyway. The granularity is worth re-deriving whenever the retention window changes.