Re: regression in PG 15.1 - Mailing list pgsql-admin

From David G. Johnston
Subject Re: regression in PG 15.1
Date
Msg-id CAKFQuwam1bzKUVEfruZZv8jGddTf3xrHQ4fNKaGwySXi_L=35Q@mail.gmail.com
Whole thread Raw
In response to Re: regression in PG 15.1  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
On Mon, Nov 28, 2022 at 7:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
>   select * from tbl where txid > something and tbl_id = (select id from reftbl where name = 'someval');
> ^^^ fails to exclude partitions, kicks off parallel scans on all 142, takes 23,170ms

Why do you say this is a regression?  From what I know of the partition
pruning logic (admittedly not a whole lot), I don't think we'd have
ever pruned on the basis of such a constraint.


The second bullet point here indicates the subquery should prune during execution.


During actual execution of the query plan. Partition pruning may also be performed here to remove partitions using values which are only known during actual query execution. This includes values from subqueries [...]

That subquery seems like it should meet whatever criteria there is for this runtime pruning.  Namely, it must return zero or one rows and it not correlated with the rest of the query.

David J.

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: regression in PG 15.1
Next
From: Scott Ribe
Date:
Subject: Re: regression in PG 15.1