From: | "kato-sho(at)fujitsu(dot)com" <kato-sho(at)fujitsu(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Performing partition pruning using row value |
Date: | 2020-07-06 08:25:37 |
Message-ID: | OSBPR01MB5126B798138635061D63355A9F690@OSBPR01MB5126.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Georgios Kokolatos | 2020-07-06 08:34:56 | Re: Using Valgrind to detect faulty buffer accesses (no pin or buffer content lock held) |
Previous Message | Pavel Stehule | 2020-07-06 08:17:07 | Re: proposal: schema variables |