Re: Performing partition pruning using row value
On 2/16/21 9:07 AM, Anastasia Lubennikova wrote:
On 21.07.2020 11:24, kato-...@fujitsu.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
da...@pgmasters.net
Re: Performing partition pruning using row value
On 21.07.2020 11:24, kato-...@fujitsu.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
Re: Performing partition pruning using row value
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.
RE: Performing partition pruning using row value
>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 Description: pruning-with-row-wise-comparison-v2.patch
RE: Performing partition pruning using row value
Amit-san Friday, July 10, 2020 10:00 AM, Amit Langote 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
Re: Performing partition pruning using row value
On Fri, Jul 10, 2020 at 9:35 AM Etsuro Fujita wrote: > On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao > 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
Re: Performing partition pruning using row value
Fujii-san, On Thu, Jul 9, 2020 at 7:57 PM Fujii Masao 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
Re: Performing partition pruning using row value
On 2020/07/09 19:45, Etsuro Fujita wrote: Kato-san, On Thu, Jul 9, 2020 at 5:43 PM kato-...@fujitsu.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
Re: Performing partition pruning using row value
Kato-san, On Thu, Jul 9, 2020 at 5:43 PM kato-...@fujitsu.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
RE: Performing partition pruning using row value
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-...@fujitsu.com > Sent: Wednesday, July 8, 2020 10:33 AM > To: 'Etsuro Fujita' > Cc: PostgreSQL-development > Subject: RE: Performing partition pruning using row value > > Fujita san > > On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita > 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 > > Sent: Tuesday, July 7, 2020 6:31 PM > > To: Kato, Sho/加藤 翔 > > Cc: PostgreSQL-development > > Subject: Re: Performing partition pruning using row value > > > > Kato-san, > > > > On Mon, Jul 6, 2020 at 5:25 PM kato-...@fujitsu.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 pruning-with-row-wise-comparison.patch Description: pruning-with-row-wise-comparison.patch
RE: Performing partition pruning using row value
Fujii-san Wednesday, July 8, 2020 3:20 PM, Fujii Masao 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 > Sent: Wednesday, July 8, 2020 3:20 PM > To: Kato, Sho/加藤 翔 ; 'Amit Langote' > > Cc: Etsuro Fujita ; PostgreSQL-development > > Subject: Re: Performing partition pruning using row value > > > > On 2020/07/08 13:25, kato-...@fujitsu.com wrote: > > Amit-san > > > > On Wednesday, July 8, 2020 11:53 AM, Amit Langote > : > >> 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
Re: Performing partition pruning using row value
On 2020/07/08 13:25, kato-...@fujitsu.com wrote: Amit-san On Wednesday, July 8, 2020 11:53 AM, Amit Langote : 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
RE: Performing partition pruning using row value
Amit-san On Wednesday, July 8, 2020 11:53 AM, Amit Langote : > 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 > Sent: Wednesday, July 8, 2020 11:53 AM > To: Kato, Sho/加藤 翔 > Cc: Etsuro Fujita ; PostgreSQL-development > > Subject: Re: Performing partition pruning using row value > > Kato-san, > > On Wed, Jul 8, 2020 at 10:32 AM kato-...@fujitsu.com > wrote: > > On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita > 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
Re: Performing partition pruning using row value
Kato-san, On Wed, Jul 8, 2020 at 10:32 AM kato-...@fujitsu.com wrote: > On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita > 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
RE: Performing partition pruning using row value
Fujita san On Tuesday, July 7, 2020 6:31 PM Etsuro Fujita 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 > Sent: Tuesday, July 7, 2020 6:31 PM > To: Kato, Sho/加藤 翔 > Cc: PostgreSQL-development > Subject: Re: Performing partition pruning using row value > > Kato-san, > > On Mon, Jul 6, 2020 at 5:25 PM kato-...@fujitsu.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
Re: Performing partition pruning using row value
Kato-san, On Mon, Jul 6, 2020 at 5:25 PM kato-...@fujitsu.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
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
