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