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
From 2a25202f2f0a415c6884c28fd5bc76243507d5c4 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Fri, 8 Dec 2017 19:09:31 +0900
Subject: [PATCH] Teach operator_predicate_proof() to strip RelabelType
---
src/backend/optimizer/util/predtest.c | 13 ++++++++----
src/test/regress/expected/partition_prune.out | 29 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 8 ++++++++
3 files changed, 46 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/util/predtest.c
b/src/backend/optimizer/util/predtest.c
index 134460cc13..d0f6278984 100644
--- a/src/backend/optimizer/util/predtest.c
+++ b/src/backend/optimizer/util/predtest.c
@@ -1407,6 +1407,11 @@ static const StrategyNumber BT_refute_table[6][6] = {
{none, none, BTEQ, none, none, none} /* NE */
};
+/* Strip expr of the surrounding RelabelType, if any. */
+#define strip_relabel(expr) \
+ ((Node *) (IsA((expr), RelabelType) \
+ ? ((RelabelType *) (expr))->arg \
+ : (expr)))
/*
* operator_predicate_proof
@@ -1503,10 +1508,10 @@ operator_predicate_proof(Expr *predicate, Node *clause,
bool refute_it)
/*
* We have to match up at least one pair of input expressions.
*/
- pred_leftop = (Node *) linitial(pred_opexpr->args);
- pred_rightop = (Node *) lsecond(pred_opexpr->args);
- clause_leftop = (Node *) linitial(clause_opexpr->args);
- clause_rightop = (Node *) lsecond(clause_opexpr->args);
+ pred_leftop = strip_relabel(linitial(pred_opexpr->args));
+ pred_rightop = strip_relabel(lsecond(pred_opexpr->args));
+ clause_leftop = strip_relabel(linitial(clause_opexpr->args));
+ clause_rightop = strip_relabel(lsecond(clause_opexpr->args));
if (equal(pred_leftop, clause_leftop))
{
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index aabb0240a9..7c78f11d55 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1092,4 +1092,33 @@ explain (costs off) select * from boolpart where a is
not unknown;
Filter: (a IS NOT UNKNOWN)
(7 rows)
+-- array type list partition key
+create table arrpart (a int[]) partition by list (a);
+create table arrpart1 partition of arrpart for values in ('{1}');
+create table arrpart2 partition of arrpart for values in ('{2, 3}', '{4, 5}');
+explain (costs off) select * from arrpart where a = '{1}';
+ QUERY PLAN
+----------------------------------------
+ Append
+ -> Seq Scan on arrpart1
+ Filter: (a = '{1}'::integer[])
+(3 rows)
+
+explain (costs off) select * from arrpart where a = '{1, 2}';
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from arrpart where a in ('{4, 5}', '{1}');
+ QUERY PLAN
+----------------------------------------------------------------------
+ Append
+ -> Seq Scan on arrpart1
+ Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
+ -> Seq Scan on arrpart2
+ Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
+(5 rows)
+
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index 514f8e5ce1..c353387bb9 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -152,4 +152,12 @@ explain (costs off) select * from boolpart where a is not
true and a is not fals
explain (costs off) select * from boolpart where a is unknown;
explain (costs off) select * from boolpart where a is not unknown;
+-- array type list partition key
+create table arrpart (a int[]) partition by list (a);
+create table arrpart1 partition of arrpart for values in ('{1}');
+create table arrpart2 partition of arrpart for values in ('{2, 3}', '{4, 5}');
+explain (costs off) select * from arrpart where a = '{1}';
+explain (costs off) select * from arrpart where a = '{1, 2}';
+explain (costs off) select * from arrpart where a in ('{4, 5}', '{1}');
+
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart;
--
2.11.0
