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