Performing partition pruning using row value

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Performing partition pruning using row value

kato-sho@fujitsu.com
Hello

I would like to ask about the conditions under which partition pruning is performed.
In PostgreSQL 12, when I executed following SQL, partition pruning is not performed.

postgres=# explain select * from a where (c1, c2) < (99, 99);
                           QUERY PLAN
----------------------------------------------------------------
 Append  (cost=0.00..60.00 rows=800 width=40)
   ->  Seq Scan on a1 a_1  (cost=0.00..28.00 rows=400 width=40)
         Filter: (ROW(c1, c2) < ROW(99, 99))
   ->  Seq Scan on a2 a_2  (cost=0.00..28.00 rows=400 width=40)
         Filter: (ROW(c1, c2) < ROW(99, 99))
(5 rows)

However, pruning is performed when I changed the SQL as follows.

postgres=# explain select * from a where c1  < 99 and c2 < 99;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on a1 a  (cost=0.00..28.00 rows=133 width=40)
   Filter: ((c1 < 99) AND (c2 < 99))
(2 rows)

These tables are defined as follows.

create table a( c1 int, c2 int, c3 varchar) partition by range(c1, c2);
create table a1 partition of a for values from(0, 0) to (100, 100);
create table a2 partition of a for values from(100, 100) to (200, 200);


Looking at the code, "(c1, c2) < (99, 99)" is recognized as RowCompExpr and "c1 < 99 and c2 < 99" is recognized combination of OpExpr.

Currently, pruning is not performed for RowCompExpr, is this correct?
Also, at the end of match_clause_to_partition_key(), the following Comments like.

"Since the qual didn't match up to any of the other qual types supported here, then trying to match it against any other partition key is a waste of time, so just return PARTCLAUSE_UNSUPPORTED."

Because it would take a long time to parse all Expr nodes, does match_cluause_to_partition_key() return PART_CLAUSE_UNSUPPORTED when such Expr node is passed?

If the number of args in RowCompExpr is small, I would think that expanding it would improve performance.

regards,
sho kato


Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Etsuro Fujita-2
Kato-san,

On Mon, Jul 6, 2020 at 5:25 PM [hidden email]
<[hidden email]> wrote:

> I would like to ask about the conditions under which partition pruning is performed.
> In PostgreSQL 12, when I executed following SQL, partition pruning is not performed.
>
> postgres=# explain select * from a where (c1, c2) < (99, 99);
>                            QUERY PLAN
> ----------------------------------------------------------------
>  Append  (cost=0.00..60.00 rows=800 width=40)
>    ->  Seq Scan on a1 a_1  (cost=0.00..28.00 rows=400 width=40)
>          Filter: (ROW(c1, c2) < ROW(99, 99))
>    ->  Seq Scan on a2 a_2  (cost=0.00..28.00 rows=400 width=40)
>          Filter: (ROW(c1, c2) < ROW(99, 99))
> (5 rows)
>
> However, pruning is performed when I changed the SQL as follows.
>
> postgres=# explain select * from a where c1  < 99 and c2 < 99;
>                        QUERY PLAN
> --------------------------------------------------------
>  Seq Scan on a1 a  (cost=0.00..28.00 rows=133 width=40)
>    Filter: ((c1 < 99) AND (c2 < 99))
> (2 rows)

Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent
to the condition c1 < 99 and c2 < 99 (see the documentation note in
[1]).

> Looking at the code, "(c1, c2) < (99, 99)" is recognized as RowCompExpr and "c1 < 99 and c2 < 99" is recognized combination of OpExpr.
>
> Currently, pruning is not performed for RowCompExpr, is this correct?

Yeah, I think so.

> Because it would take a long time to parse all Expr nodes, does match_cluause_to_partition_key() return PART_CLAUSE_UNSUPPORTED when such Expr node is passed?

I don't know the reason why that function doesn't support row-wise
comparison, but I don't think the main reason for that is that it
takes time to parse expressions.

> If the number of args in RowCompExpr is small, I would think that expanding it would improve performance.

Yeah, I think it's great to support row-wise comparison not only with
the small number of args but with the large number of them.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON


Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
Fujita san

On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <[hidden email]> wrote:
> Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent to the
> condition c1 < 99 and c2 < 99 (see the documentation note in [1]).

Thanks for sharing this document. I have understood.

> but I don't think the main reason for that is that it takes time to parse
> expressions.
> Yeah, I think it's great to support row-wise comparison not only with the small
> number of args but with the large number of them.

These comments are very helpful.
Ok, I try to make POC that allows row-wise comparison with partition-pruning.

Regards,
sho kato

> -----Original Message-----
> From: Etsuro Fujita <[hidden email]>
> Sent: Tuesday, July 7, 2020 6:31 PM
> To: Kato, Sho/加藤 翔 <[hidden email]>
> Cc: PostgreSQL-development <[hidden email]>
> Subject: Re: Performing partition pruning using row value
>
> Kato-san,
>
> On Mon, Jul 6, 2020 at 5:25 PM [hidden email] <[hidden email]>
> wrote:
> > I would like to ask about the conditions under which partition pruning is
> performed.
> > In PostgreSQL 12, when I executed following SQL, partition pruning is not
> performed.
> >
> > postgres=# explain select * from a where (c1, c2) < (99, 99);
> >                            QUERY PLAN
> > ----------------------------------------------------------------
> >  Append  (cost=0.00..60.00 rows=800 width=40)
> >    ->  Seq Scan on a1 a_1  (cost=0.00..28.00 rows=400 width=40)
> >          Filter: (ROW(c1, c2) < ROW(99, 99))
> >    ->  Seq Scan on a2 a_2  (cost=0.00..28.00 rows=400 width=40)
> >          Filter: (ROW(c1, c2) < ROW(99, 99))
> > (5 rows)
> >
> > However, pruning is performed when I changed the SQL as follows.
> >
> > postgres=# explain select * from a where c1  < 99 and c2 < 99;
> >                        QUERY PLAN
> > --------------------------------------------------------
> >  Seq Scan on a1 a  (cost=0.00..28.00 rows=133 width=40)
> >    Filter: ((c1 < 99) AND (c2 < 99))
> > (2 rows)
>
> Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent to the
> condition c1 < 99 and c2 < 99 (see the documentation note in [1]).
>
> > Looking at the code, "(c1, c2) < (99, 99)" is recognized as RowCompExpr and
> "c1 < 99 and c2 < 99" is recognized combination of OpExpr.
> >
> > Currently, pruning is not performed for RowCompExpr, is this correct?
>
> Yeah, I think so.
>
> > Because it would take a long time to parse all Expr nodes, does
> match_cluause_to_partition_key() return PART_CLAUSE_UNSUPPORTED
> when such Expr node is passed?
>
> I don't know the reason why that function doesn't support row-wise comparison,
> but I don't think the main reason for that is that it takes time to parse
> expressions.
>
> > If the number of args in RowCompExpr is small, I would think that expanding
> it would improve performance.
>
> Yeah, I think it's great to support row-wise comparison not only with the small
> number of args but with the large number of them.
>
> Best regards,
> Etsuro Fujita
>
> [1]
> https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-
> WISE-COMPARISON
Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Amit Langote
Kato-san,

On Wed, Jul 8, 2020 at 10:32 AM [hidden email]
<[hidden email]> wrote:
> On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <[hidden email]> wrote:
> > Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent to the
> > condition c1 < 99 and c2 < 99 (see the documentation note in [1]).
>
> Thanks for sharing this document. I have understood.
>
> > but I don't think the main reason for that is that it takes time to parse
> > expressions.

I think the only reason that this is not supported is that I hadn't
tested such a query when developing partition pruning, nor did anyone
else suggest doing so. :)

> > Yeah, I think it's great to support row-wise comparison not only with the small
> > number of args but with the large number of them.

+1

> These comments are very helpful.
> Ok, I try to make POC that allows row-wise comparison with partition-pruning.

That would be great, thank you.

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
Amit-san

On Wednesday, July 8, 2020 11:53 AM, Amit Langote <[hidden email]>:
> I think the only reason that this is not supported is that I hadn't tested such a
> query when developing partition pruning, nor did anyone else suggest doing
> so. :)

Thanks for the information. I'm relieved to hear this reason.

Regards,
Sho kato

> -----Original Message-----
> From: Amit Langote <[hidden email]>
> Sent: Wednesday, July 8, 2020 11:53 AM
> To: Kato, Sho/加藤 翔 <[hidden email]>
> Cc: Etsuro Fujita <[hidden email]>; PostgreSQL-development
> <[hidden email]>
> Subject: Re: Performing partition pruning using row value
>
> Kato-san,
>
> On Wed, Jul 8, 2020 at 10:32 AM [hidden email]
> <[hidden email]> wrote:
> > On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <[hidden email]>
> wrote:
> > > Just to be clear, the condition (c1, c2) < (99, 99) is not
> > > equivalent to the condition c1 < 99 and c2 < 99 (see the documentation
> note in [1]).
> >
> > Thanks for sharing this document. I have understood.
> >
> > > but I don't think the main reason for that is that it takes time to
> > > parse expressions.
>
> I think the only reason that this is not supported is that I hadn't tested such a
> query when developing partition pruning, nor did anyone else suggest doing
> so. :)
>
> > > Yeah, I think it's great to support row-wise comparison not only
> > > with the small number of args but with the large number of them.
>
> +1
>
> > These comments are very helpful.
> > Ok, I try to make POC that allows row-wise comparison with
> partition-pruning.
>
> That would be great, thank you.
>
> --
> Amit Langote
> EnterpriseDB: http://www.enterprisedb.com
Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Fujii Masao-4


On 2020/07/08 13:25, [hidden email] wrote:
> Amit-san
>
> On Wednesday, July 8, 2020 11:53 AM, Amit Langote <[hidden email]>:
>> I think the only reason that this is not supported is that I hadn't tested such a
>> query when developing partition pruning, nor did anyone else suggest doing
>> so. :)

Seems we can do partition pruning even in Kato-san's case by dong

create type hoge as (c1 int, c2 int);
create table a( c1 int, c2 int, c3 varchar) partition by range(((c1, c2)::hoge));
create table a1 partition of a for values from((0, 0)) to ((100, 100));
create table a2 partition of a for values from((100, 100)) to ((200, 200));
explain select * from a where (c1, c2)::hoge < (99, 99)::hoge;

I'm not sure if this method is officially supported or not, though...

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION


Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
Fujii-san

Wednesday, July 8, 2020 3:20 PM, Fujii Masao <[hidden email]> wrote:
> Seems we can do partition pruning even in Kato-san's case by dong
>
> create type hoge as (c1 int, c2 int);
> create table a( c1 int, c2 int, c3 varchar) partition by range(((c1, c2)::hoge));
> create table a1 partition of a for values from((0, 0)) to ((100, 100)); create table
> a2 partition of a for values from((100, 100)) to ((200, 200)); explain select * from
> a where (c1, c2)::hoge < (99, 99)::hoge;

I hadn't thought of it that way. Thanks.

Regards,
Sho kato

> -----Original Message-----
> From: Fujii Masao <[hidden email]>
> Sent: Wednesday, July 8, 2020 3:20 PM
> To: Kato, Sho/加藤 翔 <[hidden email]>; 'Amit Langote'
> <[hidden email]>
> Cc: Etsuro Fujita <[hidden email]>; PostgreSQL-development
> <[hidden email]>
> Subject: Re: Performing partition pruning using row value
>
>
>
> On 2020/07/08 13:25, [hidden email] wrote:
> > Amit-san
> >
> > On Wednesday, July 8, 2020 11:53 AM, Amit Langote
> <[hidden email]>:
> >> I think the only reason that this is not supported is that I hadn't
> >> tested such a query when developing partition pruning, nor did anyone
> >> else suggest doing so. :)
>
> Seems we can do partition pruning even in Kato-san's case by dong
>
> create type hoge as (c1 int, c2 int);
> create table a( c1 int, c2 int, c3 varchar) partition by range(((c1, c2)::hoge));
> create table a1 partition of a for values from((0, 0)) to ((100, 100)); create table
> a2 partition of a for values from((100, 100)) to ((200, 200)); explain select * from
> a where (c1, c2)::hoge < (99, 99)::hoge;
>
> I'm not sure if this method is officially supported or not, though...
>
> Regards,
>
> --
> Fujii Masao
> Advanced Computing Technology Center
> Research and Development Headquarters
> NTT DATA CORPORATION
Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
In reply to this post by kato-sho@fujitsu.com
Hi,

I made a patch that enable partition pruning using row-wise comparison.
Please review and comment on this patch.

regards,
sho kato

> -----Original Message-----
> From: [hidden email] <[hidden email]>
> Sent: Wednesday, July 8, 2020 10:33 AM
> To: 'Etsuro Fujita' <[hidden email]>
> Cc: PostgreSQL-development <[hidden email]>
> Subject: RE: Performing partition pruning using row value
>
> Fujita san
>
> On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <[hidden email]>
> wrote:
> > Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent
> > to the condition c1 < 99 and c2 < 99 (see the documentation note in [1]).
>
> Thanks for sharing this document. I have understood.
>
> > but I don't think the main reason for that is that it takes time to
> > parse expressions.
> > Yeah, I think it's great to support row-wise comparison not only with
> > the small number of args but with the large number of them.
>
> These comments are very helpful.
> Ok, I try to make POC that allows row-wise comparison with partition-pruning.
>
> Regards,
> sho kato
> > -----Original Message-----
> > From: Etsuro Fujita <[hidden email]>
> > Sent: Tuesday, July 7, 2020 6:31 PM
> > To: Kato, Sho/加藤 翔 <[hidden email]>
> > Cc: PostgreSQL-development <[hidden email]>
> > Subject: Re: Performing partition pruning using row value
> >
> > Kato-san,
> >
> > On Mon, Jul 6, 2020 at 5:25 PM [hidden email]
> > <[hidden email]>
> > wrote:
> > > I would like to ask about the conditions under which partition
> > > pruning is
> > performed.
> > > In PostgreSQL 12, when I executed following SQL, partition pruning
> > > is not
> > performed.
> > >
> > > postgres=# explain select * from a where (c1, c2) < (99, 99);
> > >                            QUERY PLAN
> > > ----------------------------------------------------------------
> > >  Append  (cost=0.00..60.00 rows=800 width=40)
> > >    ->  Seq Scan on a1 a_1  (cost=0.00..28.00 rows=400 width=40)
> > >          Filter: (ROW(c1, c2) < ROW(99, 99))
> > >    ->  Seq Scan on a2 a_2  (cost=0.00..28.00 rows=400 width=40)
> > >          Filter: (ROW(c1, c2) < ROW(99, 99))
> > > (5 rows)
> > >
> > > However, pruning is performed when I changed the SQL as follows.
> > >
> > > postgres=# explain select * from a where c1  < 99 and c2 < 99;
> > >                        QUERY PLAN
> > > --------------------------------------------------------
> > >  Seq Scan on a1 a  (cost=0.00..28.00 rows=133 width=40)
> > >    Filter: ((c1 < 99) AND (c2 < 99))
> > > (2 rows)
> >
> > Just to be clear, the condition (c1, c2) < (99, 99) is not equivalent
> > to the condition c1 < 99 and c2 < 99 (see the documentation note in [1]).
> >
> > > Looking at the code, "(c1, c2) < (99, 99)" is recognized as
> > > RowCompExpr and
> > "c1 < 99 and c2 < 99" is recognized combination of OpExpr.
> > >
> > > Currently, pruning is not performed for RowCompExpr, is this correct?
> >
> > Yeah, I think so.
> >
> > > Because it would take a long time to parse all Expr nodes, does
> > match_cluause_to_partition_key() return PART_CLAUSE_UNSUPPORTED
> when
> > such Expr node is passed?
> >
> > I don't know the reason why that function doesn't support row-wise
> > comparison, but I don't think the main reason for that is that it
> > takes time to parse expressions.
> >
> > > If the number of args in RowCompExpr is small, I would think that
> > > expanding
> > it would improve performance.
> >
> > Yeah, I think it's great to support row-wise comparison not only with
> > the small number of args but with the large number of them.
> >
> > Best regards,
> > Etsuro Fujita
> >
> > [1]
> > https://www.postgresql.org/docs/current/functions-comparisons.html#ROW
> > -
> > WISE-COMPARISON

pruning-with-row-wise-comparison.patch (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Etsuro Fujita-2
Kato-san,

On Thu, Jul 9, 2020 at 5:43 PM [hidden email]
<[hidden email]> wrote:
> I made a patch that enable partition pruning using row-wise comparison.
> Please review and comment on this patch.

Please add the patch to the next CF so that it does not get lost.

Thanks!

Best regards,
Etsuro Fujita


Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Fujii Masao-4


On 2020/07/09 19:45, Etsuro Fujita wrote:
> Kato-san,
>
> On Thu, Jul 9, 2020 at 5:43 PM [hidden email]
> <[hidden email]> wrote:
>> I made a patch that enable partition pruning using row-wise comparison.
>> Please review and comment on this patch.

Thanks for the patch!


> Please add the patch to the next CF so that it does not get lost.

Is this a bug rather than new feature?


Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION


Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Etsuro Fujita-2
Fujii-san,

On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao <[hidden email]> wrote:
> On 2020/07/09 19:45, Etsuro Fujita wrote:
> > Please add the patch to the next CF so that it does not get lost.
>
> Is this a bug rather than new feature?

I think it's a limitation rather than a bug that partition pruning
doesn't support row-wise comparison, so I think the patch is a new
feature.

Best regards,
Etsuro Fujita


Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Amit Langote
On Fri, Jul 10, 2020 at 9:35 AM Etsuro Fujita <[hidden email]> wrote:
> On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao <[hidden email]> wrote:
> > On 2020/07/09 19:45, Etsuro Fujita wrote:
> > > Please add the patch to the next CF so that it does not get lost.
> >
> > Is this a bug rather than new feature?
>
> I think it's a limitation rather than a bug that partition pruning
> doesn't support row-wise comparison, so I think the patch is a new
> feature.

I tend to think so too.  IMO, partition pruning, like any other
optimization, works on a best-effort basis.  If the result it produces
is wrong, now that would be a bug, but I don't think that's the case
here.  However, I do think it was a bit unfortunate that we failed to
consider RowCompare expressions when developing partition pruning
given, that index scans are already able to match them.

Speaking of which, I hope that Kato-san has looked at functions
match_rowcompare_to_indexcol(), expand_indexqual_rowcompare(), etc. in
indxpath.c as starting points for the code to match RowCompares to
partition keys.

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com


Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
Amit-san
Friday, July 10, 2020 10:00 AM, Amit Langote <[hidden email]> wrote:
>Speaking of which, I hope that Kato-san has looked at functions match_rowcompare_to_indexcol(), expand_indexqual_rowcompare(), etc. in indxpath.c as starting points >for the code to match RowCompares to partition keys.

Hmm, I did not look at these functions. So, after looking at these functions and modifying this patch, I would like to add this patch to the next CF.
thanks for providing this information.

regards,
sho kato
Reply | Threaded
Open this post in threaded view
|

RE: Performing partition pruning using row value

kato-sho@fujitsu.com
>So, after looking at these functions and modifying this patch, I would like to add this patch to the next

I updated this patch and registered for the next CF .

https://commitfest.postgresql.org/29/2654/

regards,
sho kato

pruning-with-row-wise-comparison-v2.patch (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Ahsan Hadi-2
The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            not tested

I have performed testing of the patch with row comparison partition pruning scenarios, it is working well. I didn't code review hence not changing the status.
Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

Anastasia Lubennikova
In reply to this post by kato-sho@fujitsu.com
On 21.07.2020 11:24, [hidden email] wrote:
>> So, after looking at these functions and modifying this patch, I would like to add this patch to the next
> I updated this patch and registered for the next CF .
>
> https://commitfest.postgresql.org/29/2654/
>
> regards,
> sho kato

Thank you for working on this improvement. I took a look at the code.

1) This piece of code is unneeded:

             switch (get_op_opfamily_strategy(opno, partopfamily))
             {
                 case BTLessStrategyNumber:
                 case BTLessEqualStrategyNumber:
                 case BTGreaterEqualStrategyNumber:
                 case BTGreaterStrategyNumber:

See the comment for RowCompareExpr, which states that "A RowCompareExpr
node is only generated for the < <= > >= cases".

2) It's worth to add a regression test for this feature.

Other than that, the patch looks good to me.

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company



Reply | Threaded
Open this post in threaded view
|

Re: Performing partition pruning using row value

David Steele
On 2/16/21 9:07 AM, Anastasia Lubennikova wrote:

> On 21.07.2020 11:24, [hidden email] wrote:
>>> So, after looking at these functions and modifying this patch, I
>>> would like to add this patch to the next
>> I updated this patch and registered for the next CF .
>>
>> https://commitfest.postgresql.org/29/2654/
>>
>> regards,
>> sho kato
>
> Thank you for working on this improvement. I took a look at the code.
>
> 1) This piece of code is unneeded:
>
>              switch (get_op_opfamily_strategy(opno, partopfamily))
>              {
>                  case BTLessStrategyNumber:
>                  case BTLessEqualStrategyNumber:
>                  case BTGreaterEqualStrategyNumber:
>                  case BTGreaterStrategyNumber:
>
> See the comment for RowCompareExpr, which states that "A RowCompareExpr
> node is only generated for the < <= > >= cases".
>
> 2) It's worth to add a regression test for this feature.
>
> Other than that, the patch looks good to me.

This patch has been Waiting on Author for several months, so marking
Returned with Feedback.

Please resubmit to the next CF when you have a new patch.

Regards,
--
-David
[hidden email]