Partition pruning behavior in PostgreSQL

Introduction

Partition pruning helps the database skip searching parts of data that are not needed. This makes queries faster and saves resources.

When you have a large table split into smaller parts by date, and you want to query only recent data.

When you want to improve query speed by avoiding scanning irrelevant partitions.

When you use partitioned tables and want the database to automatically ignore partitions that don't match your query conditions.

When you want to reduce disk I/O and CPU usage during data retrieval.

When you want to optimize queries on large datasets divided by categories like region or type.

Syntax

PostgreSQL

SELECT * FROM partitioned_table WHERE partition_key = value;

Partition pruning happens automatically when the query filters on the partition key.

It works best when the filter condition is simple and directly on the partition key.

Examples

This query prunes partitions by filtering on the sale_date column, which is the partition key.

PostgreSQL

SELECT * FROM sales WHERE sale_date = '2024-01-01';

This query prunes partitions covering January 2024 only, skipping others.

PostgreSQL

SELECT * FROM sales WHERE sale_date >= '2024-01-01' AND sale_date < '2024-02-01';

If region is a partition key, this query prunes partitions for the 'North' region only.

PostgreSQL

SELECT * FROM sales WHERE region = 'North';

Sample Program

This example creates a partitioned sales table by month. The query filters on sale_date to trigger partition pruning. The EXPLAIN shows which partitions are scanned.

PostgreSQL

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

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

CREATE TABLE sales_2024_02 PARTITION OF sales
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

INSERT INTO sales (sale_date, region, amount) VALUES
  ('2024-01-10', 'North', 100),
  ('2024-01-20', 'South', 150),
  ('2024-02-10', 'North', 200);

EXPLAIN SELECT * FROM sales WHERE sale_date = '2024-01-15';

Important Notes

Partition pruning only works if the query condition matches the partition key exactly or with a range.

Complex expressions or functions on the partition key may prevent pruning.

Pruning reduces query time by scanning fewer partitions, especially useful for large datasets.

Summary

Partition pruning skips unnecessary partitions to speed up queries.

It works automatically when filtering on the partition key.

Use simple conditions on partition keys for best pruning results.