Re: Potential partition pruning regression on PostgreSQL 18

On Thu, 2 Apr 2026 at 00:57, Cándido Antonio Martínez Descalzo
<candido(at)ninehq(dot)com> wrote:
> We noticed that one of our queries unexpectedly stopped applying partition pruning on PG18, although it applies it on PG16 and PG17. The issue has been replicated on Linux and macOS.
>
> Failing to apply partition pruning significantly impacts the performance of these queries.
>
> We recreated the issue using a simplified schema and query. Details on the schema, query and resulting plans in PG17 and PG18 are provided below. Some changes in the query restore partition pruning in PG18, specifically:
>
> Replacing the view and date condition used with a sub-query or CTE with the same condition restores partition pruning (updated query and plan provided further below)
> Keeping the view and using a single "group by" instead of multiple grouping sets restores partition pruning (updated query and plan provided further below)
>
>
> Does anybody know if there is a documented behaviour change in PG18 that could explain this or if this is a known issue?

It relates to the "This release also fixes some GROUPING SETS queries
that used to return incorrect results." mentioned in [1]. Basically,
match_clause_to_partition_key() now sees a PlaceHolderVar rather than
the Var, which is the partition key column.

The question is, can we do the same thing in
match_clause_to_partition_key() as we did for index clauses in
ad66f705f. The PlaceHolderVar's phnullingrels are empty for this
query, so I expect we just need to give the same treatment to
partition key columns as was done for indexes columns in
fix_indexqual_operand().

Richard, any thoughts?

David

[1] https://www.postgresql.org/docs/release/18.0/