RE: Performing partition pruning using row value - Mailing list pgsql-hackers

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