Re: Postgresql 14/15/16/17 partition pruning on dependent table during join

On 3/11/2024 03:21, Vijaykumar Jain wrote:
> On Fri, 1 Nov 2024 at 18:51, Stepan Yankevych <Stepan_Yankevych(at)epam(dot)com> wrote:
>>
>> Partition pruning is not pushing predicate into dependent table during join in some cases.
>> See example. Predicate highlighted in red
>>
>
> i think your observation is correct.
> you may need to provide redundant predicates for join both tables to
> prune partition (as below).
>
> there is explanation on how dynamic pruning works for some cases, but
> idk which part satisfies this case.
> https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING
>
> explain select *
> from public.orders co
> left join public.execution e on e.order_id = co.order_id and
> e.exec_date_id >= co.create_date_id
> where co.order_text in ('Order 5259 - F968FDC8')
> and co.create_date_id = 20241021
> and e.exec_date_id >= 20241021; -- this is redundant but without this
> pruning does not work.
>
> i can be corrected and would be great if someone explains with more
> detail which i cannot due to lack of understanding of dynamic pruning.
I guess you think that Postgres should create an additional clause on
the 'e.exec_date_id from' the chain of:

'co.create_date_id = 20241021 and e.exec_date_id >= co.create_date_id'

but Postgres doesn't have such a functionality yet. It can deduce
clauses from equivalence clauses only. For example, having 'x=1 AND
x=y', Postgres can build a new clause 'y=1'. But it doesn't work for
inequalities [1].
So, to perform partition pruning on the table 'e', you need to add this
redundant clause.

[1]
https://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com

--
regards, Andrei Lepikhov