Re: Performing partition pruning using row value

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

Kato-san,

On Mon, Jul 6, 2020 at 5:25 PM kato-sho(at)fujitsu(dot)com
<kato-sho(at)fujitsu(dot)com> 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

Fujita san

On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> 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 <etsuro(dot)fujita(at)gmail(dot)com>
> Sent: Tuesday, July 7, 2020 6:31 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)fujitsu(dot)com>
> Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: Performing partition pruning using row value
>
> Kato-san,
>
> On Mon, Jul 6, 2020 at 5:25 PM kato-sho(at)fujitsu(dot)com <kato-sho(at)fujitsu(dot)com>
> 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

Kato-san,

On Wed, Jul 8, 2020 at 10:32 AM kato-sho(at)fujitsu(dot)com
<kato-sho(at)fujitsu(dot)com> wrote:
> On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> 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

Amit-san

On Wednesday, July 8, 2020 11:53 AM, Amit Langote <amitlangote09(at)gmail(dot)com>:
> 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 <amitlangote09(at)gmail(dot)com>
> Sent: Wednesday, July 8, 2020 11:53 AM
> To: Kato, Sho/加藤 翔 <kato-sho(at)fujitsu(dot)com>
> Cc: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>; PostgreSQL-development
> <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: Performing partition pruning using row value
>
> Kato-san,
>
> On Wed, Jul 8, 2020 at 10:32 AM kato-sho(at)fujitsu(dot)com
> <kato-sho(at)fujitsu(dot)com> wrote:
> > On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> 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

On 2020/07/08 13:25, kato-sho(at)fujitsu(dot)com wrote:
> Amit-san
>
> On Wednesday, July 8, 2020 11:53 AM, Amit Langote <amitlangote09(at)gmail(dot)com>:
>> 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

Fujii-san

Wednesday, July 8, 2020 3:20 PM, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> 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 <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
> Sent: Wednesday, July 8, 2020 3:20 PM
> To: Kato, Sho/加藤 翔 <kato-sho(at)fujitsu(dot)com>; 'Amit Langote'
> <amitlangote09(at)gmail(dot)com>
> Cc: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>; PostgreSQL-development
> <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: Performing partition pruning using row value
>
>
>
> On 2020/07/08 13:25, kato-sho(at)fujitsu(dot)com wrote:
> > Amit-san
> >
> > On Wednesday, July 8, 2020 11:53 AM, Amit Langote
> <amitlangote09(at)gmail(dot)com>:
> >> 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

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: kato-sho(at)fujitsu(dot)com <kato-sho(at)fujitsu(dot)com>
> Sent: Wednesday, July 8, 2020 10:33 AM
> To: 'Etsuro Fujita' <etsuro(dot)fujita(at)gmail(dot)com>
> Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
> Subject: RE: Performing partition pruning using row value
>
> Fujita san
>
> On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> 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 <etsuro(dot)fujita(at)gmail(dot)com>
> > Sent: Tuesday, July 7, 2020 6:31 PM
> > To: Kato, Sho/加藤 翔 <kato-sho(at)fujitsu(dot)com>
> > Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
> > Subject: Re: Performing partition pruning using row value
> >
> > Kato-san,
> >
> > On Mon, Jul 6, 2020 at 5:25 PM kato-sho(at)fujitsu(dot)com
> > <kato-sho(at)fujitsu(dot)com>
> > 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

Kato-san,

On Thu, Jul 9, 2020 at 5:43 PM kato-sho(at)fujitsu(dot)com
<kato-sho(at)fujitsu(dot)com> 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

On 2020/07/09 19:45, Etsuro Fujita wrote:
> Kato-san,
>
> On Thu, Jul 9, 2020 at 5:43 PM kato-sho(at)fujitsu(dot)com
> <kato-sho(at)fujitsu(dot)com> 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

Fujii-san,

On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> 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

On Fri, Jul 10, 2020 at 9:35 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> 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

Amit-san
Friday, July 10, 2020 10:00 AM, Amit Langote <amitlangote09(at)gmail(dot)com> 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

>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

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.

On 21.07.2020 11:24, kato-sho(at)fujitsu(dot)com 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

On 2/16/21 9:07 AM, Anastasia Lubennikova wrote:
> On 21.07.2020 11:24, kato-sho(at)fujitsu(dot)com 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
david(at)pgmasters(dot)net