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
