Partition Pruning in PostgreSQL: What It Is and How It Works

In PostgreSQL, partition pruning is a technique that skips scanning unnecessary partitions in a partitioned table during query execution. It improves performance by only accessing relevant partitions based on query conditions.

How It Works

Partition pruning works like choosing which drawers to open in a filing cabinet instead of searching through every drawer. When you run a query on a partitioned table, PostgreSQL looks at the query's conditions and decides which partitions could contain matching data.

It then skips the partitions that cannot have relevant rows, saving time and resources. This is done during query planning or execution, depending on the PostgreSQL version and query type.

Example

This example shows how partition pruning works with a table partitioned by a date column. The query only scans the partition for the specified date.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

INSERT INTO sales (sale_date, amount) VALUES
  ('2023-06-15', 100),
  ('2024-03-10', 200);

EXPLAIN ANALYZE
SELECT * FROM sales WHERE sale_date = '2023-06-15';

Output

Index Scan using sales_2023_pkey on sales_2023  (cost=0.29..8.31 rows=1 width=16) (actual time=0.012..0.013 rows=1 loops=1)
Planning Time: 0.123 ms
Execution Time: 0.025 ms

When to Use

Use partition pruning when working with large partitioned tables to speed up queries that filter on the partition key. It is especially helpful for time-series data, logs, or any data split by ranges or lists.

This reduces disk I/O and CPU usage by avoiding scanning irrelevant partitions, making queries faster and more efficient.

Key Points

• Partition pruning skips scanning partitions that cannot match query filters.
• It improves query speed and reduces resource use.
• Works best when queries filter on the partition key.
• Supported in PostgreSQL 11 and later with improvements in newer versions.

Key Takeaways

Partition pruning improves query performance by scanning only relevant partitions.

It works by using query conditions to exclude partitions without matching data.

Best used on large partitioned tables with filters on the partition key.

Available from PostgreSQL 11 with ongoing enhancements in newer versions.