On 3/11/2024 03:21, Vijaykumar Jain wrote:
On Fri, 1 Nov 2024 at 18:51, Stepan Yankevych <Stepan_Yankevych@xxxxxxxx> 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
