On 2018/04/10 13:27, Ashutosh Bapat wrote: > On Mon, Apr 9, 2018 at 8:56 PM, Robert Haas <robertmh...@gmail.com> wrote: >> On Fri, Apr 6, 2018 at 11:41 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> David Rowley <david.row...@2ndquadrant.com> writes: >>>> Sounds like you're saying that if we have too many alternative files >>>> then there's a chance that one could pass by luck. >>> >>> Yeah, exactly: it passed, but did it pass for the right reason? >>> >>> If there's just two expected-files, it's likely not a big problem, >>> but if you have a bunch it's something to worry about. >>> >>> I'm also wondering how come we had hash partitioning before and >>> did not have this sort of problem. Is it just that we added a >>> new test that's more sensitive to the details of the hashing >>> (if so, could it be made less so)? Or is there actually more >>> platform dependence now than before (and if so, why is that)? >> >> The existing hash partitioning tests did have some dependencies on the >> hash function, but they took care not to use the built-in hash >> functions. Instead they did stuff like this: >> >> 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); >> >> I think that this approach should also be used for the new tests. >> Variant expected output files are a pain to maintain, and you >> basically just have to take whatever output you get as the right >> answer, because nobody knows what output a certain built-in hash >> function should produce for a given input except by running the code. >> If you do the kind of thing shown above, though, then you can easily >> see by inspection that you're getting the right answer.
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]. However, I noticed that there is a bug in RelationBuildPartitionKey that causes a crash when using a SQL function as partition support function as the revised tests do, so please refer to and apply the patches I posted here before running the revised tests: https://www.postgresql.org/message-id/3041e853-b1dd-a0c6-ff21-7cc5633bffd0%40lab.ntt.co.jp Thanks, Amit [1] https://www.postgresql.org/message-id/CAKJS1f-SON_hAekqoV4_WQwJBtJ_rvvSe68jRNhuYcXqQ8PoQg%40mail.gmail.com
From c1508fc715a7783108f626c67c76fcc1f2303719 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Tue, 10 Apr 2018 16:06:33 +0900 Subject: [PATCH v1] 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 | 202 ++++++++++++++++++++- 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 | 59 +++++- src/test/regress/sql/partition_prune_hash.sql | 41 ----- 7 files changed, 259 insertions(+), 422 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..935e7dc79b 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1330,7 +1330,207 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; Filter: (a > '100000000000000'::bigint) (3 rows) -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)::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; +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) + +-- 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 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) + +-- 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 lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2, 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 -- 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..c02d3e2494 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -236,8 +236,63 @@ create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values fr -- all partitions but rparted_by_int2_maxvalue pruned 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)::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; + +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'; + +-- 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 = 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); + +-- hash partitiong pruning doesn't occur with <> operator clauses +explain (costs off) select * from hp where a <> 1 and b <> 'xxx'; + +drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2, 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 +642,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
