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 - 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 Partition Pruning for Key Columns Local Indexes Global Indexes

Pruning on Non-Partition Key Columns 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 ( 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.

Generating more data 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'; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────── Seq Scan on event_y2025 event Filter: (("timestamp" >= '2025-12-01 00:00:00+00'::timestamp with time zone) 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; QUERY PLAN ────────────────────────────────────────────────────── Append (cost=0.00..1060.07 rows=11 width=37) -> 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.

Global Index 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:

Plot timestamp against 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.

Session ID range by 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?