From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | no partition pruning when partitioning using array type |
Date: | 2017-12-08 10:40:35 |
Message-ID: | 54745d13-7ed4-54ac-97d8-ea1eec95ae25@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
I noticed that if you partition using a array type column, partition
pruning using constraint exclusion fails to work due to a minor problem.
Example:
create table p (a int[]) partition by list (a);
create table p1 partition of p for values in ('{1}');
create table p1 partition of p for values in ('{2, 3}', '{4, 5}');
explain select a from p where a = '{1}';
QUERY PLAN
|---------------------------------------------------------
Append (cost=0.00..54.00 rows=14 width=32)
-> Seq Scan on p1 (cost=0.00..27.00 rows=7 width=32)
Filter: (a = '{1}'::integer[])
-> Seq Scan on p2 (cost=0.00..27.00 rows=7 width=32)
Filter: (a = '{1}'::integer[])
explain select a from p where a = '{2, 3}';
QUERY PLAN
|---------------------------------------------------------
Append (cost=0.00..54.00 rows=14 width=32)
-> Seq Scan on p1 (cost=0.00..27.00 rows=7 width=32)
Filter: (a = '{2,3}'::integer[])
-> Seq Scan on p2 (cost=0.00..27.00 rows=7 width=32)
Filter: (a = '{2,3}'::integer[])
(5 rows)
In the case of array type partition key, make_partition_op_expr() will
have to put a RelabelType node on top of the partition key Var, after
having selected an = operator from the array_ops family. The RelabelType
causes operator_predicate_proof() to fail to consider predicate leftop and
clause leftop as equal, because only one of them ends up having the
RelabelType attached to it.
As a simple measure, the attached patch teaches operator_predicate_proof()
to strip RelabelType nodes from all the nodes it compares using equal().
I also added a relevant test in partition_prune.sql.
Thoughts?
Thanks,
Amit
Attachment | Content-Type | Size |
---|---|---|
0001-Teach-operator_predicate_proof-to-strip-RelabelType.patch | text/plain | 4.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Everaldo Canuto | 2017-12-08 11:24:05 | Re: proposal: alternative psql commands quit and exit |
Previous Message | Amit Kapila | 2017-12-08 10:11:58 | Re: explain analyze output with parallel workers - question about meaning of information for explain.depesz.com |