Kato-san, On Mon, Jul 6, 2020 at 5:25 PM kato-...@fujitsu.com <kato-...@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
