How to Achieve Pruning When Querying by Non-Partitioned Columns in PostgreSQL | Haki Benita
One of the most valuable things about partitioned tables is pruning - the database's ability to eliminate entire partitions based on a query predicate. Under conventional wisdom, pruning can only be achieved when querying by the partition key - this makes choosing the right key extremely difficult. However, if your data follows certain patterns, using some clever tricks you can achieve pruning even when filtering by non-partition key columns.
In this article, I demonstrate how to achieve partition pruning when filtering by non-partition key columns.
image by abstrakt design
Table of Contents
Table Partition<br>Partition Pruning for Key Columns
Local Indexes
Global Indexes
Pruning on Non-Partition Key Columns<br>Talking to the Optimizer
The constraint_exclusion Parameter
Introducing Outliers
Handling Outliers
Gaps and Islands
The Backstory
Final Thoughts
Table Partition
Imagine you run a popular website with many users. Your product team wants to gain some insight into how the system is used, so you start logging events. To give events context, you group them into sessions and keep the time, the type, and some data in a database table:
db=# CREATE TABLE event (<br>id BIGINT GENERATED ALWAYS AS IDENTITY,<br>timestamp TIMESTAMPTZ NOT NULL,<br>session_id BIGINT NOT NULL,<br>type TEXT NOT NULL,<br>data JSONB<br>) PARTITION BY RANGE (timestamp);
CREATE TABLE;
You have many users so you expect many events. Most queries use only a subset of the data, usually a specific date range, so you create a partition for each year based on the timestamp:
db=# CREATE TABLE event_y2025 PARTITION OF event<br>FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC');
CREATE TABLE
db=# CREATE TABLE event_y2026 PARTITION OF event<br>FOR VALUES FROM ('2026-01-01 UTC') TO ('2027-01-01 UTC');
CREATE TABLE
You now have two partitions - one for events from 2025 and another for 2026. A session can look like this:
INSERT INTO event (session_id, timestamp, type, data) VALUES<br>(1, '2025-12-28 15:00:00 UTC', 'view', '{"page": "/login"}'),<br>(1, '2025-12-28 15:00:06 UTC', 'click', '{"selector": "#login"}'),<br>(1, '2025-12-28 15:00:07 UTC', 'login_failed', '{"attempt": 1}'),<br>(1, '2025-12-28 15:00:10 UTC', 'click', '{"selector": "#forgot-password"}'),<br>(1, '2025-12-28 15:00:17 UTC', 'view', '{"page": "/reset-password"}'),<br>(1, '2025-12-28 15:00:23 UTC', 'click', '{"selector": "#reset-password"}');
In this session the user tried to log into the system, failed and asked to reset their password.
Generating more data
To make the examples more realistic we need more data, so let's create some:
WITH<br>sessions AS (<br>SELECT<br>n AS session_id,<br>'2025-12-28 23:59:56 UTC'::timestamptz + interval '1 minute' * n as started_at<br>FROM<br>generate_series(2, 10_000) AS t(n)<br>INSERT INTO event (session_id, timestamp, type, data)<br>SELECT<br>session_id,<br>started_at + interval '1 second' * n,<br>(array['view', 'click', 'login_failed', 'logged_in'])[ceil(random() * 3)] as type,<br>'{}'::jsonb as data<br>FROM<br>sessions,<br>generate_series(1, 5) as n<br>ORDER BY 1, 2;
INSERT 0 49995
You now have ~50K events in the table across both partitions.
Partition Pruning for Key Columns
The partition key of the table is timestamp, so queries that filter by timestamp can benefit from partition pruning. For example, query events in December 2025:
db=# EXPLAIN SELECT * FROM event<br>WHERE timestamp >= '2025-12-01 UTC' AND timestamp '2026-01-01 UTC';<br>QUERY PLAN<br>────────────────────────────────────────────────────────────────────────────────────────────────<br>Seq Scan on event_y2025 event<br>Filter: (("timestamp" >= '2025-12-01 00:00:00+00'::timestamp with time zone)<br>AND ("timestamp" '2026-01-01 00:00:00+00'::timestamp with time zone))
Notice that the database was smart enough to figure out it only needs to scan the partition for 2025. The partition for 2026 was not even accessed. This is partition pruning.
Another common query is to find all events for a given session:
db=# EXPLAIN SELECT * FROM event WHERE session_id = 1;<br>QUERY PLAN<br>──────────────────────────────────────────────────────<br>Append (cost=0.00..1060.07 rows=11 width=37)<br>-> Seq Scan on event_y2025 event_1<br>Filter: (session_id = 1)<br>-> Seq Scan on event_y2026 event_2<br>Filter: (session_id = 1)
This time, the database accessed all partitions - partition pruning was not used. In this query, the database has no way of eliminating partitions, so it had no other choice but to scan all partitions to look for matching events.
This is where partitions get a bit hairy. On one hand, you want to achieve pruning, but then you have to make painful compromises in other, potentially very common queries.
Local Indexes
Getting events for a specific session is fairly common, so it needs to be fast. To make things fast in databases you should just create an index, right?
db=# CREATE INDEX event_session_ix ON event(session_id);<br>CREATE INDEX
This creates an index on the session ID. With...