diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 102 | ||||
| -rw-r--r-- | src/test/regress/sql/partition_prune.sql | 49 |
2 files changed, 151 insertions, 0 deletions
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 50d2a7e4b97..80e71b8e2b9 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3671,6 +3671,108 @@ explain (costs off) update listp1 set a = 1 where a = 2; reset constraint_exclusion; reset enable_partition_pruning; drop table listp; +-- Ensure run-time pruning works correctly for nested Append nodes +set parallel_setup_cost to 0; +set parallel_tuple_cost to 0; +create table listp (a int) partition by list(a); +create table listp_12 partition of listp for values in(1,2) partition by list(a); +create table listp_12_1 partition of listp_12 for values in(1); +create table listp_12_2 partition of listp_12 for values in(2); +-- Force the 2nd subnode of the Append to be non-parallel. This results in +-- a nested Append node because the mixed parallel / non-parallel paths cannot +-- be pulled into the top-level Append. +alter table listp_12_1 set (parallel_workers = 0); +-- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in +-- the plan as it's pulled in setref.c due to having just a single subnode). +explain (analyze on, costs off, timing off, summary off) +select * from listp where a = (select 1); + QUERY PLAN +---------------------------------------------------------------------- + Gather (actual rows=0 loops=1) + Workers Planned: 2 + Params Evaluated: $0 + Workers Launched: 2 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Parallel Append (actual rows=0 loops=3) + -> Seq Scan on listp_12_1 listp_1 (actual rows=0 loops=1) + Filter: (a = $0) + -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) +(11 rows) + +-- Like the above but throw some more complexity at the planner by adding +-- a UNION ALL. We expect both sides of the union not to scan the +-- non-required partitions. +explain (analyze on, costs off, timing off, summary off) +select * from listp where a = (select 1) + union all +select * from listp where a = (select 2); + QUERY PLAN +----------------------------------------------------------------------------------- + Append (actual rows=0 loops=1) + -> Gather (actual rows=0 loops=1) + Workers Planned: 2 + Params Evaluated: $0 + Workers Launched: 2 + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + -> Parallel Append (actual rows=0 loops=3) + -> Seq Scan on listp_12_1 listp_1 (actual rows=0 loops=1) + Filter: (a = $0) + -> Parallel Seq Scan on listp_12_2 listp_2 (never executed) + Filter: (a = $0) + -> Gather (actual rows=0 loops=1) + Workers Planned: 2 + Params Evaluated: $1 + Workers Launched: 2 + InitPlan 2 (returns $1) + -> Result (actual rows=1 loops=1) + -> Parallel Append (actual rows=0 loops=3) + -> Seq Scan on listp_12_1 listp_4 (never executed) + Filter: (a = $1) + -> Parallel Seq Scan on listp_12_2 listp_5 (actual rows=0 loops=1) + Filter: (a = $1) +(23 rows) + +drop table listp; +reset parallel_tuple_cost; +reset parallel_setup_cost; +-- Test case for run-time pruning with a nested Merge Append +set enable_sort to 0; +create table rangep (a int, b int) partition by range (a); +create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b); +-- We need 3 sub-partitions. 1 to validate pruning worked and another two +-- because a single remaining partition would be pulled up to the main Append. +create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1); +create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2); +create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); +create table rangep_100_to_200 partition of rangep for values from (100) to (200); +create index on rangep (a); +-- Ensure run-time pruning works on the nested Merge Append +explain (analyze on, costs off, timing off, summary off) +select * from rangep where b IN((select 1),(select 2)) order by a; + QUERY PLAN +------------------------------------------------------------------------------------------------------------ + Append (actual rows=0 loops=1) + InitPlan 1 (returns $0) + -> Result (actual rows=1 loops=1) + InitPlan 2 (returns $1) + -> Result (actual rows=1 loops=1) + -> Merge Append (actual rows=0 loops=1) + Sort Key: rangep_2.a + -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed) + Filter: (b = ANY (ARRAY[$0, $1])) + -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1) + Filter: (b = ANY (ARRAY[$0, $1])) +(15 rows) + +reset enable_sort; +drop table rangep; -- -- Check that gen_prune_steps_from_opexps() works well for various cases of -- clauses for different partition keys diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 1e904a8c5b7..939a9b1193e 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -1051,6 +1051,55 @@ reset enable_partition_pruning; drop table listp; +-- Ensure run-time pruning works correctly for nested Append nodes +set parallel_setup_cost to 0; +set parallel_tuple_cost to 0; + +create table listp (a int) partition by list(a); +create table listp_12 partition of listp for values in(1,2) partition by list(a); +create table listp_12_1 partition of listp_12 for values in(1); +create table listp_12_2 partition of listp_12 for values in(2); + +-- Force the 2nd subnode of the Append to be non-parallel. This results in +-- a nested Append node because the mixed parallel / non-parallel paths cannot +-- be pulled into the top-level Append. +alter table listp_12_1 set (parallel_workers = 0); + +-- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in +-- the plan as it's pulled in setref.c due to having just a single subnode). +explain (analyze on, costs off, timing off, summary off) +select * from listp where a = (select 1); + +-- Like the above but throw some more complexity at the planner by adding +-- a UNION ALL. We expect both sides of the union not to scan the +-- non-required partitions. +explain (analyze on, costs off, timing off, summary off) +select * from listp where a = (select 1) + union all +select * from listp where a = (select 2); + +drop table listp; +reset parallel_tuple_cost; +reset parallel_setup_cost; + +-- Test case for run-time pruning with a nested Merge Append +set enable_sort to 0; +create table rangep (a int, b int) partition by range (a); +create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b); +-- We need 3 sub-partitions. 1 to validate pruning worked and another two +-- because a single remaining partition would be pulled up to the main Append. +create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1); +create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2); +create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3); +create table rangep_100_to_200 partition of rangep for values from (100) to (200); +create index on rangep (a); + +-- Ensure run-time pruning works on the nested Merge Append +explain (analyze on, costs off, timing off, summary off) +select * from rangep where b IN((select 1),(select 2)) order by a; +reset enable_sort; +drop table rangep; + -- -- Check that gen_prune_steps_from_opexps() works well for various cases of -- clauses for different partition keys |