Partition Pruning at Plan and Execution Time
Read EXPLAIN output to confirm static and runtime pruning eliminate irrelevant partitions from your queries.
Why Partition Pruning Matters
You partitioned a huge table so PostgreSQL can skip partitions that cannot contain matching rows. That skipping is called partition pruning.
Without pruning, a query that touches one month of data could still scan every partition for every month. The whole performance benefit of partitioning depends on the planner (and sometimes the executor) recognising which partitions are relevant.
- Plan-time pruning happens when the planner already knows the filter values.
- Execution-time pruning happens when the values are only known once the query runs.
In this lesson you will learn to confirm both kinds by reading EXPLAIN output.
Our Example Table
Throughout the lesson we use a range-partitioned events table, partitioned by month on created_at. Each child partition holds one month of rows.
This is the classic time-series layout where pruning pays off the most: queries usually target a narrow date range, so most partitions should be skipped entirely.
CREATE TABLE events (
id bigint NOT NULL,
created_at timestamptz NOT NULL,
user_id bigint NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_2024_03 PARTITION OF events
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');All lessons in this course
- Choosing a Partition Key and Strategy
- Partition Pruning at Plan and Execution Time
- Automating Partition Creation and Retention
- Migrating a Huge Table to Partitions Online