Performing partition pruning using row value

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

Responses

Browse pgsql-hackers by date

  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