diff options
Diffstat (limited to 'src/test/regress/sql/partition_prune.sql')
| -rw-r--r-- | src/test/regress/sql/partition_prune.sql | 27 |
1 files changed, 19 insertions, 8 deletions
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index f17240a5e40..9b94ac50af5 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -291,19 +291,20 @@ drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pru -- part_part_test_int4_ops and part_test_text_ops in insert.sql. -- -create table hp (a int, b text) partition by hash (a part_test_int4_ops, b part_test_text_ops); +create table hp (a int, b text, c int) + partition by hash (a part_test_int4_ops, b part_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; +insert into hp values (null, null, 0); +insert into hp values (1, null, 1); +insert into hp values (1, 'xxx', 2); +insert into hp values (null, 'xxx', 3); +insert into hp values (2, 'xxx', 4); +insert into hp values (1, 'abcde', 5); +select tableoid::regclass, * from hp order by c; -- partial keys won't prune, nor would non-equality conditions explain (costs off) select * from hp where a = 1; @@ -324,6 +325,16 @@ 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); +-- test pruning when not all the partitions exist +drop table hp1; +drop table hp3; +explain (costs off) select * from hp where a = 1 and b = 'abcde'; +explain (costs off) select * from hp where a = 1 and b = 'abcde' and + (c = 2 or c = 3); +drop table hp2; +explain (costs off) select * from hp where a = 1 and b = 'abcde' and + (c = 2 or c = 3); + drop table hp; -- |