Verify Partition Pruning Is Effective

Confirm that queries filter on the partition key and that the planner prunes unrelated partitions, using EXPLAIN and runtime statistics.

~30 minpostgresqlperformancedatabases

Task

Check that a partitioned table actually prunes partitions for the queries that matter.

When to use this

A partitioned table was created for performance, but queries still scan every partition because the filter does not match the partition key.

Prerequisites

A partitioned table
Access to run EXPLAIN ANALYZE

Steps

1. Run EXPLAIN ANALYZE on the production query and check which partitions appear.

Expected output: Pruned partitions are absent from the plan.

2. Confirm the WHERE clause filters on the partition key with a constant or a stable expression.

Expected output: The planner has enough information to prune.

3. Check that parameterized queries do not defeat pruning by using a plan-time-unknown value.

Expected output: Pruning still happens at execution.

4. Align indexes with the filtered columns inside each partition.

psql -c "EXPLAIN ANALYZE SELECT * FROM metrics WHERE ts >= now() - interval '1 day';"

Expected output: Each surviving partition uses an index, not a scan.

Troubleshooting

Problem

Pruning fails at runtime

Solution

Avoid wrapping the partition key in a function or casting that the planner cannot fold.

Problem

Only some partitions pruned

Solution

Ensure the query range is contiguous and the partition bounds are aligned with the query predicates.

Limitations

Pruning only helps when queries constrain the partition key; broad analytical queries still touch many partitions.
Over-partitioning increases planning time and catalog overhead without improving pruning for wide queries.

Context

Check that a partitioned table actually prunes partitions for the queries that matter. A partitioned table was created for performance, but queries still scan every partition because the filter does not match the partition key.

Steps

Run EXPLAIN ANALYZE on the production query and check which partitions appear. Expected: Pruned partitions are absent from the plan.
Confirm the WHERE clause filters on the partition key with a constant or a stable expression. Expected: The planner has enough information to prune.
Check that parameterized queries do not defeat pruning by using a plan-time-unknown value. Expected: Pruning still happens at execution.
Align indexes with the filtered columns inside each partition. Expected: Each surviving partition uses an index, not a scan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM metrics
WHERE ts >= now() - interval '1 day'
  AND name = 'cpu';
-- the plan should mention only the current partitions

Verify

Pruned partitions are absent from the plan.
The planner has enough information to prune.
Pruning still happens at execution.
Each surviving partition uses an index, not a scan.

Limits

Pruning only helps when queries constrain the partition key; broad analytical queries still touch many partitions.
Over-partitioning increases planning time and catalog overhead without improving pruning for wide queries.