runtime_pruning_make_tests_stable.patch
application/octet-stream
Filename: runtime_pruning_make_tests_stable.patch
Type: application/octet-stream
Part: 0
Patch
Format: unified
| File | + | − |
|---|---|---|
| src/test/regress/expected/partition_prune.out | 132 | 193 |
| src/test/regress/sql/partition_prune.sql | 17 | 1 |
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9762cc9748..de339797f5 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1347,6 +1347,8 @@ create table ab_a3 partition of ab for values in(3) partition by list (b);
create table ab_a3_b1 partition of ab_a3 for values in (1);
create table ab_a3_b2 partition of ab_a3 for values in (2);
create table ab_a3_b3 partition of ab_a3 for values in (3);
+-- disable to prevent variable "Heap Fetches:" output
+set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
-- Execute query 5 times to allow choose_custom_plan
@@ -1709,7 +1711,7 @@ create table lprt_a (a int not null);
insert into lprt_a select 0 from generate_series(1,100);
-- and insert some values that we should find.
insert into lprt_a values(1),(1);
-analyze lprt_a;
+vacuum (analyze) lprt_a;
create index ab_a2_b1_a_idx on ab_a2_b1 (a);
create index ab_a2_b2_a_idx on ab_a2_b2 (a);
create index ab_a2_b3_a_idx on ab_a2_b3 (a);
@@ -1754,8 +1756,8 @@ execute ab_q6 (1, 2, 3);
(1 row)
explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
- QUERY PLAN
---------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=2 loops=1)
Workers Planned: 1
@@ -1765,39 +1767,31 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
-> Parallel Seq Scan on lprt_a a (actual rows=51 loops=2)
Filter: (a = ANY ('{0,0,1}'::integer[]))
-> Append (actual rows=0 loops=102)
- -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
-(36 rows)
+(27 rows)
insert into lprt_a values(3),(3);
+vacuum (analyze) lprt_a;
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
- QUERY PLAN
---------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=2 loops=1)
Workers Planned: 1
@@ -1807,38 +1801,29 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
-> Parallel Seq Scan on lprt_a a (actual rows=52 loops=2)
Filter: (a = ANY ('{1,0,3}'::integer[]))
-> Append (actual rows=0 loops=104)
- -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
+ -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
+ -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
+ -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
-(36 rows)
+(27 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
- QUERY PLAN
---------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=2 loops=1)
Workers Planned: 1
@@ -1849,39 +1834,31 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
Filter: (a = ANY ('{1,0,0}'::integer[]))
Rows Removed by Filter: 1
-> Append (actual rows=0 loops=102)
- -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
+ -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (actual rows=0 loops=2)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
-(37 rows)
+(28 rows)
delete from lprt_a where a = 1;
+vacuum (analyze) lprt_a;
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=2 loops=1)
Workers Planned: 1
@@ -1892,34 +1869,25 @@ explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
Filter: (a = ANY ('{1,0,0}'::integer[]))
Rows Removed by Filter: 1
-> Append (actual rows=0 loops=100)
- -> Index Only Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
+ -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)
+ -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed)
+ -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
+ -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
+ -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
+ -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
+ -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
+ -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
- -> Index Only Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
+ -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 (never executed)
Index Cond: (a = a.a)
- Heap Fetches: 0
-(37 rows)
+(28 rows)
reset enable_hashjoin;
reset enable_mergejoin;
@@ -2011,61 +1979,50 @@ create index tprt4_idx on tprt_4 (col1);
create index tprt5_idx on tprt_5 (col1);
create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+vacuum (analyze) tprt;
set enable_hashjoin = off;
set enable_mergejoin = off;
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Nested Loop (actual rows=6 loops=1)
-> Seq Scan on tbl1 (actual rows=2 loops=1)
-> Append (actual rows=3 loops=2)
- -> Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 4
- -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 2
- -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
-(21 rows)
+ -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+(15 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Nested Loop (actual rows=2 loops=1)
-> Seq Scan on tbl1 (actual rows=2 loops=1)
-> Append (actual rows=1 loops=2)
- -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ -> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+ -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 2
- -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ -> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ -> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
-(21 rows)
+(15 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
@@ -2091,59 +2048,50 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
+vacuum (analyze) tbl1;
+set enable_seqscan = off;
+set enable_bitmapscan = off;
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Nested Loop (actual rows=23 loops=1)
-> Seq Scan on tbl1 (actual rows=5 loops=1)
-> Append (actual rows=5 loops=5)
- -> Index Only Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 10
- -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 11
- -> Index Only Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 2
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
- Index Cond: (col1 < tbl1.col1)
- Heap Fetches: 0
-(21 rows)
+ -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+ -> Index Scan using tprt6_idx on tprt_6 (never executed)
+ Index Cond: (tbl1.col1 > col1)
+(15 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Nested Loop (actual rows=3 loops=1)
-> Seq Scan on tbl1 (actual rows=5 loops=1)
-> Append (actual rows=1 loops=5)
- -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ -> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
+ -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 2
- -> Index Only Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
+ -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 1
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ -> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
-(21 rows)
+(15 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
@@ -2190,30 +2138,24 @@ delete from tbl1;
insert into tbl1 values (4400);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Nested Loop (actual rows=1 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt2_idx on tprt_2 (never executed)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
- Index Cond: (col1 > tbl1.col1)
- Heap Fetches: 1
-(21 rows)
+ -> Index Scan using tprt1_idx on tprt_1 (never executed)
+ Index Cond: (tbl1.col1 < col1)
+ -> Index Scan using tprt2_idx on tprt_2 (never executed)
+ Index Cond: (tbl1.col1 < col1)
+ -> Index Scan using tprt3_idx on tprt_3 (never executed)
+ Index Cond: (tbl1.col1 < col1)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
+ Index Cond: (tbl1.col1 < col1)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
+ Index Cond: (tbl1.col1 < col1)
+ -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
+ Index Cond: (tbl1.col1 < col1)
+(15 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 < tprt.col1
@@ -2228,30 +2170,24 @@ delete from tbl1;
insert into tbl1 values (10000);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop (actual rows=0 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
- -> Index Only Scan using tprt1_idx on tprt_1 (never executed)
+ -> Index Scan using tprt1_idx on tprt_1 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt2_idx on tprt_2 (never executed)
+ -> Index Scan using tprt2_idx on tprt_2 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt3_idx on tprt_3 (never executed)
+ -> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt4_idx on tprt_4 (never executed)
+ -> Index Scan using tprt4_idx on tprt_4 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt5_idx on tprt_5 (never executed)
+ -> Index Scan using tprt5_idx on tprt_5 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
- -> Index Only Scan using tprt6_idx on tprt_6 (never executed)
+ -> Index Scan using tprt6_idx on tprt_6 (never executed)
Index Cond: (col1 = tbl1.col1)
- Heap Fetches: 0
-(21 rows)
+(15 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
@@ -2260,6 +2196,8 @@ order by tbl1.col1, tprt.col1;
------+------
(0 rows)
+reset enable_seqscan;
+reset enable_bitmapscan;
drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
@@ -2466,3 +2404,4 @@ select * from boolp where a = (select value from boolvalues where not value);
(9 rows)
drop table boolp;
+reset enable_indexonlyscan;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 1d712b4ada..7d75c9adf9 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -256,6 +256,9 @@ create table ab_a3_b1 partition of ab_a3 for values in (1);
create table ab_a3_b2 partition of ab_a3 for values in (2);
create table ab_a3_b3 partition of ab_a3 for values in (3);
+-- disable to prevent variable "Heap Fetches:" output
+set enable_indexonlyscan = off;
+
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
@@ -359,7 +362,7 @@ insert into lprt_a select 0 from generate_series(1,100);
-- and insert some values that we should find.
insert into lprt_a values(1),(1);
-analyze lprt_a;
+vacuum (analyze) lprt_a;
create index ab_a2_b1_a_idx on ab_a2_b1 (a);
create index ab_a2_b2_a_idx on ab_a2_b2 (a);
@@ -385,11 +388,13 @@ execute ab_q6 (1, 2, 3);
explain (analyze, costs off, summary off, timing off) execute ab_q6 (0, 0, 1);
insert into lprt_a values(3),(3);
+vacuum (analyze) lprt_a;
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 3);
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
delete from lprt_a where a = 1;
+vacuum (analyze) lprt_a;
explain (analyze, costs off, summary off, timing off) execute ab_q6 (1, 0, 0);
@@ -435,6 +440,7 @@ create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
+vacuum (analyze) tprt;
set enable_hashjoin = off;
set enable_mergejoin = off;
@@ -454,6 +460,11 @@ order by tbl1.col1, tprt.col1;
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
+vacuum (analyze) tbl1;
+
+set enable_seqscan = off;
+set enable_bitmapscan = off;
+
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
@@ -488,6 +499,9 @@ select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
+reset enable_seqscan;
+reset enable_bitmapscan;
+
drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
@@ -581,3 +595,5 @@ explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where not value);
drop table boolp;
+
+reset enable_indexonlyscan;
\ No newline at end of file