Why don't I see partition pruning for my query based on generate_series()?

Question

I'm using declarative partitioning in PostgreSQL 12:

create table foo (
    id  integer not null,
    date timestamp not null,
    count integer default 0,
    primary key (id, date)
)
    partition by RANGE (date);

With this simple request, Postgres searches through the only one proper partition - which is great:

select sum(foo.count) as total,
       date_trunc('day', foo.date) as day_date
from foo
where foo.date between '2023-01-01' and '2023-01-02'
group by day_date

However, when I modify this query using generate_series(), Postgres starts to search through all partitions:

with times as (
    select generate_series('2023-01-01 12:00', '2023-01-02 16:00', '1 hour') as date1,
           generate_series('2023-01-01 16:00', '2023-01-02 20:00', '1 hour') as date2
)
select sum(foo.count) as total,
       times.date1
from times join foo on foo.date between times.date1 and times.date2
group by times.date1;

I assume, the query planner plans to ignore some partitions before executing the query just by looking at it. Which means, I cannot use dynamically generated parameters for filtering?
Are there some approaches to solve this?

Answer 1

Most probably, you just missed that partitions are pruned for your second query, too.

Fix your DB design and query first

• Don't use the misleading name "date" for a timestamp. Proceeding with "ts" instead.

• Change your primary key to PRIMARY KEY (ts, id). Functionally the same, but ts as leading column in the PK index makes a big difference for your queries. See:

  • Is a composite index also good for queries on the first field?

• Don't use BETWEEN for timestamps, which includes the upper bound, while partition boundaries exclude the upper bound (like any sane range operation on timestamps). The manual:

  When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound.

  Else, you invite all kinds of confusion with bounds that don't match.

Explanation how it (probably) works already

We need enable_partition_pruning turned on - which you obviously have, and is default anyway. This covers two distinct methods of partition pruning: one while planning the query, another one during execution. The manual:

Enables or disables the query planner's ability to eliminate a partitioned table's partitions from query plans. This also controls the planner's ability to generate query plans which allow the query executor to remove (ignore) partitions during query execution. The default is on. See Section 5.11.4 for details.

Your first, simple query with a constant filter can use the first method because values are known at planning time. That's not the case for your second query: values are generated later. So only the second variant is possible - which was added with Postgres 11. The release notes:

Allow partition elimination during query execution (David Rowley, Beena Emerson)

Previously, partition elimination only happened at planning time, meaning many joins and prepared queries could not use partition elimination.

You declared Postgres 12, so it should work.
Aside, it won't hurt to upgrade to the latest version. The release notes Postgres 13:

Allow pruning of partitions to happen in more cases (Yuzuko Hosoya, Amit Langote, Álvaro Herrera)

Changes were discussed in this thread on pgsql-hackers.
But your simple query should get partition pruning in Postgres 12, too.

The second method of partition pruning during execution does not (cannot) show in the simple query plan from EXPLAIN. You have to test with EXPLAIN (ANALYZE), then you'll see (never executed) for partitions that are always pruned. The manual:

[...] Determining if partitions were pruned during this phase requires careful inspection of the loops property in the EXPLAIN ANALYZE output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as (never executed) if they were pruned every time.

Inspect the query plans in this fiddle!

The query plan (even from plain EXECUTE) for your first (improved!) query shows partition pruning during planning: only the first partition is involved to begin with.

The query plan (even from plain EXECUTE) for your second (improved!) query shows (never executed) for partition 3, which is pruned all the time, but not for partition 1 & 2, which are only pruned for some of the iterations.

Related:

• How to utilize partition pruning with subqueries or joins?