MIME-Version: 1.0
References: 
 <CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com>
In-Reply-To: 
 <CAH5YaUwVUWETTyVECTnhs7C=CVwi+uMSQH=cOkwAUqMdvXdwWA@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 2 Apr 2026 12:00:48 +1300
Message-ID: 
 <CAApHDvooK9iKSDWu45D3tjMBec2=6dXzKk_LzaGj_DqfTs6Xvw@mail.gmail.com>
Subject: Re: Potential partition pruning regression on PostgreSQL 18
To: =?UTF-8?Q?C=C3=A1ndido_Antonio_Mart=C3=ADnez_Descalzo?=
 <candido@ninehq.com>,
	Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvooK9iKSDWu45D3tjMBec2%3D6dXzKk_LzaGj_DqfTs6Xvw%40mail.gmail.com>
Precedence: bulk

On Thu, 2 Apr 2026 at 00:57, C=C3=A1ndido Antonio Mart=C3=ADnez Descalzo
<candido@ninehq.com> wrote:
> We noticed that one of our queries unexpectedly stopped applying partitio=
n pruning on PG18, although it applies it on PG16 and PG17. The issue has b=
een 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 th=
e schema, query and resulting plans in PG17 and PG18 are provided below. So=
me 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 provi=
ded further below)
> Keeping the view and using a single "group by" instead of multiple groupi=
ng 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/