Let's classify it as possible improvement / new feature for further releases. Optimizer definitely should be able to add that extra (redundant) condition and e.exec_date_id >= 20241021 or even transform e.exec_date_id >= co.create_date_id to e.exec_date_id >= 20241021
Stepan Yankevych ________________________________ From: Andrei Lepikhov <lepi...@gmail.com> Sent: Sunday, November 3, 2024 4:42 AM To: Vijaykumar Jain <vijaykumarjain.git...@gmail.com>; Stepan Yankevych <stepan_yankev...@epam.com> Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org> Subject: 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_yankev...@epam.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://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fddl-partitioning.html%23DDL-PARTITION-PRUNING&data=05%7C02%7CStepan_Yankevych%40epam.com%7Cb0119e5e3c5e47a7dd5f08dcfbb13be0%7Cb41b72d04e9f4c268a69f949f367c91d%7C1%7C0%7C638661985836678039%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=xqVRyWW11KoN0qFb%2FZsTO%2FjijULLW84NSW8lURa5UzY%3D&reserved=0<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://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2FCAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%2540mail.gmail.com&data=05%7C02%7CStepan_Yankevych%40epam.com%7Cb0119e5e3c5e47a7dd5f08dcfbb13be0%7Cb41b72d04e9f4c268a69f949f367c91d%7C1%7C0%7C638661985836699390%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=P3TVf%2FVm2s48xqB00DBaO0LQAlq4%2BGdcXbtpEU0XNi4%3D&reserved=0<https://www.postgresql.org/message-id/flat/CAKJS1f9FK_X_5HKcPcSeimy16Owe3EmPmmGsGWLcKkj_rW9s6A%40mail.gmail.com> -- regards, Andrei Lepikhov
