Re: regression in PG 15.1

On Mon, Nov 28, 2022 at 7:45 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Scott Ribe <scott_ribe(at)elevated-dev(dot)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.

https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITION-PRUNING

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.