Thanks for the review.
On 2018/04/10 21:02, David Rowley wrote:
> On 10 April 2018 at 20:56, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote:
>> On 2018/04/10 13:27, Ashutosh Bapat wrote:
>>> On Mon, Apr 9, 2018 at 8:56 PM, Robert Haas <robertmh...@gmail.com> wrote:
>>>> CREATE OR REPLACE FUNCTION hashint4_noop(int4, int8) RETURNS int8 AS
>>>> $$SELECT coalesce($1,0)::int8$$ LANGUAGE sql IMMUTABLE;
>>>> CREATE OPERATOR CLASS test_int4_ops FOR TYPE int4 USING HASH AS
>>>> OPERATOR 1 = , FUNCTION 2 hashint4_noop(int4, int8);
>>>> CREATE TABLE mchash (a int, b text, c jsonb)
>>>> PARTITION BY HASH (a test_int4_ops, b test_text_ops);
>>
>> Thanks for the idea. I think it makes sense and also agree that alternate
>> outputs approach is not perfectly reliable and maintainable.
>>
>>> +1.
>>
>> Attached find a patch that rewrites hash partition pruning tests that
>> away. It creates two hash operator classes, one for int4 and another for
>> text type and uses them to create hash partitioned table to be used in the
>> tests, like done in the existing tests in hash_part.sql. Since that makes
>> tests (hopefully) reliably return the same result always, I no longer see
>> the need to keep them in a separate partition_prune_hash.sql. The
>> reasoning behind having the separate file was to keep the alternative
>> output file small as David explained in [1].
>> [1]
>> https://www.postgresql.org/message-id/CAKJS1f-SON_hAekqoV4_WQwJBtJ_rvvSe68jRNhuYcXqQ8PoQg%40mail.gmail.com
>
> I had a quick look, but I'm still confused about why a function like
> hash_uint32_extended() is susceptible to varying results depending on
> CPU endianness but hash_combine64 is not.
It might as well be the combination of both that's sensitive to
endianness. I too am not sure exactly which part. They're are both used
in succession in compute_hash_value:
/*
* Compute hash for each datum value by calling respective
* datatype-specific hash functions of each partition key
* attribute.
*/
hash = FunctionCall2(&partsupfunc[i], values[i], seed);
/* Form a single 64-bit hash value */
rowHash = hash_combine64(rowHash, DatumGetUInt64(hash));
> Apart from that confusion, looking at the patch:
>
> +CREATE OR REPLACE FUNCTION pp_hashint4_noop(int4, int8) RETURNS int8 AS
> +$$SELECT coalesce($1)::int8$$ LANGUAGE sql IMMUTABLE STRICT;
> +CREATE OPERATOR CLASS pp_test_int4_ops FOR TYPE int4 USING HASH AS
> +OPERATOR 1 = , FUNCTION 2 pp_hashint4_noop(int4, int8);
> +CREATE OR REPLACE FUNCTION pp_hashtext_length(text, int8) RETURNS int8 AS
> +$$SELECT length(coalesce($1))::int8$$ LANGUAGE sql IMMUTABLE STRICT;
>
>
> Why coalesce here? Maybe I've not thought of something, but coalesce
> only seems useful to me if there's > 1 argument. Plus the function is
> strict, so not sure it's really doing even if you added a default.
After reading Ashutosh's comment, I realized I didn't really mean to add
the STRICT to those function definitions. As these are not operators, but
support (hash) procedures, it's insignificant to the pruning code whether
they are STRICT or not, unlike clause operators where it is.
Also, I've adopted the coalesce-based hashing function from hash_part.sql,
albeit with unnecessary tweaks. I've not read anywhere about why the
coalesce was used in the first place, but it's insignificant for our
purpose here anyway.
> I know this one was there before, but I only just noticed it:
>
> +-- pruning should work if non-null values are provided for all the keys
> +explain (costs off) select * from hp where a is null and b is null;
>
> The comment is a bit misleading given the first test below it is
> testing for nulls. Maybe it can be changed to
>
> +-- pruning should work if values or is null clauses are provided for
> all partition keys.
I have adjusted the comments.
Updated patch attached.
Thanks,
Amit
From 5a6d00d4d9d6aa8bb84dc9699646ee5c4fa77719 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 10 Apr 2018 16:06:33 +0900
Subject: [PATCH v2] Rewrite hash partition pruning tests to use custom opclass
Relying on platform-provided hashing functions makes tests unreliable
as shown by buildfarm recently.
This adds adjusted tests to partition_prune.sql itself and hence
partition_prune_hash.sql is deleted along with two expected output
files.
Discussion:
https://postgr.es/m/CA%2BTgmoZ0D5kJbt8eKXtvVdvTcGGWn6ehWCRSZbWytD-uzH92mQ%40mail.gmail.com
---
src/test/regress/expected/partition_prune.out | 201 +++++++++++++++++++++
src/test/regress/expected/partition_prune_hash.out | 189 -------------------
.../regress/expected/partition_prune_hash_1.out | 187 -------------------
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 -
src/test/regress/sql/partition_prune.sql | 58 +++++-
src/test/regress/sql/partition_prune_hash.sql | 41 -----
7 files changed, 259 insertions(+), 420 deletions(-)
delete mode 100644 src/test/regress/expected/partition_prune_hash.out
delete mode 100644 src/test/regress/expected/partition_prune_hash_1.out
delete mode 100644 src/test/regress/sql/partition_prune_hash.sql
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index df3fca025e..eb89a5eb67 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1332,6 +1332,207 @@ explain (costs off) select * from rparted_by_int2 where
a > 100000000000000;
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp,
coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
--
+-- Test Partition pruning for HASH partitioning
+-- We roll our own operator classes to use for tests, because depending on the
+-- platform-provided hashing functions makes tests unreliable
+--
+CREATE OR REPLACE FUNCTION pp_hashint4_noop(int4, int8) RETURNS int8 AS
+$$SELECT coalesce($1, $2)::int8$$ LANGUAGE sql IMMUTABLE;
+CREATE OPERATOR CLASS pp_test_int4_ops FOR TYPE int4 USING HASH AS
+OPERATOR 1 = , FUNCTION 2 pp_hashint4_noop(int4, int8);
+CREATE OR REPLACE FUNCTION pp_hashtext_length(text, int8) RETURNS int8 AS
+$$SELECT length(coalesce($1, ''))::int8$$ LANGUAGE sql IMMUTABLE;
+CREATE OPERATOR CLASS pp_test_text_ops FOR TYPE text USING HASH AS
+OPERATOR 1 = , FUNCTION 2 pp_hashtext_length(text, int8);
+create table hp (a int, b text) partition by hash (a pp_test_int4_ops, b
pp_test_text_ops);
+create table hp0 partition of hp for values with (modulus 4, remainder 0);
+create table hp3 partition of hp for values with (modulus 4, remainder 3);
+create table hp1 partition of hp for values with (modulus 4, remainder 1);
+create table hp2 partition of hp for values with (modulus 4, remainder 2);
+insert into hp values (null, null);
+insert into hp values (1, null);
+insert into hp values (1, 'xxx');
+insert into hp values (null, 'xxx');
+insert into hp values (2, 'xxx');
+insert into hp values (1, 'abcde');
+select tableoid::regclass, * from hp order by 1;
+ tableoid | a | b
+----------+---+-------
+ hp0 | |
+ hp0 | 1 |
+ hp3 | 1 | xxx
+ hp1 | 1 | abcde
+ hp2 | | xxx
+ hp2 | 2 | xxx
+(6 rows)
+
+-- partial keys won't prune, nor would non-equality conditions
+explain (costs off) select * from hp where a = 1;
+ QUERY PLAN
+-------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: (a = 1)
+ -> Seq Scan on hp1
+ Filter: (a = 1)
+ -> Seq Scan on hp2
+ Filter: (a = 1)
+ -> Seq Scan on hp3
+ Filter: (a = 1)
+(9 rows)
+
+explain (costs off) select * from hp where b = 'xxx';
+ QUERY PLAN
+-----------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: (b = 'xxx'::text)
+ -> Seq Scan on hp1
+ Filter: (b = 'xxx'::text)
+ -> Seq Scan on hp2
+ Filter: (b = 'xxx'::text)
+ -> Seq Scan on hp3
+ Filter: (b = 'xxx'::text)
+(9 rows)
+
+explain (costs off) select * from hp where a is null;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: (a IS NULL)
+ -> Seq Scan on hp1
+ Filter: (a IS NULL)
+ -> Seq Scan on hp2
+ Filter: (a IS NULL)
+ -> Seq Scan on hp3
+ Filter: (a IS NULL)
+(9 rows)
+
+explain (costs off) select * from hp where b is null;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: (b IS NULL)
+ -> Seq Scan on hp1
+ Filter: (b IS NULL)
+ -> Seq Scan on hp2
+ Filter: (b IS NULL)
+ -> Seq Scan on hp3
+ Filter: (b IS NULL)
+(9 rows)
+
+explain (costs off) select * from hp where a < 1 and b = 'xxx';
+ QUERY PLAN
+-------------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: ((a < 1) AND (b = 'xxx'::text))
+ -> Seq Scan on hp1
+ Filter: ((a < 1) AND (b = 'xxx'::text))
+ -> Seq Scan on hp2
+ Filter: ((a < 1) AND (b = 'xxx'::text))
+ -> Seq Scan on hp3
+ Filter: ((a < 1) AND (b = 'xxx'::text))
+(9 rows)
+
+explain (costs off) select * from hp where a <> 1 and b = 'yyy';
+ QUERY PLAN
+--------------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: ((a <> 1) AND (b = 'yyy'::text))
+ -> Seq Scan on hp1
+ Filter: ((a <> 1) AND (b = 'yyy'::text))
+ -> Seq Scan on hp2
+ Filter: ((a <> 1) AND (b = 'yyy'::text))
+ -> Seq Scan on hp3
+ Filter: ((a <> 1) AND (b = 'yyy'::text))
+(9 rows)
+
+explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
+ QUERY PLAN
+---------------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: ((a <> 1) AND (b <> 'xxx'::text))
+ -> Seq Scan on hp1
+ Filter: ((a <> 1) AND (b <> 'xxx'::text))
+ -> Seq Scan on hp2
+ Filter: ((a <> 1) AND (b <> 'xxx'::text))
+ -> Seq Scan on hp3
+ Filter: ((a <> 1) AND (b <> 'xxx'::text))
+(9 rows)
+
+-- pruning should work if either a value or a IS NULL clause is provided for
+-- each of the keys
+explain (costs off) select * from hp where a is null and b is null;
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: ((a IS NULL) AND (b IS NULL))
+(3 rows)
+
+explain (costs off) select * from hp where a = 1 and b is null;
+ QUERY PLAN
+-------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: ((b IS NULL) AND (a = 1))
+(3 rows)
+
+explain (costs off) select * from hp where a = 1 and b = 'xxx';
+ QUERY PLAN
+-------------------------------------------------
+ Append
+ -> Seq Scan on hp3
+ Filter: ((a = 1) AND (b = 'xxx'::text))
+(3 rows)
+
+explain (costs off) select * from hp where a is null and b = 'xxx';
+ QUERY PLAN
+-----------------------------------------------------
+ Append
+ -> Seq Scan on hp2
+ Filter: ((a IS NULL) AND (b = 'xxx'::text))
+(3 rows)
+
+explain (costs off) select * from hp where a = 2 and b = 'xxx';
+ QUERY PLAN
+-------------------------------------------------
+ Append
+ -> Seq Scan on hp2
+ Filter: ((a = 2) AND (b = 'xxx'::text))
+(3 rows)
+
+explain (costs off) select * from hp where a = 1 and b = 'abcde';
+ QUERY PLAN
+---------------------------------------------------
+ Append
+ -> Seq Scan on hp1
+ Filter: ((a = 1) AND (b = 'abcde'::text))
+(3 rows)
+
+explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2
and b = 'xxx') or (a is null and b is null);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on hp0
+ Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
+ -> Seq Scan on hp1
+ Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
+ -> Seq Scan on hp2
+ Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
+(7 rows)
+
+drop table hp;
+drop operator class pp_test_text_ops using hash;
+drop operator class pp_test_int4_ops using hash;
+drop function pp_hashint4_noop(int4, int8);
+drop function pp_hashtext_length(text, int8);
+--
-- Test runtime partition pruning
--
create table ab (a int not null, b int not null) partition by list (a);
diff --git a/src/test/regress/expected/partition_prune_hash.out
b/src/test/regress/expected/partition_prune_hash.out
deleted file mode 100644
index fbba3f1ff8..0000000000
--- a/src/test/regress/expected/partition_prune_hash.out
+++ /dev/null
@@ -1,189 +0,0 @@
---
--- Test Partition pruning for HASH partitioning
--- We keep this as a seperate test as hash functions return
--- values will vary based on CPU architecture.
---
-create table hp (a int, b text) partition by hash (a, b);
-create table hp0 partition of hp for values with (modulus 4, remainder 0);
-create table hp3 partition of hp for values with (modulus 4, remainder 3);
-create table hp1 partition of hp for values with (modulus 4, remainder 1);
-create table hp2 partition of hp for values with (modulus 4, remainder 2);
-insert into hp values (null, null);
-insert into hp values (1, null);
-insert into hp values (1, 'xxx');
-insert into hp values (null, 'xxx');
-insert into hp values (10, 'xxx');
-insert into hp values (10, 'yyy');
-select tableoid::regclass, * from hp order by 1;
- tableoid | a | b
-----------+----+-----
- hp0 | |
- hp0 | 1 |
- hp0 | 1 | xxx
- hp3 | 10 | yyy
- hp1 | | xxx
- hp2 | 10 | xxx
-(6 rows)
-
--- partial keys won't prune, nor would non-equality conditions
-explain (costs off) select * from hp where a = 1;
- QUERY PLAN
--------------------------
- Append
- -> Seq Scan on hp0
- Filter: (a = 1)
- -> Seq Scan on hp1
- Filter: (a = 1)
- -> Seq Scan on hp2
- Filter: (a = 1)
- -> Seq Scan on hp3
- Filter: (a = 1)
-(9 rows)
-
-explain (costs off) select * from hp where b = 'xxx';
- QUERY PLAN
------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp1
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp2
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp3
- Filter: (b = 'xxx'::text)
-(9 rows)
-
-explain (costs off) select * from hp where a is null;
- QUERY PLAN
------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (a IS NULL)
- -> Seq Scan on hp1
- Filter: (a IS NULL)
- -> Seq Scan on hp2
- Filter: (a IS NULL)
- -> Seq Scan on hp3
- Filter: (a IS NULL)
-(9 rows)
-
-explain (costs off) select * from hp where b is null;
- QUERY PLAN
------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (b IS NULL)
- -> Seq Scan on hp1
- Filter: (b IS NULL)
- -> Seq Scan on hp2
- Filter: (b IS NULL)
- -> Seq Scan on hp3
- Filter: (b IS NULL)
-(9 rows)
-
-explain (costs off) select * from hp where a < 1 and b = 'xxx';
- QUERY PLAN
--------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp1
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp2
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp3
- Filter: ((a < 1) AND (b = 'xxx'::text))
-(9 rows)
-
-explain (costs off) select * from hp where a <> 1 and b = 'yyy';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp1
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp2
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp3
- Filter: ((a <> 1) AND (b = 'yyy'::text))
-(9 rows)
-
--- pruning should work if non-null values are provided for all the keys
-explain (costs off) select * from hp where a is null and b is null;
- QUERY PLAN
------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a IS NULL) AND (b IS NULL))
-(3 rows)
-
-explain (costs off) select * from hp where a = 1 and b is null;
- QUERY PLAN
--------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((b IS NULL) AND (a = 1))
-(3 rows)
-
-explain (costs off) select * from hp where a = 1 and b = 'xxx';
- QUERY PLAN
--------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a = 1) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a is null and b = 'xxx';
- QUERY PLAN
------------------------------------------------------
- Append
- -> Seq Scan on hp1
- Filter: ((a IS NULL) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a = 10 and b = 'xxx';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp2
- Filter: ((a = 10) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a = 10 and b = 'yyy';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp3
- Filter: ((a = 10) AND (b = 'yyy'::text))
-(3 rows)
-
-explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10
and b = 'xxx') or (a is null and b is null);
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
- -> Seq Scan on hp2
- Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
- -> Seq Scan on hp3
- Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
-(7 rows)
-
--- hash partitiong pruning doesn't occur with <> operator clauses
-explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
- QUERY PLAN
----------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp1
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp2
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp3
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
-(9 rows)
-
-drop table hp;
diff --git a/src/test/regress/expected/partition_prune_hash_1.out
b/src/test/regress/expected/partition_prune_hash_1.out
deleted file mode 100644
index 4a26a0e277..0000000000
--- a/src/test/regress/expected/partition_prune_hash_1.out
+++ /dev/null
@@ -1,187 +0,0 @@
---
--- Test Partition pruning for HASH partitioning
--- We keep this as a seperate test as hash functions return
--- values will vary based on CPU architecture.
---
-create table hp (a int, b text) partition by hash (a, b);
-create table hp0 partition of hp for values with (modulus 4, remainder 0);
-create table hp3 partition of hp for values with (modulus 4, remainder 3);
-create table hp1 partition of hp for values with (modulus 4, remainder 1);
-create table hp2 partition of hp for values with (modulus 4, remainder 2);
-insert into hp values (null, null);
-insert into hp values (1, null);
-insert into hp values (1, 'xxx');
-insert into hp values (null, 'xxx');
-insert into hp values (10, 'xxx');
-insert into hp values (10, 'yyy');
-select tableoid::regclass, * from hp order by 1;
- tableoid | a | b
-----------+----+-----
- hp0 | |
- hp0 | 1 |
- hp0 | 10 | xxx
- hp3 | | xxx
- hp3 | 10 | yyy
- hp2 | 1 | xxx
-(6 rows)
-
--- partial keys won't prune, nor would non-equality conditions
-explain (costs off) select * from hp where a = 1;
- QUERY PLAN
--------------------------
- Append
- -> Seq Scan on hp0
- Filter: (a = 1)
- -> Seq Scan on hp1
- Filter: (a = 1)
- -> Seq Scan on hp2
- Filter: (a = 1)
- -> Seq Scan on hp3
- Filter: (a = 1)
-(9 rows)
-
-explain (costs off) select * from hp where b = 'xxx';
- QUERY PLAN
------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp1
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp2
- Filter: (b = 'xxx'::text)
- -> Seq Scan on hp3
- Filter: (b = 'xxx'::text)
-(9 rows)
-
-explain (costs off) select * from hp where a is null;
- QUERY PLAN
------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (a IS NULL)
- -> Seq Scan on hp1
- Filter: (a IS NULL)
- -> Seq Scan on hp2
- Filter: (a IS NULL)
- -> Seq Scan on hp3
- Filter: (a IS NULL)
-(9 rows)
-
-explain (costs off) select * from hp where b is null;
- QUERY PLAN
------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (b IS NULL)
- -> Seq Scan on hp1
- Filter: (b IS NULL)
- -> Seq Scan on hp2
- Filter: (b IS NULL)
- -> Seq Scan on hp3
- Filter: (b IS NULL)
-(9 rows)
-
-explain (costs off) select * from hp where a < 1 and b = 'xxx';
- QUERY PLAN
--------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp1
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp2
- Filter: ((a < 1) AND (b = 'xxx'::text))
- -> Seq Scan on hp3
- Filter: ((a < 1) AND (b = 'xxx'::text))
-(9 rows)
-
-explain (costs off) select * from hp where a <> 1 and b = 'yyy';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp1
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp2
- Filter: ((a <> 1) AND (b = 'yyy'::text))
- -> Seq Scan on hp3
- Filter: ((a <> 1) AND (b = 'yyy'::text))
-(9 rows)
-
--- pruning should work if non-null values are provided for all the keys
-explain (costs off) select * from hp where a is null and b is null;
- QUERY PLAN
------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a IS NULL) AND (b IS NULL))
-(3 rows)
-
-explain (costs off) select * from hp where a = 1 and b is null;
- QUERY PLAN
--------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((b IS NULL) AND (a = 1))
-(3 rows)
-
-explain (costs off) select * from hp where a = 1 and b = 'xxx';
- QUERY PLAN
--------------------------------------------------
- Append
- -> Seq Scan on hp2
- Filter: ((a = 1) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a is null and b = 'xxx';
- QUERY PLAN
------------------------------------------------------
- Append
- -> Seq Scan on hp3
- Filter: ((a IS NULL) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a = 10 and b = 'xxx';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a = 10) AND (b = 'xxx'::text))
-(3 rows)
-
-explain (costs off) select * from hp where a = 10 and b = 'yyy';
- QUERY PLAN
---------------------------------------------------
- Append
- -> Seq Scan on hp3
- Filter: ((a = 10) AND (b = 'yyy'::text))
-(3 rows)
-
-explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10
and b = 'xxx') or (a is null and b is null);
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
- -> Seq Scan on hp3
- Filter: (((a = 10) AND (b = 'yyy'::text)) OR ((a = 10) AND (b =
'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
-(5 rows)
-
--- hash partitiong pruning doesn't occur with <> operator clauses
-explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
- QUERY PLAN
----------------------------------------------------
- Append
- -> Seq Scan on hp0
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp1
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp2
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
- -> Seq Scan on hp3
- Filter: ((a <> 1) AND (b <> 'xxx'::text))
-(9 rows)
-
-drop table hp;
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index 0d3a27ed41..839d8a4a4d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs
prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune partition_prune_hash reloptions
hash_part indexing partition_aggregate fast_default
+test: identity partition_join partition_prune reloptions hash_part indexing
partition_aggregate fast_default
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 20027c131c..12e10b3ce4 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -185,7 +185,6 @@ test: xml
test: identity
test: partition_join
test: partition_prune
-test: partition_prune_hash
test: reloptions
test: hash_part
test: indexing
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index 7fe93bbc04..6cc8e3cdfc 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -238,6 +238,62 @@ explain (costs off) select * from rparted_by_int2 where a
> 100000000000000;
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp,
coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
+--
+-- Test Partition pruning for HASH partitioning
+-- We roll our own operator classes to use for tests, because depending on the
+-- platform-provided hashing functions makes tests unreliable
+--
+
+CREATE OR REPLACE FUNCTION pp_hashint4_noop(int4, int8) RETURNS int8 AS
+$$SELECT coalesce($1, $2)::int8$$ LANGUAGE sql IMMUTABLE;
+
+CREATE OPERATOR CLASS pp_test_int4_ops FOR TYPE int4 USING HASH AS
+OPERATOR 1 = , FUNCTION 2 pp_hashint4_noop(int4, int8);
+
+CREATE OR REPLACE FUNCTION pp_hashtext_length(text, int8) RETURNS int8 AS
+$$SELECT length(coalesce($1, ''))::int8$$ LANGUAGE sql IMMUTABLE;
+
+CREATE OPERATOR CLASS pp_test_text_ops FOR TYPE text USING HASH AS
+OPERATOR 1 = , FUNCTION 2 pp_hashtext_length(text, int8);
+
+create table hp (a int, b text) partition by hash (a pp_test_int4_ops, b
pp_test_text_ops);
+create table hp0 partition of hp for values with (modulus 4, remainder 0);
+create table hp3 partition of hp for values with (modulus 4, remainder 3);
+create table hp1 partition of hp for values with (modulus 4, remainder 1);
+create table hp2 partition of hp for values with (modulus 4, remainder 2);
+
+insert into hp values (null, null);
+insert into hp values (1, null);
+insert into hp values (1, 'xxx');
+insert into hp values (null, 'xxx');
+insert into hp values (2, 'xxx');
+insert into hp values (1, 'abcde');
+select tableoid::regclass, * from hp order by 1;
+
+-- partial keys won't prune, nor would non-equality conditions
+explain (costs off) select * from hp where a = 1;
+explain (costs off) select * from hp where b = 'xxx';
+explain (costs off) select * from hp where a is null;
+explain (costs off) select * from hp where b is null;
+explain (costs off) select * from hp where a < 1 and b = 'xxx';
+explain (costs off) select * from hp where a <> 1 and b = 'yyy';
+explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
+
+-- pruning should work if either a value or a IS NULL clause is provided for
+-- each of the keys
+explain (costs off) select * from hp where a is null and b is null;
+explain (costs off) select * from hp where a = 1 and b is null;
+explain (costs off) select * from hp where a = 1 and b = 'xxx';
+explain (costs off) select * from hp where a is null and b = 'xxx';
+explain (costs off) select * from hp where a = 2 and b = 'xxx';
+explain (costs off) select * from hp where a = 1 and b = 'abcde';
+explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2
and b = 'xxx') or (a is null and b is null);
+
+drop table hp;
+drop operator class pp_test_text_ops using hash;
+drop operator class pp_test_int4_ops using hash;
+drop function pp_hashint4_noop(int4, int8);
+drop function pp_hashtext_length(text, int8);
--
-- Test runtime partition pruning
@@ -587,4 +643,4 @@ 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;
diff --git a/src/test/regress/sql/partition_prune_hash.sql
b/src/test/regress/sql/partition_prune_hash.sql
deleted file mode 100644
index fd1783bf53..0000000000
--- a/src/test/regress/sql/partition_prune_hash.sql
+++ /dev/null
@@ -1,41 +0,0 @@
---
--- Test Partition pruning for HASH partitioning
--- We keep this as a seperate test as hash functions return
--- values will vary based on CPU architecture.
---
-
-create table hp (a int, b text) partition by hash (a, b);
-create table hp0 partition of hp for values with (modulus 4, remainder 0);
-create table hp3 partition of hp for values with (modulus 4, remainder 3);
-create table hp1 partition of hp for values with (modulus 4, remainder 1);
-create table hp2 partition of hp for values with (modulus 4, remainder 2);
-
-insert into hp values (null, null);
-insert into hp values (1, null);
-insert into hp values (1, 'xxx');
-insert into hp values (null, 'xxx');
-insert into hp values (10, 'xxx');
-insert into hp values (10, 'yyy');
-select tableoid::regclass, * from hp order by 1;
-
--- partial keys won't prune, nor would non-equality conditions
-explain (costs off) select * from hp where a = 1;
-explain (costs off) select * from hp where b = 'xxx';
-explain (costs off) select * from hp where a is null;
-explain (costs off) select * from hp where b is null;
-explain (costs off) select * from hp where a < 1 and b = 'xxx';
-explain (costs off) select * from hp where a <> 1 and b = 'yyy';
-
--- pruning should work if non-null values are provided for all the keys
-explain (costs off) select * from hp where a is null and b is null;
-explain (costs off) select * from hp where a = 1 and b is null;
-explain (costs off) select * from hp where a = 1 and b = 'xxx';
-explain (costs off) select * from hp where a is null and b = 'xxx';
-explain (costs off) select * from hp where a = 10 and b = 'xxx';
-explain (costs off) select * from hp where a = 10 and b = 'yyy';
-explain (costs off) select * from hp where (a = 10 and b = 'yyy') or (a = 10
and b = 'xxx') or (a is null and b is null);
-
--- hash partitiong pruning doesn't occur with <> operator clauses
-explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
-
-drop table hp;
--
2.11.0
