Re: Postgresql 14/15/16/17 partition pruning on dependent table during join - Mailing list pgsql-performance
| From | Stepan Yankevych |
|---|---|
| Subject | Re: Postgresql 14/15/16/17 partition pruning on dependent table during join |
| Date | |
| Msg-id | VE1PR03MB566458B841E7B0AE1E15FA2692512@VE1PR03MB5664.eurprd03.prod.outlook.com Whole thread Raw |
| In response to | Re: Postgresql 14/15/16/17 partition pruning on dependent table during join (Andrei Lepikhov <lepihov@gmail.com>) |
| Responses |
Re: Postgresql 14/15/16/17 partition pruning on dependent table during join
|
| List | pgsql-performance |
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@gmail.com>
Sent: Sunday, November 3, 2024 4:42 AM
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; Stepan Yankevych <Stepan_Yankevych@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
Sent: Sunday, November 3, 2024 4:42 AM
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; Stepan Yankevych <Stepan_Yankevych@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_Yankevych@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
>
> 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
--
regards, Andrei Lepikhov
> On Fri, 1 Nov 2024 at 18:51, Stepan Yankevych <Stepan_Yankevych@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
>
> 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
--
regards, Andrei Lepikhov
pgsql-performance by date: