Hi.
I noticed that the newly added pruning does not work if the partition key
is of one of the types that have a corresponding pseudo-type.
-- 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[])
-> Seq Scan on arrpart2
Filter: (a = '{1}'::integer[])
(5 rows)
For pruning, we normally rely on the type's operator class information in
the system catalogs to be up-to-date, which if it isn't we give up on
pruning. For example, if pg_amproc entry for a given type and AM type
(btree, hash, etc.) has not been populated, we may fail to prune using a
clause that contains an expression of the said type. While this is the
theory for the normal cases, we should make an exception for the
pseudo-type types. For those types, we never have pg_amproc entries with
the "real" type listed. Instead, the pg_amproc entries contain the
corresponding pseudo-type. For example, there aren't pg_amproc entries
with int4[] (really, its OID) as amproclefttype and/or amprocrighttype,
instead anyarray is listed there.
Attached find a patch that tries to fix that and adds relevant tests.
Thanks,
Amit
From c7945da855973b606b5aa012295e2c0ae93c39c5 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Fri, 8 Dec 2017 19:09:31 +0900
Subject: [PATCH v1] Fix pruning when partition key of array, enum, record type
We never have pg_amproc catalog entries with "real" array, enum, record,
range types as leftop and rightop types. Instead, AM procedures
manipulating such types have entries with the corresponding "pseudo-types"
listed as leftop and rightop types. For example, for enums, all
procedures entries are marked with anyenum as their leftop and rightop
types. So, if we pass "real" type OIDs to get_opfamily_member() or
get_opfamily_proc(), we get back an InvalidOid for these type categories.
Whereas we'd normally give up on performing pruning in that case, don't
do that in this case.
---
src/backend/partitioning/partprune.c | 4 +-
src/test/regress/expected/partition_prune.out | 98 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 44 +++++++++++-
3 files changed, 144 insertions(+), 2 deletions(-)
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 417e1fee81..bd1b99102d 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -1519,7 +1519,9 @@ match_clause_to_partition_key(RelOptInfo *rel,
/* Check if we're going to need a cross-type comparison
function. */
exprtype = exprType((Node *) expr);
- if (exprtype != part_scheme->partopcintype[partkeyidx])
+ if (exprtype != part_scheme->partopcintype[partkeyidx] &&
+ get_typtype(part_scheme->partopcintype[partkeyidx]) !=
+ TYPTYPE_PSEUDO)
{
switch (part_scheme->strategy)
{
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index df3fca025e..69e679d930 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2399,3 +2399,101 @@ select * from boolp where a = (select value from
boolvalues where not value);
drop table boolp;
reset enable_indexonlyscan;
+--
+-- check that pruning works properly when the partition key is of array, enum,
+-- or record type
+--
+-- 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 arrpart;
+-- enum type list partition key
+create type colors as enum ('green', 'blue', 'black');
+create table enumpart (a colors) partition by list (a);
+create table enumpart_green partition of enumpart for values in ('green');
+create table enumpart_blue partition of enumpart for values in ('blue');
+explain (costs off) select * from enumpart where a = 'blue';
+ QUERY PLAN
+--------------------------------------
+ Append
+ -> Seq Scan on enumpart_blue
+ Filter: (a = 'blue'::colors)
+(3 rows)
+
+explain (costs off) select * from enumpart where a = 'black';
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+drop table enumpart;
+drop type colors;
+-- record type as partition key
+create type rectype as (a int, b int);
+create table recpart (a rectype) partition by list (a);
+create table recpart_11 partition of recpart for values in ('(1,1)');
+create table recpart_23 partition of recpart for values in ('(2,3)');
+explain (costs off) select * from recpart where a = '(1,1)'::rectype;
+ QUERY PLAN
+----------------------------------------
+ Append
+ -> Seq Scan on recpart_11
+ Filter: (a = '(1,1)'::rectype)
+(3 rows)
+
+explain (costs off) select * from recpart where a = '(1,2)'::rectype;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+drop table recpart;
+drop type rectype;
+-- range type partition key
+create table intrangepart (a int4range) partition by list (a);
+create table intrangepart12 partition of intrangepart for values in ('[1,2]');
+create table intrangepart2inf partition of intrangepart for values in ('[2,)');
+explain (costs off) select * from intrangepart where a = '[1,2]'::int4range;
+ QUERY PLAN
+------------------------------------------
+ Append
+ -> Seq Scan on intrangepart12
+ Filter: (a = '[1,3)'::int4range)
+(3 rows)
+
+explain (costs off) select * from intrangepart where a = '(1,2)'::int4range;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+drop table intrangepart;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index 7fe93bbc04..587dfb9baf 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -587,4 +587,46 @@ select * from boolp where a = (select value from
boolvalues where not value);
drop table boolp;
-reset enable_indexonlyscan;
\ No newline at end of file
+reset enable_indexonlyscan;
+
+--
+-- check that pruning works properly when the partition key is of array, enum,
+-- or record type
+--
+
+-- 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 arrpart;
+
+-- enum type list partition key
+create type colors as enum ('green', 'blue', 'black');
+create table enumpart (a colors) partition by list (a);
+create table enumpart_green partition of enumpart for values in ('green');
+create table enumpart_blue partition of enumpart for values in ('blue');
+explain (costs off) select * from enumpart where a = 'blue';
+explain (costs off) select * from enumpart where a = 'black';
+drop table enumpart;
+drop type colors;
+
+-- record type as partition key
+create type rectype as (a int, b int);
+create table recpart (a rectype) partition by list (a);
+create table recpart_11 partition of recpart for values in ('(1,1)');
+create table recpart_23 partition of recpart for values in ('(2,3)');
+explain (costs off) select * from recpart where a = '(1,1)'::rectype;
+explain (costs off) select * from recpart where a = '(1,2)'::rectype;
+drop table recpart;
+drop type rectype;
+
+-- range type partition key
+create table intrangepart (a int4range) partition by list (a);
+create table intrangepart12 partition of intrangepart for values in ('[1,2]');
+create table intrangepart2inf partition of intrangepart for values in ('[2,)');
+explain (costs off) select * from intrangepart where a = '[1,2]'::int4range;
+explain (costs off) select * from intrangepart where a = '(1,2)'::int4range;
+drop table intrangepart;
--
2.11.0
