Postgres partition pruning not applied: a cast on the key scanned all 36 partitions

Three months after we partitioned the orders table by month, the finance team's daily revenue report was slower than before the migration. The report covers one calendar day, so it should read one partition. Instead it took 48 seconds and the replica it runs on showed a burst of sequential reads every morning at 06:00.

The plan told the story immediately:

Finalize GroupAggregate  (actual time=47811.204..47811.390 rows=14 loops=1)
  ->  Gather Merge  ...
        ->  Partial HashAggregate ...
              ->  Parallel Append  (actual time=0.412..46902.117 rows=81233 loops=3)
                    ->  Parallel Seq Scan on orders_2022_01 orders_1
                          Filter: (((created_at AT TIME ZONE 'Europe/Berlin'::text))::date = '2025-01-15'::date)
                          Rows Removed by Filter: 5104332
                    ->  Parallel Seq Scan on orders_2022_02 orders_2
                    ...
                    ->  Parallel Seq Scan on orders_2024_12 orders_36
Execution Time: 47811.802 ms

Thirty-six partitions scanned, one of which had relevant rows.

Reproducing it

The report query, simplified:

SELECT o.currency, sum(o.total_cents)
FROM orders o
WHERE (o.created_at AT TIME ZONE 'Europe/Berlin')::date = DATE '2025-01-15'
GROUP BY o.currency;

Running EXPLAIN without ANALYZE was enough: every partition appeared under the Append node, and there was no Subplans Removed line anywhere.

What we ruled out

• Pruning disabled. SHOW enable_partition_pruning; returned on on both primary and replica.
• Wrong partition bounds. \d+ orders listed clean monthly ranges FROM ('2025-01-01 00:00:00+00') TO ('2025-02-01 00:00:00+00'). A test query with a literal range on created_at pruned correctly to one partition.
• Default partition catching rows. There was a default partition, but it was empty, and its presence does not stop pruning of the others.

Root cause

The partition key is created_at. Pruning works by comparing the query's conditions on the key against each partition's bounds. Our report did not put a condition on created_at; it put one on (created_at AT TIME ZONE 'Europe/Berlin')::date, which is a different expression. Postgres cannot reason backwards from "the date in Berlin is January 15" to a range of created_at values, so it has nothing to prune with and keeps every partition.

A second query in the same report had a subtler version: it joined orders to a small report_days table and filtered on report_days.day. The condition on the key arrived through a hash join, where runtime pruning does not happen.

What is happening under the hood

Postgres prunes partitions at up to three moments:

All three require the condition to be on the partition key expression itself, using an operator from the key's B-tree operator family. Anything that transforms the column first (a cast, date_trunc, AT TIME ZONE, coalesce) produces a new expression that does not match the key, and pruning is skipped silently. There is no warning; the plan just lists every partition.

Joins are a common second trap. Run-time pruning during execution is supported for parameters passed into the inner side of a nested loop. With a hash join or merge join, the partitioned side is scanned in full before the join can filter it.

Environment

Minimal reproduction

CREATE TABLE orders (
  id          bigint NOT NULL,
  currency    text NOT NULL,
  total_cents bigint NOT NULL,
  created_at  timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

DO $$
DECLARE m date := DATE '2024-01-01';
BEGIN
  WHILE m < DATE '2025-03-01' LOOP
    EXECUTE format(
      'CREATE TABLE orders_%s PARTITION OF orders FOR VALUES FROM (%L) TO (%L)',
      to_char(m, 'YYYY_MM'), m::timestamptz, (m + interval '1 month')::timestamptz);
    m := m + interval '1 month';
  END LOOP;
END $$;

INSERT INTO orders
SELECT g, (ARRAY['EUR','USD','GBP'])[1 + g % 3], (random() * 10000)::bigint,
       TIMESTAMPTZ '2024-01-01 00:00:00+00' + g * interval '10 seconds'
FROM generate_series(1, 3500000) AS g;
ANALYZE orders;

-- no pruning: expression on the key
EXPLAIN SELECT count(*) FROM orders
WHERE (created_at AT TIME ZONE 'Europe/Berlin')::date = DATE '2025-01-15';

-- plan-time pruning: plain range on the key
EXPLAIN SELECT count(*) FROM orders
WHERE created_at >= TIMESTAMPTZ '2025-01-15 00:00:00+01'
  AND created_at <  TIMESTAMPTZ '2025-01-16 00:00:00+01';

-- startup pruning: stable expression
EXPLAIN SELECT count(*) FROM orders
WHERE created_at >= now() - interval '1 day';

The first plan lists all 14 partitions, the second lists one, and the third lists Subplans Removed: 13 under the Append.

Who is affected

Every PostgreSQL version with declarative partitioning behaves this way; the detail of runtime pruning has improved since version 11, but expressions on the key have never been prunable. Report tools, ORMs that group by date, and "convenience" views that expose a local-date column are the usual sources. Tables partitioned by timestamptz and queried by local calendar date are especially exposed.