|
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 <lepihov@xxxxxxxxx>
Sent: Sunday, November 3, 2024 4:42 AM To: Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx>; Stepan Yankevych <Stepan_Yankevych@xxxxxxxx> Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx> 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_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://eur01.safelinks.protection.outlook.com/?url=""> > > 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=""> -- regards, Andrei Lepikhov |
