Hi David. Thanks for the review.
On 2018/04/11 17:59, David Rowley wrote: > On 11 April 2018 at 18:04, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: >> Updated patch attached. > > Thanks for the updated patch. > > The only thing I'm not sure about is the chances you've made to the > COALESCE function. > > +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; > > Why does one default to the seed and the other to an empty string? > Shouldn't they both do the same thing? If you were to copy the > hash_part.sql you'd just coalesce($1, 0) and coalesce($1, ''), any > special reason not to do that? Oops, so I hadn't actually restored it to the way it is in hash_part.sql. Also, Ashutosh was talking about the custom hashing function used in insert.sql, not hash_part.sql, which I based my revision upon. Fixed it now. > Also just wondering if it's worth adding some verification that we've > actually eliminated the correct partitions by backing the tests up > with a call to satisfies_hash_partition. > > I've attached a delta patch that applies to your v2 which does this. > Do you think it's worth doing? We can see check by inspection that individual values are in appropriate partitions, which is the point of having the inserts and the select just above the actual pruning related tests. So, I'm not sure if adding the satisfies_hash_partition against each pruning tests adds much. Attached revised patch. Thanks, Amit
From 4685448a7eb2eaf5feceea2206d136c135b2dea7 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Tue, 10 Apr 2018 16:06:33 +0900 Subject: [PATCH v3] 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 | 237 +++++++++++++++++++++ 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 | 70 +++++- src/test/regress/sql/partition_prune_hash.sql | 41 ---- 7 files changed, 307 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..d13389b9c2 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1332,6 +1332,243 @@ 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, 0)::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) + +select satisfies_hash_partition('hp'::regclass, 4, 0, null::int, null::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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) + +select satisfies_hash_partition('hp'::regclass, 4, 0, 1, null::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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) + +select satisfies_hash_partition('hp'::regclass, 4, 3, 1, 'xxx'::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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) + +select satisfies_hash_partition('hp'::regclass, 4, 2, null::int, 'xxx'::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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) + +select satisfies_hash_partition('hp'::regclass, 4, 2, 2, 'xxx'::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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) + +select satisfies_hash_partition('hp'::regclass, 4, 1, 1, 'abcde'::text); + satisfies_hash_partition +-------------------------- + t +(1 row) + +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..f18c4c7f9e 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -238,6 +238,74 @@ 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, 0)::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; +select satisfies_hash_partition('hp'::regclass, 4, 0, null::int, null::text); + +explain (costs off) select * from hp where a = 1 and b is null; +select satisfies_hash_partition('hp'::regclass, 4, 0, 1, null::text); + +explain (costs off) select * from hp where a = 1 and b = 'xxx'; +select satisfies_hash_partition('hp'::regclass, 4, 3, 1, 'xxx'::text); + +explain (costs off) select * from hp where a is null and b = 'xxx'; +select satisfies_hash_partition('hp'::regclass, 4, 2, null::int, 'xxx'::text); + +explain (costs off) select * from hp where a = 2 and b = 'xxx'; +select satisfies_hash_partition('hp'::regclass, 4, 2, 2, 'xxx'::text); + +explain (costs off) select * from hp where a = 1 and b = 'abcde'; +select satisfies_hash_partition('hp'::regclass, 4, 1, 1, 'abcde'::text); + +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 +655,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
