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
combinationof 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_UNSUPPORTEDwhen 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