Manage Partition Pruning

Your PostgreSQL database is growing, and so is your query latency. You've partitioned a massive table into monthly chunks, expecting every query to fly — but they're just as slow as before. The culprit is almost always one of two things: the planner is scanning every partition because pruning isn't happening, or your indexes aren't optimized for the partitioned layout. In this lesson, you'll learn how to manage partition pruning and indexes so your partitioned tables actually deliver the performance you signed up for.

The problem this lesson solves

Partitioning is a powerful technique for managing large tables, but it's not a magic performance switch. When you split a table into partitions, PostgreSQL's planner can skip entire partitions that don't match the query's filter conditions — that's partition pruning. If pruning doesn't happen, the planner scans every partition, and you're no better off than with a single big table.

Even when pruning works, indexes can be a second bottleneck. Indexes on partitioned tables aren't global; each partition gets its own index. If you create indexes incorrectly — or not at all — queries that touch many partitions can degrade into sequential scans. You might see slow queries, high CPU, or unexplained EXPLAIN plans that show all partitions being scanned.

Let's fix that. By the end of this lesson, you'll be able to diagnose pruning failures, design effective indexes for partitioned tables, and keep your queries fast as data grows.

Core concept / mental model

Think of your partitioned table as a filing cabinet. Each partition is a drawer labeled by month or region. A query with a filter like WHERE created_at >= '2025-01-01' is like asking for files from January onward. If you know which drawers hold those months, you only open the ones you need — that's partition pruning. If you don't know, you open every drawer and rummage through — that's a full scan.

PostgreSQL's planner uses constraint exclusion and partition pruning to skip irrelevant partitions. Constraint exclusion evaluates the partition's declared bounds (e.g., FOR VALUES FROM ... TO ...) against the query's WHERE clause. If a partition's bounds can't match the filter, it's skipped. Pruning works at plan time for static values and at execution time for parameterized or prepared statements.

Indexes on partitioned tables work differently from indexes on plain tables. There's no single index spanning all partitions — PostgreSQL creates a separate index on each partition (or you create them manually). When you define an index on the partitioned table, PostgreSQL automatically creates matching indexes on each existing partition and on any future partitions. But if you create indexes directly on partitions, they must be identical in structure to the parent's indexes, or they won't be used consistently.

The key mental model: partition pruning decides which partitions to touch; indexes decide how fast each touched partition is scanned. You need both working together.

How it works step by step

Let's walk through the mechanics of partition pruning and index usage.

1. Declare partitions with bounds. You create a partitioned table with a PARTITION BY RANGE clause, then create partitions with FOR VALUES FROM ... TO ....
2. Write a query with a filter on the partition key. For pruning to happen, the WHERE clause must contain a constant or a parameter on the partition key. For example, WHERE order_date = '2025-03-01'.
3. The planner checks each partition's bounds. It sees which partitions could possibly match the filter and builds a plan only for those. Partitions with non-matching bounds are omitted.
4. At execution time, pruning may go further. If you use prepared statements or bind parameters, the planner may not know the value at plan time. PostgreSQL uses execution-time pruning to skip partitions when the actual parameter value arrives.
5. Indexes on partitions are used per-partition. After pruning decides which partitions to scan, the planner uses each partition's indexes to find rows quickly. If no index matches, it does a sequential scan on that partition.
6. If the filter uses a function or a non-immutable expression on the partition key, pruning fails. For example, WHERE date_trunc('month', order_date) = '2025-03-01' prevents pruning because the expression isn't a direct comparison of the partition key.

Hands-on walkthrough

Let's get our hands dirty. First, create a partitioned table and some partitions.

-- Create a partitioned table
CREATE TABLE orders (
    id bigint NOT NULL,
    order_date date NOT NULL,
    customer_id bigint,
    amount numeric
) PARTITION BY RANGE (order_date);

-- Create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE orders_2025_02 PARTITION OF orders
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE orders_2025_03 PARTITION OF orders
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Insert some sample data
INSERT INTO orders (id, order_date, customer_id, amount) VALUES
    (1, '2025-01-15', 101, 250.00),
    (2, '2025-02-10', 102, 320.00),
    (3, '2025-03-05', 103, 150.00);

Now, let's see partition pruning in action using EXPLAIN.

-- This should prune to only the January partition
EXPLAIN SELECT * FROM orders WHERE order_date = '2025-01-15';

-- Output:
-- Seq Scan on orders_2025_01 (cost=0.00..40.00 rows=1 width=...)
--   Filter: (order_date = '2025-01-15'::date)

Notice how the plan only mentions orders_2025_01 — the other two partitions are pruned. Now, try a query that prevents pruning:

-- This uses a function on the partition key, so pruning fails
EXPLAIN SELECT * FROM orders WHERE date_trunc('month', order_date) = '2025-02-01';

-- Output:
-- Append
--   Subplans:
--     Seq Scan on orders_2025_01 ...
--     Seq Scan on orders_2025_02 ...
--     Seq Scan on orders_2025_03 ...

All three partitions are scanned. That's a classic pruning pitfall.

Now, let's add indexes. Because orders is partitioned, an index on the partitioned table automatically creates per-partition indexes.

-- Create an index on the partitioned table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Check the indexes on a partition
\d orders_2025_01
-- Output includes:
-- "orders_2025_01_customer_id_idx" btree (customer_id)

For a query that filters on both the partition key and an indexed column, pruning + index scan work together:

EXPLAIN SELECT * FROM orders
WHERE order_date >= '2025-02-01' AND customer_id = 102;

-- Output (simplified):
-- Index Scan using orders_2025_02_customer_id_idx on orders_2025_02 ...

Only the February partition is scanned, and it uses the index on customer_id.

Compare options / when to choose what

Partitioning strategy and index design go hand in hand. Here's a comparison to help you decide:

Pro tip: For range-partitioned tables, an index on the partition key alone is often wasteful because the planner usually prunes to a single partition; the index adds overhead for writes. Instead, put indexes on columns that appear in WHERE clauses alongside the partition key.

When you create an index on the partitioned table, PostgreSQL 11+ handles it automatically. For older versions, you'd have to create indexes manually on each partition. If you're on 10 or earlier, upgrade — it's a huge productivity boost.

Another choice: global indexes aren't supported in PostgreSQL (as of 16). Each partition has its own index. If you need a unique constraint across partitions, you must include the partition key in the constraint — a common gotcha.

Troubleshooting & edge cases

Here are common issues and their fixes.

• Pruning doesn't happen at all. Check if your WHERE clause uses OR conditions that span partitions — pruning may be conservative and scan more. Also, functions like now() or current_date are stable, not immutable, so the planner can't always fold them. Use a literal or a parameter.

• Prepared statements cause sequential scans. By default, PostgreSQL plans with generic plans after five executions, which may prevent execution-time pruning. You can force custom plans with SET plan_cache_mode = force_custom_plan; for a specific session.

• Indexes not used on partitions. If you created indexes directly on partitions, make sure they match the parent table's index definition. If you define an index on the parent after partitions exist, it's applied to all partitions automatically — but if you add a new partition later, the index is created automatically too.

• Unique constraints across partitions fail. Without the partition key in the index, you'll get ERROR: unique constraint on partitioned table must include all partitioning columns. Fix by adding the partition key to the unique index.

• Queries slow when pruning works but data grows. Re-evaluate your partition ranges. If partitions are too large, consider sub-partitioning or increasing frequency (e.g., daily instead of monthly).

What you learned & what's next

You now understand how to manage partition pruning and indexes in PostgreSQL. You've learned:

• Partition pruning skips irrelevant partitions based on the query's filter on the partition key.
• Indexes on partitioned tables are per-partition; they're automatically created when you create an index on the parent.
• Pruning failure sources: functions on the partition key, OR conditions, and generic plans for prepared statements.
• Index design must complement pruning — indexes on common filter columns, not just the partition key.
• Unique constraints across partitions must include the partition key.

Next, you'll dive into advanced indexing strategies — partial indexes, covering indexes, and expression indexes — to squeeze even more performance from your partitioned tables. You'll learn how to combine these techniques with pruning for complex reporting queries.

Now, try this mini-exercise: Create a partitioned table by day for the last 7 days, insert sample data, and run EXPLAIN queries with different WHERE clauses — one that prunes, one that doesn't. Add an index on a non-partition column and observe the plan changes.

With pruning and indexes mastered, your partitioned tables will handle millions of rows without breaking a sweat.