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