How to Achieve Pruning When Querying by Non-Partitioned Columns in PostgreSQL
How to Achieve Pruning When Querying by Non-Partitioned Columns in PostgreSQL
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, which 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.
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 (
id BIGINT GENERATED ALWAYS AS IDENTITY,
timestamp TIMESTAMPTZ NOT NULL,
session_id BIGINT NOT NULL,
type TEXT NOT NULL,
data JSONB
) 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
FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC');
CREATE TABLE
db=# CREATE TABLE event_y2026 PARTITION OF event
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
(1, '2025-12-28 15:00:00 UTC', 'view', '{"page": "/login"}'),
(1, '2025-12-28 15:00:06 UTC', 'click', '{"selector": "#login"}'),
(1, '2025-12-28 15:00:07 UTC', 'login_failed', '{"attempt": 1}'),
(1, '2025-12-28 15:00:10 UTC', 'click', '{"selector": "#forgot-password"}'),
(1, '2025-12-28 15:00:17 UTC', 'view', '{"page": "/reset-password"}'),
(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. To make the examples more realistic we need more data, so let's create some:
WITH sessions AS (
SELECT n AS session_id,
'2025-12-28 23:59:56 UTC'::timestamptz + interval '1 minute' * n as started_at
FROM generate_series(2, 10_000) AS t(n)
)
INSERT INTO event (session_id, timestamp, type, data)
SELECT session_id,
started_at + interval '1 second' * n,
(array['view', 'click', 'login_failed', 'logged_in'])[ceil(random() * 3)] as type,
'{}'::jsonb as data
FROM sessions, generate_series(1, 5) as n
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
WHERE timestamp >= '2025-12-01 UTC'
AND timestamp < '2026-01-01 UTC';
The database will only scan the 2025 partition. This is partition pruning in action.
Partition Pruning for Non-Key Columns
Now, what if you want to query events for a specific session? For example, get all events for session ID 1:
db=# EXPLAIN SELECT * FROM event WHERE session_id = 1;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────
Append (cost=0.00..0.00 rows=0 width=0)
-> Seq Scan on event_y2025 event_1
Filter: (session_id = 1)
-> Seq Scan on event_y2026 event_2
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);
CREATE INDEX
This creates an index on the session ID. With the index in place, get events for session 1:
db=# EXPLAIN SELECT * FROM event WHERE session_id = 1;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────
Append (cost=0.29..16.82 rows=11 width=37)
-> Index Scan using event_y2025_session_id_idx on event_y2025 event_1
Index Cond: (session_id = 1)
-> Index Scan using event_y2026_session_id_idx on event_y2026 event_2
Index Cond: (session_id = 1)
The database once again had to visit all partitions. The only difference is that this time, it used the index on each partition. Using an index is faster than scanning the entire partition, but the database is still forced to scan through all of the partitions. Right now there are only two partitions, but if the table had a hundred partitions, this query would be like querying a hundred tables!
This type of index is called a local index because it creates a separate index on every partition:
db=# \di event_*
List of indexes
Schema │ Name │ Type │ Owner │ Table
────────┼───────────────────────────────┼───────────────────┼───────┼─────────────
public │ event_session_ix │ partitioned index │ haki │ event
public │ event_y2025_session_id_idx │ index │ haki │ event_y2025
public │ event_y2026_session_id_idx │ index │ haki │ event_y2026
Local indexes are useful when you frequently filter by columns not part of the partition key.
Global Indexes
Another approach to indexing partitioned tables is to create a single index that spans multiple partitions. This is called a global index. Unfortunately, as of version 19, PostgreSQL does not support global indexes on partitioned tables. You can keep an eye on the pgsql-hackers mailing list for updates - there are discussions going all the way back to 2009 on this subject.
Another pain point of not having global indexes is that it makes it difficult to enforce uniqueness on anything other than the partition key. This is outside the scope of this article.
Pruning on Non-Partition Key Columns
The events table is partitioned by timestamp, so queries by timestamp can benefit from partition pruning. However, there are still many situations where you want to query by session_id. Events from a single session can potentially span multiple partitions and the database currently has no way of eliminating irrelevant ones. Local indexes alleviate some of the pain, but the database still has to visit all partitions, which may not scale very well.
At this point you reached the limit of what the database can just do out of the box, and you need to tap into your domain expertise and knowledge of the data - how it's being used and how it's being stored:
- The events table is append only: no updates to the table, events are immutable.
- Session IDs are generated sequentially: session IDs increment over time.
- Sessions are short lived: a normal session is usually no longer than a couple of minutes or hours.
This pattern can be useful! To visualize the pattern, plot the timestamp against the session ID:
Session ID is strongly correlated with the timestamp. This means it should be possible to identify a distinct range of session IDs for each partition. Get the first and last session ID in each partition:
db=# SELECT tableoid::regclass, MIN(session_id), MAX(session_id)
FROM event
GROUP BY 1
ORDER BY 1;
tableoid │ min │ max
──────────────┼──────┼───────
event_y2025 │ 1 │ 4320
event_y2026 │ 4320 │ 10000
Thanks to the strong correlation between the session ID and the timestamp, you can identify a clear and distinct range of session IDs for each partition. Just by looking at the results, it's clear that sessions with IDs between 1 and 4319 only exist in the 2025 partition, and sessions with IDs between 4321 and 10000 only exist in the 2026 partition.
Knowing this pattern, the database can potentially eliminate entire partitions when querying by the session ID, but how can you communicate this information to the database?
Talking to the Optimizer
The database optimizer is truly a marvelous piece of engineering. It takes a query and figures out on its own how to execute it without looking at the actual data. The only thing the database can use are statistics it maintains on tables and columns. Statistics are used to produce row estimates. Row estimates help the optimizer decide which tables to scan first, which indexes to use and what filters or joins to apply and in what order.
However, as the name suggests, statistics are just statistics - there is no guarantee they are correct, so the optimizer can consult them, but never blindly rely on them. The only information the optimizer can rely on is information that is guaranteed to always be correct. In databases, to guarantee that something is always correct, you use a constraint.
Check constraints are used to enforce custom validation rules. Using a check constraint, you provide an expression and the database guarantees that the expression is true for all the rows in the table. But here's the secret, the thing nobody tells you about - check constraints can be used to communicate information about your data to the optimizer.
For example, if you have a check constraint to make sure event ID is always greater than zero, if you query for events with ID less than zero, the database doesn't really have to scan the table to figure out no rows can match this condition, right?
To check if you can influence the optimizer, add a simple check constraint on each partition to enforce a specific range of session IDs:
db=# ALTER TABLE event_y2025
ADD CONSTRAINT event_y2025_session_id_range
CHECK (session_id between 1 and 4320);
ALTER TABLE
db=# ALTER TABLE event_y2026
ADD CONSTRAINT event_y2026_session_id_range
CHECK (session_id between 4320 and 10000);
ALTER TABLE
The first check constraint sets the range for the 2025 partition to session IDs between 1 and 4320. The second check constraint sets the range for the 2026 partition to session IDs between 4320 and 10000.
Now to see if the database can actually use the check constraint to eliminate entire partitions, query events for session with ID 1000:
db=# EXPLAIN SELECT * FROM event WHERE session_id = 1000;
QUERY PLAN
──────────────────────────────────────────────────────────────────
Index Scan using event_y2025_session_id_idx on event_y2025 event
Index Cond: (session_id = 1000)
Amazing! With the check constraint in place, the database was able to eliminate the partition for 2026. According to the constraint, this partition can only contain session IDs between 4320 and 10000 and the query looks for session ID 1000. The database ended up scanning only one partition.
Check another session ID:
db=# EXPLAIN SELECT * FROM event WHERE session_id = 6000;
QUERY PLAN
──────────────────────────────────────────────────────────────────
Index Scan using event_y2026_session_id_idx on event_y2026 event
Index Cond: (session_id = 6000)
Once again, the database scanned only a single partition. This time, according to the check constraint, events for session ID 6000 can only be in the partition for year 2026, so the database only scanned that partition.
What if you query for a session that may have events in more than one partition?
db=# EXPLAIN SELECT * FROM event WHERE session_id = 4320;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────
Append (cost=0.29..16.80 rows=10 width=37)
-> Index Scan using event_y2025_session_id_idx on event_y2025 event_1
Index Cond: (session_id = 4320)
-> Index Scan using event_y2026_session_id_idx on event_y2026 event_2
Index Cond: (session_id = 4320)
The database scanned two partitions and that's OK! Based on the check constraints, both of these partitions may have events for session ID 4320.
This demonstrates that using carefully crafted check constraints you can communicate information about your data to the database, and the database can use that information to eliminate entire partitions and effectively achieve partition pruning.
The constraint_exclusion Parameter
This pruning mechanism is controlled by the parameter constraint_exclusion:
Controls the query planner's use of table constraints to optimize queries. [...] When this parameter allows it for a particular table, the planner compares query conditions with the table's CHECK constraints, and omits scanning tables for which the conditions contradict the constraints.
Constraint exclusion is on by default for partitioned tables. The database will use any check constraint you have on a partitioned table to achieve pruning, and you don't even have to change any parameters for it.
I wrote about using constraint_exclusion on a non-partitioned table to eliminate entire table scans in queries that contain impossible predicates.
Introducing Outliers
To achieve pruning for queries by session ID you relied on the predictable nature of the data and the strong correlation between the session ID and the timestamp. In this case, the table is append only and sessions are short lived. This means there's not a lot of overlapping session IDs between partitions, and it's possible to identify distinct ranges for each partition.
Unfortunately, reality is usually not that tidy! What if some sessions become days or weeks long? This can introduce outliers into the ranges.
First, drop the check constraints so you can introduce outliers:
db=# ALTER TABLE event_y2025 DROP CONSTRAINT event_y2025_session_id_range;
ALTER TABLE
db=# ALTER TABLE event_y2026 DROP CONSTRAINT event_y2026_session_id_range;
ALTER TABLE
Now, consider this session:
db=# SELECT * FROM event WHERE session_id = 1 ORDER BY timestamp;
id │ timestamp │ session_id │ type │ data
────┼─────────────────────────────┼────────────┼───────────────┼──────────────────────────────────
1 │ 2025-12-29 15:00:00+00 │ 1 │ view │ {"page": "/login"}
2 │ 2025-12-29 15:00:06+00 │ 1 │ click │ {"selector": "#login"}
3 │ 2025-12-29 15:00:07+00 │ 1 │ login_failed │ {"attempt": 1}
4 │ 2025-12-29 15:00:10+00 │ 1 │ click │ {"selector": "#forgot-password"}
5 │ 2025-12-29 15:00:17+00 │ 1 │ view │ {"page": "/reset-password"}
6 │ 2025-12-29 15:00:23+00 │ 1 │ click │ {"selector": "#reset-password"}
Comments
No comments yet. Start the discussion.