Hello
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)
These tables are defined as follows.
create table a( c1 int, c2 int, c3 varchar) partition by range(c1, c2);
create table a1 partition of a for values from(0, 0) to (100, 100);
create table a2 partition of a for values from(100, 100) to (200, 200);
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?
Also, at the end of match_clause_to_partition_key(), the following Comments
like.
"Since the qual didn't match up to any of the other qual types supported here,
then trying to match it against any other partition key is a waste of time, so
just return PARTCLAUSE_UNSUPPORTED."
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?
If the number of args in RowCompExpr is small, I would think that expanding it
would improve performance.
regards,
sho kato
