public inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: David Rowley <dgrowleyml@gmail.com>
To: Cándido Antonio Martínez Descalzo <candido@ninehq.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Potential partition pruning regression on PostgreSQL 18
Date: Thu, 2 Apr 2026 12:00:48 +1300
Message-ID: <CAApHDvooK9iKSDWu45D3tjMBec2=6dXzKk_LzaGj_DqfTs6Xvw@mail.gmail.com> (raw)
In-Reply-To: <CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com>
References: <CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com>

On Thu, 2 Apr 2026 at 00:57, Cándido Antonio Martínez Descalzo
<candido@ninehq.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/

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dgrowleyml@gmail.com, candido@ninehq.com, guofenglinux@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: Potential partition pruning regression on PostgreSQL 18
  In-Reply-To: <CAApHDvooK9iKSDWu45D3tjMBec2=6dXzKk_LzaGj_DqfTs6Xvw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox