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

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.