Partition Pruning

Prove with EXPLAIN that partitions drop from plans - otherwise partitioning only adds DDL overhead without scan reduction.

Recipe

Quick-reference recipe card - copy-paste ready.

-- Prune-friendly: literal or param bound on partition key
EXPLAIN (COSTS OFF)
SELECT * FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-03-01'
  AND occurred_at <  TIMESTAMPTZ '2026-04-01';
 
-- Enable partition pruning (default on in PG 18)
SET enable_partition_pruning = on;
SET enable_partitionwise_join = off;  -- test baseline first

When to reach for this:

• After creating partitioned tables - verify before production traffic.
• When query latency matches full table scan despite partitioning.
• When ORM-generated SQL wraps partition key in functions.

Working Example

CREATE TABLE metrics (
  device_id   text NOT NULL,
  recorded_at timestamptz NOT NULL,
  cpu_pct     numeric(5, 2) NOT NULL,
  PRIMARY KEY (device_id, recorded_at)
) PARTITION BY RANGE (recorded_at);
 
CREATE TABLE metrics_2026_01 PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE metrics_2026_02 PARTITION OF metrics
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE metrics_2026_03 PARTITION OF metrics
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
 
INSERT INTO metrics SELECT 'd1', '2026-01-10', 12.5;
INSERT INTO metrics SELECT 'd1', '2026-02-10', 22.0;
INSERT INTO metrics SELECT 'd1', '2026-03-10', 33.0;
 
ANALYZE metrics;
 
EXPLAIN (COSTS OFF, VERBOSE)
SELECT AVG(cpu_pct) FROM metrics
WHERE recorded_at >= '2026-03-01' AND recorded_at < '2026-04-01';

Expected plan characteristics:

• Scan node references metrics_2026_03 only.
• EXPLAIN output includes Partitions pruned: 2 (PostgreSQL 14+).
• No Append children for pruned months.

What this demonstrates:

• Half-open range predicate aligned with partition bounds enables pruning.
• ANALYZE on parent updates stats for all partitions.
• VERBOSE shows which child relations are scanned.

Deep Dive

How It Works

• Static pruning at plan time when predicates are constants (WHERE recorded_at = '2026-03-15').
• Runtime pruning when predicates use parameters ($1) - still prunes per execution in prepared statements.
• Pruning requires comparisons on the partition key expression, not wrapped forms like date_trunc('month', recorded_at).
• constraint_exclusion setting is legacy; declarative pruning uses enable_partition_pruning.

Pruning Checklist

SQL Notes

-- Compare pruned vs unpruned
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT COUNT(*) FROM metrics WHERE device_id = 'd1';
 
EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT COUNT(*) FROM metrics
WHERE recorded_at >= '2026-03-01' AND recorded_at < '2026-04-01';

Gotchas

• Function on partition key - WHERE occurred_at::date = CURRENT_DATE may not prune. Fix: range on raw timestamptz.
• Default partition absorbs everything - pruning still scans default when bounds unknown. Fix: explicit partitions, monitor default size.
• Stable vs immutable functions - occurred_at AT TIME ZONE 'UTC' can block pruning. Fix: store UTC, compare directly.
• Join without partition key filter - fact table partitioned but dimension join hides key. Fix: push logged_at filter into fact side.
• Thousands of partitions - planning time grows even with pruning. Fix: fewer, larger partitions.

NzA2MjAyfDQ4MDFvaWdjfG9pLnNlZGl1Z2Vkb2M=

Alternatives

FAQs

Related

• Declarative Partitioning DDL - partition setup
• Partitioning Basics - EXPLAIN example
• EXPLAIN Basics - reading plan trees
• Partitioning Best Practices - partition count guidance

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.