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