Partition pruning and constraint exclusion: how to verify the planner is doing its job

Capsule description

Partitioning a table only pays off if the planner actually discards the partitions that don't apply. If your events table is partitioned by month but every query scans all 24 partitions, the surgery was pointless. Worse: you added planning overhead with no benefit whatsoever.

Partition pruning is the mechanism that makes partitioning worthwhile. It's PostgreSQL's planner's ability to look at your query's WHERE and decide, before executing, which partitions to touch and which to ignore. When it works, the plan shows only the relevant partitions and the query is 10×-100× faster. When it fails silently, every partition shows up and the query takes as long as — or longer than — it did unpartitioned.

This capsule trains your eye to read EXPLAIN ANALYZE on partitioned tables, recognize whether pruning is happening or not, identify the typical cases where it fails (queries with OR, inadequate casts, untyped parameters, non-immutable functions), and apply the techniques to force it when the planner hesitates. Without this capsule, partitioning is a black box; with it, it's a verifiable tool.

By the end you'll be able to audit any query on a partitioned table, distinguish "the plan lists the partitions in the requested range" from "the plan lists all of them", and diagnose why when the second case happens.

Mental model: the planner as the doorman of the partition set

Think of a partitioned table as a building with N floors (each floor is a partition). When a query arrives, the planner is the doorman who checks the WHERE: "which floors have what you're looking for?". If the question is clear ("events from April 2026"), the doorman sends you to floor events_2026_04 and nowhere else. If the question is vague ("events where event_type starts with a vowel"), the doorman can't decide and opens every floor for you to look through.

┌──────────────────────────────────────────────────────────────┐
│   Query 1: SELECT * FROM events                              │
│            WHERE created_at >= '2026-04-01'                  │
│              AND created_at <  '2026-05-01'                  │
│                                                              │
│                            │                                 │
│                            ▼                                 │
│              ┌─────────────────────────┐                     │
│              │   Planner (doorman)     │                     │
│              │   Looks at WHERE.       │                     │
│              │   Identifies:           │                     │
│              │   "range covers April"  │                     │
│              └────────────┬────────────┘                     │
│                           │                                  │
│              ✅ Opens only events_2026_04                    │
│              ❌ events_2026_03, _05, _06... stay closed      │
└──────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────┐
│   Query 2: SELECT * FROM events                              │
│            WHERE event_type = 'view'                         │
│                                                              │
│                            │                                 │
│                            ▼                                 │
│              ┌─────────────────────────┐                     │
│              │   Planner (doorman)     │                     │
│              │   Looks at WHERE.       │                     │
│              │   "doesn't filter by    │                     │
│              │    partition key —      │                     │
│              │    I can't discard"     │                     │
│              └────────────┬────────────┘                     │
│                           │                                  │
│              ❌ Opens events_2026_03, 04, 05, 06,            │
│                 07, 08, 09, ... ALL OF THEM                  │
└──────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. Pruning happens at TWO moments, and the difference shows up in the plan.

   • Planning-time pruning: the planner knows the WHERE values (they're literals) and discards partitions while building the plan. The discarded ones don't appear in the EXPLAIN: it's as if they didn't exist.
   • Runtime pruning (PG 11+): the planner does not know the values yet — they're parameters ($1) or STABLE functions like NOW() — so it builds the plan with every candidate partition and discards them at execution time. This is announced by a Subplans Removed: N line under the Append.

   You win in both cases. What changes is where you read it in the plan. A Subplans Removed: 23 is not a problem: it's pruning working in its late-binding form.

2. Pruning requires the WHERE to filter by the partition key. Without a filter on the partition key, the planner has no information to discard anything. It doesn't matter how selective the filter on other columns is — the planner looks only at the partition key to decide pruning.

3. The planner is conservative: when in doubt, it opens. If it can't mathematically prove that a partition contains no relevant rows, it includes it. This is for correctness safety — it prefers giving you slow correct results over risking fast incorrect ones. Your job is to help the planner be certain.

This architecture explains the cases where pruning fails: anything that obscures the WHERE for the planner — complex ORs, implicit casts, non-immutable functions, parameters without a clear type — results in "open them all, just in case".

Constraint exclusion: pruning's ancestor

There's a historical detail worth understanding. Before PostgreSQL 10, partitioning was done with table inheritance (child tables with CHECK constraints). The mechanism the planner used to discard partitions was called constraint exclusion: it reviewed each child table's CHECK constraints and if they contradicted the WHERE, it discarded them.

PostgreSQL 10+ introduced declarative partitioning and a new mechanism: partition pruning. It's faster (it doesn't walk constraints) and more capable (it works with prepared parameters, IN queries, etc.). But the old constraint_exclusion is still active for compatibility with inherited tables.

-- Relevant configuration (defaults in PG 16)
SHOW constraint_exclusion;     -- 'partition' (only for inheritance)
SHOW enable_partition_pruning; -- 'on' (the modern mechanism for declarative)

For declarative tables (what you're learning in this module), the one that matters is enable_partition_pruning. Leave it on (the default). If you ever see an old project with constraint_exclusion = on and slow queries on declarative partitions, it has no positive effect (it can have negative overhead). That setting is from the pre-10 era.

From here on, "pruning" refers to the modern declarative partitioning mechanism. It's what you'll always be using.

Reading an EXPLAIN: the 4 signs of successful pruning

Before the cases where pruning fails, internalize the positive signs. When pruning works, the plan has these characteristics:

Sign 1: only the partitions in the requested range appear

EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01';

Plan with successful pruning:

Aggregate
  ->  Seq Scan on events_2026_04 events
        Filter: ((created_at >= '2026-04-01') AND (created_at < '2026-05-01'))

Only events_2026_04. The other 23 partitions don't appear. Pruning worked.

Plan without pruning (same query, a problem in the WHERE):

Aggregate
  ->  Append
        ->  Seq Scan on events_2025_05 events_1
        ->  Seq Scan on events_2025_06 events_2
        ...
        ->  Seq Scan on events_2026_04 events_12
        ...
        ->  Seq Scan on events_2026_05 events_13

Every partition appears under Append. Pruning failed.

Sign 2: the exact partition name appears, not the parent

When pruning identifies a specific partition, the plan mentions the child's name (events_2026_04). If only the parent (events) appears, something is odd — investigate.

Sign 3: Subplans Removed: N — the signature of runtime pruning

When the planner does not know the WHERE values while building the plan (parameters $1, or STABLE functions like NOW()), it can't prune ahead of time. So it leaves the candidate partitions in the plan and discards them at execution time. That's announced like this:

Aggregate (actual rows=1 loops=1)
  ->  Append (actual rows=50000 loops=1)
        Subplans Removed: 23
        ->  Seq Scan on events_2026_04 events_1 (actual rows=50000 loops=1)
              Filter: ((created_at >= $1) AND (created_at < $2))

Read it like this: PostgreSQL prepared 24 partitions, discarded 23 at execution time, and only touched events_2026_04.

And now the most important part, because almost everyone reads it backwards:

The partitions that runtime pruning discards DO NOT appear in the plan. All that's left is the Subplans Removed: N counter and the survivors.

Two diagnostic rules follow from that:

• Subplans Removed: 23 = pruning worked. 23 partitions eliminated.
• Subplans Removed: 0 = nothing was discarded. It's the opposite of what it looks like. If you also see all 24 partitions listed, pruning failed — it isn't "working silently".

If the Subplans Removed line doesn't appear at all, that means pruning happened at planning time (the values were literals) and the discarded partitions simply never entered the plan.

Sign 4: Buffers: read=X proportional to the volume scanned

Comparing Buffers: shared read= between the plan with and without pruning gives you the magnitude of the benefit:

A 24× difference, aligned with the number of partitions avoided.

The 6 cases where pruning fails silently

Here comes the critical part of the capsule. These are the patterns that typically break pruning. Memorize them: when you see your query is slow on a partitioned table, check whether you've fallen into one of them.

Case 1: the WHERE doesn't filter by the partition key

Symptom: the query doesn't mention the partition key in the WHERE.

-- events table partitioned by created_at
SELECT * FROM events WHERE event_type = 'view' LIMIT 100;

Why it fails: without a filter on created_at, the planner has no information to decide which partitions of the time range to scan. Any partition could have rows with event_type = 'view'.

How to detect it: EXPLAIN shows Append with every partition.

How to fix it: add a date filter if it makes sense for the use case.

-- Assume that for this report we only care about recent views
SELECT * FROM events
WHERE event_type = 'view'
  AND created_at > NOW() - INTERVAL '30 days'
LIMIT 100;

Resulting plan:

Limit
  ->  Append
        ->  Seq Scan on events_2026_04 events_1
              Filter: (event_type = 'view')
        ->  Seq Scan on events_2026_05 events_2
              Filter: (event_type = 'view')

Only 2 partitions (the ones in the "last 30 days" range). Pruning applied.

Case 2: a query with OR mixing the partition key and another column

Symptom: the WHERE has an OR where one side filters by the partition key and the other doesn't.

SELECT * FROM events
WHERE created_at >= '2026-04-01'
   OR user_id = 42;

Why it fails: the planner can't apply pruning to an OR where one side isn't restricted to specific partitions. Rows with user_id = 42 could be in any partition. The planner opens them all to satisfy the OR.

How to detect it: EXPLAIN shows every partition despite one side of the OR having the partition key.

First, the bad news: there's no rewrite trick here. An OR with user_id = 42 forces you to look at every partition, because an event from user 42 could be in any month. That's not a planner limitation: it's the question you asked. No rewrite that preserves the result can avoid touching all 24 partitions.

⚠️ Beware of the "fix" that circulates out there. It's tempting to write this:

-- ❌ NOT the same query. It silently drops rows.
SELECT * FROM events WHERE created_at >= '2026-04-01'
UNION
SELECT * FROM events WHERE user_id = 42 AND created_at >= '2026-04-01';

Look at the second branch: by adding AND created_at >= '2026-04-01' you turned it into a subset of the first. Which means the second branch is dead code and the whole query is equivalent to a plain WHERE created_at >= '2026-04-01'. Events from user 42 before April — which the original OR did return — vanish:

-- original OR
SELECT count(*) FROM events WHERE created_at >= '2026-04-01' OR user_id = 42;
--  100001

-- the "fix" with UNION
SELECT count(*) FROM (
  SELECT * FROM events WHERE created_at >= '2026-04-01'
  UNION
  SELECT * FROM events WHERE user_id = 42 AND created_at >= '2026-04-01') t;
--  100000     ← it ate a row

One row missing. You gained speed by changing the answer, which is the worst kind of optimization.

What to actually do:

1. Ask yourself whether the OR was intentional. Very often it comes from a condition glued on without thinking and what you really wanted was an AND, or two separate queries. That's the real fix in most cases.

2. If the OR is correct, accept the scan and make it cheap. An index on user_id (which propagates to every partition) turns the 24 Seq Scans into 24 cheap Index Scans. You still open 24 partitions, but you're no longer reading 24 entire tables.

3. UNION does help — but only if you DON'T mutilate the second branch:

   SELECT * FROM events WHERE created_at >= '2026-04-01'
   UNION
   SELECT * FROM events WHERE user_id = 42;   -- no date filter: preserves the semantics

   Now it does return all 100001 rows. The first branch prunes to one partition; the second still touches all 24 (there's no way around it), but at least with the user_id index it's fast. The benefit is partial and honest.

Case 3: implicit cast or wrong type

Symptom: the column is TIMESTAMPTZ but the WHERE passes a DATE or a TEXT.

-- created_at is TIMESTAMPTZ
SELECT * FROM events WHERE created_at::date = '2026-04-15';

Why it fails: the ::date cast wraps the column. The planner can't use the partitions' metadata (which is based on TIMESTAMPTZ) to prune. It has to scan them all and apply the cast to every row.

How to detect it: EXPLAIN shows every partition, and the filter appears as (created_at)::date = '2026-04-15'::date.

How to fix it: rewrite as a range with no cast:

SELECT * FROM events
WHERE created_at >= '2026-04-15'
  AND created_at < '2026-04-16';

The planner sees the partition key directly and prunes to April's partition.

General pattern: avoid functions or casts on the partition column. Move them to the value side:

Case 4: a VOLATILE function, or a range open at the top

Symptom: a query with NOW() looks correct and yet the plan lists a pile of partitions.

SELECT count(*) FROM events WHERE created_at >= NOW() - INTERVAL '24 hours';

It's tempting to assume this prunes down to the current month's partition. It does not. This is the real plan (a table with 24 monthly partitions, where "today" falls in events_2026_07):

Aggregate
  ->  Append
        Subplans Removed: 14
        ->  Seq Scan on events_2026_07 events_1
              Filter: (created_at >= (now() - '24:00:00'::interval))
        ->  Seq Scan on events_2026_08 events_2
              Filter: (created_at >= (now() - '24:00:00'::interval))
        ->  Seq Scan on events_2026_09 events_3
        ...
        ->  Seq Scan on events_2027_04 events_10

There are two things to learn here:

(a) NOW() does prune, but at runtime. NOW() is STABLE, not IMMUTABLE: its value doesn't exist when the plan is built. That's why the discard shows up as Subplans Removed: 14 instead of "the old partitions never appeared". It works. It's not the problem.

(b) The real problem is that >= has no ceiling. You asked for "everything after yesterday" and you set no upper bound. As far as the planner is concerned, any future partition could contain qualifying rows. So it discards the 14 in the past and keeps the 10 from the current month onward. There's no bug: your WHERE genuinely spans those partitions.

How to fix it: close the range on both sides.

SELECT count(*) FROM events
WHERE created_at >= NOW() - INTERVAL '24 hours'
  AND created_at <  NOW();

With a ceiling, the planner also discards everything in the future and keeps the current month's partition.

And the case that truly has no rescue: VOLATILE functions. random() or clock_timestamp() can't be evaluated even at runtime-init, so there's no pruning of any kind:

-- ❌ No pruning possible: clock_timestamp() is VOLATILE
SELECT * FROM events WHERE created_at >= clock_timestamp() - INTERVAL '7 days';

The rule: IMMUTABLE → planning-time pruning. STABLE (like NOW()) → runtime pruning. VOLATILE → no pruning.

The cleanest option of all is to compute the value in the app and send it as a parameter. You stop depending on the function's volatility and the range is explicit on both sides:

from datetime import datetime, timezone, timedelta

end = datetime.now(timezone.utc)
start = end - timedelta(days=7)

stmt = select(Event).where(
    Event.created_at >= start,
    Event.created_at < end,
)

Case 5: a subquery or join where the planner loses the filter

Symptom: a join with another table "hides" the filter on the partition key.

SELECT e.* FROM events e
JOIN events_dates d ON e.created_at = d.date
WHERE d.date >= '2026-04-01';

Why it fails: the WHERE filters by d.date, not by e.created_at directly. The planner may or may not propagate the condition to the partitioned table (it depends on the version and the optimizer).

How to detect it: EXPLAIN. If the plan opens every partition of e, the filter didn't propagate.

How to fix it: repeat the filter explicitly on the partition key:

SELECT e.* FROM events e
JOIN events_dates d ON e.created_at = d.date
WHERE e.created_at >= '2026-04-01'
  AND d.date >= '2026-04-01';

It looks redundant at first glance but it gives the planner the explicit condition on e.created_at.

Case 6: IN with many values covering multiple partitions

Symptom: a table partitioned by list of tenants, with a very wide IN query.

SELECT * FROM tenant_data
WHERE tenant_id IN (1, 2, 3, 4, 5, ..., 50);

Why (technically it doesn't fail, but): pruning works, but the plan opens every partition containing the listed values. If the IN covers 50 tenants and each has its own partition, it opens 50 partitions. The relative benefit is smaller than if it were WHERE tenant_id = 1.

How to detect it: EXPLAIN shows many partitions (not all, but many).

How to fix it: depends on the use case. If you genuinely need all 50 tenants, there's no miracle — that's 50 partitions to scan. If the list comes from a previous query, consider:

• Process tenant by tenant in the app (50 individual queries with optimal pruning).
• Materialize the result if the list is stable (capsule 5 — materialized views).

Practical verification: 3 queries and their EXPLAINs

Here you see the 3 typical cases side by side. Assume an events table partitioned by created_at monthly with 24 partitions.

Query A: successful pruning

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), event_type
FROM events
WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01'
GROUP BY event_type;

Plan:

HashAggregate  (cost=82310.45..82311.95 rows=4 width=16)
               (actual time=39.812..39.814 rows=4 loops=1)
  Group Key: event_type
  Buffers: shared read=17542
  ->  Seq Scan on events_2026_04 events
        Filter: ((created_at >= '2026-04-01') AND (created_at < '2026-05-01'))
        Rows Removed by Filter: 0

Planning Time: 0.812 ms
Execution Time: 39.921 ms

Reading it:

• Only events_2026_04 appears. Pruning applied.
• Buffers: read=17542 (~140 MB) — what you'd expect for scanning one partition of 4M rows.
• Time: 40ms. The target query.

Query B: pruning fails due to a missing partition-key filter

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), event_type
FROM events
WHERE event_type = 'view'
GROUP BY event_type;

Plan:

Finalize HashAggregate
  Group Key: events.event_type
  Buffers: shared read=421688
  ->  Append
        ->  Partial HashAggregate
              ->  Seq Scan on events_2025_05 events_1
                    Filter: (event_type = 'view')
        ->  Partial HashAggregate
              ->  Seq Scan on events_2025_06 events_2
                    Filter: (event_type = 'view')
        ...
        ->  Partial HashAggregate
              ->  Seq Scan on events_2026_05 events_24
                    Filter: (event_type = 'view')

Planning Time: 1.234 ms
Execution Time: 4523.892 ms

Reading it:

• 24 partitions under Append. Pruning failed.
• Buffers: read=421688 (~3.4 GB) — it scans the whole table.
• Time: 4.5s.
• Solution: add a date filter if the case allows it.

Query C: pruning broken by a problematic cast

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at::date = '2026-04-15';

Plan:

Aggregate
  Buffers: shared read=421688
  ->  Append
        ->  Seq Scan on events_2025_05 events_1
              Filter: ((created_at)::date = '2026-04-15'::date)
        ...
        ->  Seq Scan on events_2026_05 events_24
              Filter: ((created_at)::date = '2026-04-15'::date)

Planning Time: 1.412 ms
Execution Time: 4892.231 ms

Reading it:

• 24 partitions. Pruning failed because of the ::date cast.
• The same overhead as Query B.
• Solution: rewrite as a range:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= '2026-04-15'
  AND created_at < '2026-04-16';

Resulting plan:

Aggregate
  Buffers: shared read=584
  ->  Seq Scan on events_2026_04 events
        Filter: ((created_at >= '2026-04-15') AND (created_at < '2026-04-16'))

Only events_2026_04. Pruning applied. Time drops to ~80ms.

Pruning from SQLAlchemy 2.0 async

The ORM doesn't decide pruning — that's the planner's job. But your code influences how the final SQL is formed, and that affects pruning.

The correct pattern: typed parameters

# services/event_service.py
from datetime import datetime, timezone
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.event import Event

async def count_events_in_range(
    session: AsyncSession,
    start: datetime,
    end: datetime,
) -> int:
    """Counts events in a range. SQLAlchemy passes start/end as typed
    parameters; the planner sees the exact values and prunes."""
    stmt = select(func.count()).select_from(Event).where(
        Event.created_at >= start,
        Event.created_at < end,
    )
    result = await session.execute(stmt)
    return result.scalar_one()

PostgreSQL receives created_at >= $1 AND created_at < $2 with specific TIMESTAMPTZ values. Pruning works.

Anti-pattern: filters that break pruning

# ❌ BAD: a cast in the WHERE
stmt = select(Event).where(
    func.cast(Event.created_at, sqlalchemy.Date) == date(2026, 4, 15)
)

# ❌ BAD: a non-stable function in the query
stmt = select(Event).where(
    Event.created_at >= func.now() - text("INTERVAL '7 days'")
)
# (NOW() is STABLE in PG 12+, but passing the value computed in Python is more explicit)

# ❌ BAD: an OR mixing the partition key and another column
stmt = select(Event).where(
    sqlalchemy.or_(
        Event.created_at >= cutoff,
        Event.user_id == 42
    )
)

Verifying with EXPLAIN from code

To audit your queries in development:

# debug/explain_query.py
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def explain_query(
    session: AsyncSession,
    stmt: Any,
) -> str:
    """Returns the EXPLAIN ANALYZE of a SQLAlchemy statement."""
    compiled = stmt.compile(
        session.bind,
        compile_kwargs={"literal_binds": True}
    )
    sql = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) {compiled}"
    result = await session.execute(text(sql))
    return "\n".join(row[0] for row in result.all())

# Usage:
plan = await explain_query(session, stmt)
print(plan)
# Look in the output: is there an Append with many partitions?
# How many Buffers are read?

Note: literal_binds=True injects the values directly into the SQL so the EXPLAIN is executable. Never use this in production (SQL injection risk if the values come from input). Debug only.

Why does this matter in real work?

1. It's the skill that separates "I partitioned and nothing changed" from "I partitioned and it improved 100×". Partitioning without verifying pruning is like installing a cache without measuring hit rate. Your lead will ask "how much did the latency drop?" and you need a concrete answer. This capsule gives you the tool to measure.

2. Performance regressions are inevitable if you don't audit new queries. Every new feature adds queries. Some can break pruning silently (a new dev adds WHERE LOWER(event_type) = 'view' without knowing it breaks the plan). Without the practice of auditing EXPLAIN, you find out when production burns.

3. Code review on partitioned-table queries becomes concrete. "This query doesn't use the partition key, it opens every partition — refactor or add a date filter". Without this capsule, code review is generic ("optimize the query"); with it, it's actionable.

4. Diagnosing production problems demands reading EXPLAIN. When the dashboard takes 8 seconds instead of 50ms, the first action is EXPLAIN ANALYZE in production. If you can't interpret a partitioned table's plan, you're blind.

5. Conversations with DBAs and SREs put you at their level. "The planner is discarding 22 of 24 partitions, we see Subplans Removed: 22" is vocabulary a DBA recognizes immediately. It validates you as a technical peer.

Traps and common mistakes

Mistake 1 (conceptual): assuming partitioning always speeds things up, without verifying pruning

Symptom: you partition events, run the API, notice no improvement. "Partitioning didn't help."

Why it happens: the queries the planner executes don't leverage pruning. It could be any of the 6 cases from the previous section. The partitioning is well implemented, but the queries don't use it.

How to tell: run EXPLAIN ANALYZE on the 5-10 dominant queries (the ones that appear in pg_stat_statements). If they all show Append with every partition, the problem is pruning, not partitioning.

How to fix it: audit each query individually. Apply the matching pattern from the 6 cases. Refactor the queries to include the partition key in the WHERE.

Mistake 2 (practical): misreading runtime pruning

Symptom: a query with a prepared statement ($1, $2) shows a strange plan and you don't know whether pruning is working or not.

Why it happens: with parameters, the planner doesn't know the values while building the plan, so the discard happens at execution time (runtime pruning, PG 11+). The plan looks different from one with literals, and it's easy to draw the wrong conclusion.

How to tell — the only rule you need: look for the Subplans Removed: N line under the Append.

⚠️ The most common misunderstanding: believing that a partition with actual rows=0 was "discarded by runtime pruning". No. If the partition appears in the plan, it was opened and executed; returning 0 rows only means the filter found nothing there. The partitions runtime pruning actually discards do not appear in the plan — they're counted in Subplans Removed and nothing else.

How to fix it: if you see Subplans Removed: 0 with every partition, the problem is in the WHERE (review the 6 cases), not in the parameters.

Mistake 3 (conceptual): thinking pruning solves slow queries inside a partition

Symptom: pruning works (only events_2026_04 is scanned), but that partition has 50M rows and the query still takes 2 seconds.

Why it happens: pruning gives you "fewer partitions to scan". But inside each partition that does get scanned, queries still need the right indexes. If your partition has 50M rows and the query doesn't use an index, it scans all 50M.

How to tell: the plan inside the partition shows Seq Scan when you expected Index Scan. It's an indexing problem, not a partitioning one.

How to fix it: make sure the indexes are properly defined on the parent table (they propagate to the children). Go back to guide #12 to review advanced indexing inside each partition.

Mistake 4 (practical): SQLAlchemy generates SQL that breaks pruning without you noticing

Symptom: your code uses a SQLAlchemy expression that conceptually filters by the partition key, but the generated SQL has a cast or function that breaks pruning.

# It looks right to you:
stmt = select(Event).where(
    func.date_trunc('month', Event.created_at) == datetime(2026, 4, 1)
)

The generated SQL:

SELECT * FROM events WHERE date_trunc('month', created_at) = '2026-04-01';

date_trunc(created_at) wraps the column. Pruning fails.

How to detect it: run EXPLAIN on the generated SQL. If you see the column wrapped in a function, pruning is going to fail.

How to fix it: rewrite it in SQLAlchemy as a range:

from datetime import datetime
start = datetime(2026, 4, 1)
end = datetime(2026, 5, 1)

stmt = select(Event).where(
    Event.created_at >= start,
    Event.created_at < end,
)

Generated SQL:

SELECT * FROM events WHERE created_at >= $1 AND created_at < $2;

Pruning works.

Mistake 5 (conceptual): assuming enable_partition_pruning = on guarantees pruning

Symptom: you check SHOW enable_partition_pruning and it's on, but EXPLAIN shows every partition. "The parameter is enabled, why isn't it working?"

Why it happens: enable_partition_pruning = on only says "the planner will try to prune if it can". But "if it can" depends on the WHERE: it needs a filter on the partition key, without casts/functions obscuring it. The parameter isn't magic.

How to tell: the setting is a necessary but not sufficient condition. If it's off, there's definitely no pruning. If it's on, there's pruning when the WHERE allows it.

How to fix it: read the EXPLAIN of the specific query. If the partitions appear, the problem is in the query's WHERE, not in the configuration.

Exercises

Exercise 1: identify pruning in 4 plans

For each of these plans, decide: is there pruning? How many partitions are scanned? Is it a good plan?

Plan A:

Aggregate
  ->  Seq Scan on events_2026_04 events
        Filter: (created_at >= '2026-04-01' AND created_at < '2026-05-01')

Plan B:

Aggregate
  ->  Append
        ->  Seq Scan on events_2025_05 events_1
              Filter: (event_type = 'view')
        ->  Seq Scan on events_2025_06 events_2
        ...
        ->  Seq Scan on events_2026_05 events_24

Plan C:

Limit
  ->  Sort
        ->  Append
              ->  Index Scan on events_2026_04_pkey
                    Index Cond: (user_id = 42)
              ->  Index Scan on events_2026_05_pkey
                    Index Cond: (user_id = 42)

Plan D:

Append (actual rows=0 loops=1)
  Subplans Removed: 22
  ->  Seq Scan on events_2026_04 events_1 (actual rows=0 loops=1)
  ->  Seq Scan on events_2026_05 events_2 (actual rows=0 loops=1)

Exercise 2: fix a query with broken pruning

The following query runs slowly on events, partitioned by created_at monthly. Identify why pruning fails and rewrite it.

SELECT user_id, count(*)
FROM events
WHERE EXTRACT(YEAR FROM created_at) = 2026
  AND EXTRACT(MONTH FROM created_at) = 4
GROUP BY user_id;

SELECT user_id, count(*)
FROM events
WHERE created_at >= '2026-04-01'
  AND created_at < '2026-05-01'
GROUP BY user_id;

-- Before (broken pruning)
EXPLAIN ANALYZE
SELECT user_id, count(*) FROM events
WHERE EXTRACT(YEAR FROM created_at) = 2026
  AND EXTRACT(MONTH FROM created_at) = 4
GROUP BY user_id;
-- Result: Append with 24 partitions, ~5 seconds

-- After (pruning applied)
EXPLAIN ANALYZE
SELECT user_id, count(*) FROM events
WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01'
GROUP BY user_id;
-- Result: Seq Scan on events_2026_04 only, ~80 ms

Exercise 3: detect pruning in SQLAlchemy

You have this SQLAlchemy 2.0 async function. Identify whether it generates SQL that leverages pruning. If not, rewrite it.

from datetime import date
from sqlalchemy import func, select, cast, Date
from sqlalchemy.ext.asyncio import AsyncSession

async def get_events_for_day(
    session: AsyncSession,
    target_day: date,
) -> list[Event]:
    stmt = select(Event).where(
        cast(Event.created_at, Date) == target_day
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

SELECT * FROM events WHERE CAST(created_at AS DATE) = '2026-04-15';

from datetime import date, datetime, timedelta, timezone
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

async def get_events_for_day(
    session: AsyncSession,
    target_day: date,
) -> list[Event]:
    """Gets events for a specific day. Generates SQL that leverages pruning."""
    start = datetime.combine(target_day, datetime.min.time(), timezone.utc)
    end = start + timedelta(days=1)

    stmt = select(Event).where(
        Event.created_at >= start,
        Event.created_at < end,
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

SELECT * FROM events WHERE created_at >= $1 AND created_at < $2;
-- With $1 = '2026-04-15 00:00:00+00' and $2 = '2026-04-16 00:00:00+00'

from sqlalchemy import text

# Before (no pruning)
result = await session.execute(text("""
    EXPLAIN ANALYZE
    SELECT * FROM events WHERE CAST(created_at AS DATE) = '2026-04-15'
"""))
# Output: Append with 24 partitions, ~4.5s

# After (with pruning)
result = await session.execute(text("""
    EXPLAIN ANALYZE
    SELECT * FROM events WHERE created_at >= '2026-04-15' AND created_at < '2026-04-16'
"""))
# Output: Seq Scan on events_2026_04, ~30ms

Exercise 4: distinguish runtime pruning from "there is no pruning"

Two parameterized queries on events (24 monthly partitions) produce these two plans. For each: is there pruning? What kind? How many partitions are actually touched?

Plan 1:

Aggregate (actual rows=1 loops=1)
  ->  Append (actual rows=50000 loops=1)
        Subplans Removed: 23
        ->  Seq Scan on events_2026_04 events_1 (actual rows=50000 loops=1)
              Filter: ((created_at >= $1) AND (created_at < $2))

Plan 2:

Aggregate
  ->  Append
        ->  Seq Scan on events_2025_05 events_1
              Filter: (created_at >= (clock_timestamp() - '7 days'::interval))
        ->  Seq Scan on events_2025_06 events_2
              Filter: (created_at >= (clock_timestamp() - '7 days'::interval))
        ...
        ->  Seq Scan on events_2027_04 events_24
              Filter: (created_at >= (clock_timestamp() - '7 days'::interval))

Exercise 5: audit of dominant queries

Your lead asks you to audit the 5 dominant queries (extracted from pg_stat_statements) against the partitioned events table. For each, decide whether pruning is applying and propose a fix if not:

-- Query 1
SELECT * FROM events WHERE id = $1;

-- Query 2
SELECT count(*) FROM events
WHERE created_at >= NOW() - INTERVAL '24 hours';

-- Query 3
SELECT user_id, count(*) FROM events
WHERE event_type IN ('view', 'like')
GROUP BY user_id LIMIT 100;

-- Query 4
SELECT * FROM events
WHERE post_id = $1 AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC LIMIT 50;

-- Query 5
SELECT * FROM events e
JOIN users u ON e.user_id = u.id
WHERE u.created_at > '2026-01-01';

Summary and next step

In this capsule you learned to verify that partitioning produces the expected benefit:

• Partition pruning is the planner's ability to discard partitions. Without pruning, partitioning adds overhead with no benefit.

• It happens at two moments: planning time (the values are literals; the discarded partitions never enter the plan) and runtime (the values are parameters or STABLE functions like NOW(); the discard is announced with Subplans Removed: N). Both are good pruning.

• The reading rule that avoids 90% of diagnostic errors: the partitions that appear in the plan were executed, period. A partition with actual rows=0 was not "pruned": it was opened and found no rows. The genuinely pruned ones aren't listed — they're only counted in Subplans Removed. That's why Subplans Removed: 0 with 24 partitions listed means pruning failed, not that it's working silently.

• 6 cases where pruning fails silently: a WHERE with no partition key; an OR mixing the partition key and another column; casts/functions on the column; VOLATILE functions (clock_timestamp(), random()) or ranges open at the top (>= NOW() - INTERVAL ... with no ceiling leaves every future partition in the plan); joins that lose the filter; a very wide IN.

• The function's volatility rules everything: IMMUTABLE → planning-time pruning. STABLE (NOW()) → runtime pruning. VOLATILE → no pruning.

• Beware of "optimizations" that change the result. An OR on a non-partition-key column cannot be pruned, and the UNION that circulates as a fix usually mutilates the second branch and silently drops rows. If you rewrite a query, count the rows before and after.

• Constraint exclusion vs partition pruning: the former is legacy (table inheritance, pre-PG 10), the latter is modern (declarative partitioning, PG 10+). For everything you learn here, pruning is what matters.

• SQLAlchemy generates the SQL but doesn't decide pruning: your code influences how the WHERE is formed, which directly affects whether the planner can apply pruning. Avoid casts and functions on the partition key in ORM queries.

Before moving on, you should be able to:

• Read EXPLAIN ANALYZE on a partitioned table and say how many partitions are actually scanned.
• Identify the 6 typical reasons pruning fails.
• Rewrite queries that break pruning (casts, OR, functions) so the planner can decide.
• Audit your dominant queries with pg_stat_statements + EXPLAIN to validate that partitioning is paying off.

Next capsule — pg_partman and automated maintenance. You now know how to decide when to partition, the 3 types, and how to verify pruning works. What's missing is the operational side: who creates next month's partitions? who drops the old ones according to retention?. Without automation, partitioning becomes a "dirty area" that someone has to remember to maintain by hand. Capsule 07 teaches you pg_partman, the extension that automates the full lifecycle: creating future partitions, dropping old ones per the retention policy, health monitoring. It's the piece that makes partitioning sustainable in production.

Resources

1. PostgreSQL 16 — Partition Pruning — section 5.11.4, the official reference. Covers planning-time vs runtime pruning with examples.
2. PostgreSQL 16 — enable_partition_pruning parameter — the setting that controls the planner's behavior.
3. depesz — Partition pruning explained — the history and benchmarks of the introduction of modern pruning in PG 11.
4. Crunchy Data — Partition Pruning Performance — an analysis of when pruning helps and when it doesn't, with real cases.
5. 2ndQuadrant — How Postgres Partition Pruning Works — a technical deep dive into the planner's algorithm.
6. PostgreSQL Wiki — EXPLAIN — how to read EXPLAIN in general, with a section specific to partitioned tables.
7. pg_stat_statements docs — to identify the dominant queries you need to audit.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: pg_partman and automated maintenance — the operational piece that makes partitioning sustainable.