summaryrefslogtreecommitdiff
path: root/src/test/regress
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress')
-rw-r--r--src/test/regress/expected/aggregates.out51
-rw-r--r--src/test/regress/expected/case.out21
-rw-r--r--src/test/regress/expected/explain.out8
-rw-r--r--src/test/regress/expected/generated_virtual.out25
-rw-r--r--src/test/regress/expected/groupingsets.out3
-rw-r--r--src/test/regress/expected/inherit.out28
-rw-r--r--src/test/regress/expected/join.out143
-rw-r--r--src/test/regress/expected/merge.out3
-rw-r--r--src/test/regress/expected/partition_aggregate.out21
-rw-r--r--src/test/regress/expected/partition_join.out35
-rw-r--r--src/test/regress/expected/partition_prune.out141
-rw-r--r--src/test/regress/expected/predicate.out33
-rw-r--r--src/test/regress/expected/rowsecurity.out13
-rw-r--r--src/test/regress/expected/rowtypes.out6
-rw-r--r--src/test/regress/expected/select.out7
-rw-r--r--src/test/regress/expected/subselect.out17
-rw-r--r--src/test/regress/expected/tsrf.out6
-rw-r--r--src/test/regress/sql/explain.sql1
18 files changed, 338 insertions, 224 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index c35288eecde..1f24f6ffd1f 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -959,11 +959,12 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+(6 rows)
select min(unique1) from tenk1;
min
@@ -976,11 +977,12 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+(6 rows)
select max(unique1) from tenk1;
max
@@ -993,11 +995,12 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42))
-(5 rows)
+(6 rows)
select max(unique1) from tenk1 where unique1 < 42;
max
@@ -1010,11 +1013,12 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42))
-(5 rows)
+(6 rows)
select max(unique1) from tenk1 where unique1 > 42;
max
@@ -1033,11 +1037,12 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000))
-(5 rows)
+(6 rows)
select max(unique1) from tenk1 where unique1 > 42000;
max
@@ -1052,11 +1057,12 @@ explain (costs off)
QUERY PLAN
----------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan Backward using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
-(5 rows)
+(6 rows)
select max(tenthous) from tenk1 where thousand = 33;
max
@@ -1069,11 +1075,12 @@ explain (costs off)
QUERY PLAN
--------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_thous_tenthous on tenk1
Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL))
-(5 rows)
+(6 rows)
select min(tenthous) from tenk1 where thousand = 33;
min
@@ -1090,11 +1097,12 @@ explain (costs off)
Seq Scan on int4_tbl
SubPlan 2
-> Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1))
-(7 rows)
+(8 rows)
select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
from int4_tbl;
@@ -1119,7 +1127,8 @@ explain (costs off)
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+ Replaces: MinMaxAggregate
+(8 rows)
select distinct max(unique2) from tenk1;
max
@@ -1138,7 +1147,8 @@ explain (costs off)
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+ Replaces: MinMaxAggregate
+(8 rows)
select max(unique2) from tenk1 order by 1;
max
@@ -1157,7 +1167,8 @@ explain (costs off)
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+ Replaces: MinMaxAggregate
+(8 rows)
select max(unique2) from tenk1 order by max(unique2);
max
@@ -1176,7 +1187,8 @@ explain (costs off)
-> Index Only Scan Backward using tenk1_unique2 on tenk1
Index Cond: (unique2 IS NOT NULL)
-> Result
-(7 rows)
+ Replaces: MinMaxAggregate
+(8 rows)
select max(unique2) from tenk1 order by max(unique2)+1;
max
@@ -1196,7 +1208,8 @@ explain (costs off)
Index Cond: (unique2 IS NOT NULL)
-> ProjectSet
-> Result
-(8 rows)
+ Replaces: MinMaxAggregate
+(9 rows)
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;
max | g
@@ -1212,12 +1225,13 @@ explain (costs off)
QUERY PLAN
----------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Result
One-Time Filter: (100 IS NOT NULL)
-> Seq Scan on tenk1
-(6 rows)
+(7 rows)
select max(100) from tenk1;
max
@@ -1243,6 +1257,7 @@ explain (costs off)
QUERY PLAN
---------------------------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Merge Append
@@ -1265,7 +1280,7 @@ explain (costs off)
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9
-(23 rows)
+(24 rows)
select min(f1), max(f1) from minmaxtest;
min | max
@@ -1304,7 +1319,8 @@ explain (costs off)
-> Sort
Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1)
-> Result
-(26 rows)
+ Replaces: MinMaxAggregate
+(27 rows)
select distinct min(f1), max(f1) from minmaxtest;
min | max
@@ -1334,7 +1350,8 @@ explain (costs off)
-> Seq Scan on int4_tbl t1
Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1))
-> Result
-(9 rows)
+ Replaces: MinMaxAggregate
+(10 rows)
select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1)
from int4_tbl t0;
diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out
index efee7fc4317..4c77f7e3961 100644
--- a/src/test/regress/expected/case.out
+++ b/src/test/regress/expected/case.out
@@ -266,27 +266,30 @@ SELECT *
-- Tests for constant subexpression simplification
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, 2) = 2;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on case_tbl
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, 1) IS NOT NULL;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on case_tbl
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off)
SELECT * FROM CASE_TBL WHERE NULLIF(1, null) = 2;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on case_tbl
One-Time Filter: false
-(2 rows)
+(3 rows)
--
-- Examples of updates involving tables
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index c53bf9c8aa3..7c1f26b182c 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -54,6 +54,14 @@ set jit = off;
-- enabled.
set track_io_timing = off;
-- Simple cases
+explain (costs off) select 1 as a, 2 as b having false;
+ QUERY PLAN
+--------------------------
+ Result
+ Replaces: Aggregate
+ One-Time Filter: false
+(3 rows)
+
select explain_filter('explain select * from int8_tbl i8');
explain_filter
---------------------------------------------------------
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index d8645192351..c861bd36c5a 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1570,17 +1570,18 @@ where coalesce(t2.b, 1) = 2 or t1.a is null;
-- Ensure that the generation expressions are wrapped into PHVs if needed
explain (verbose, costs off)
select t2.* from gtest32 t1 left join gtest32 t2 on false;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Nested Loop Left Join
- Output: a, (a * 2), (20), (COALESCE(a, 100)), e
+ Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e
Join Filter: false
-> Seq Scan on generated_virtual_tests.gtest32 t1
Output: t1.a, t1.b, t1.c, t1.d, t1.e
-> Result
- Output: a, e, 20, COALESCE(a, 100)
+ Output: t2.a, t2.e, 20, COALESCE(t2.a, 100)
+ Replaces: Scan on t2
One-Time Filter: false
-(8 rows)
+(9 rows)
select t2.* from gtest32 t1 left join gtest32 t2 on false;
a | b | c | d | e
@@ -1642,20 +1643,22 @@ set constraint_exclusion to on;
-- should get a dummy Result, not a seq scan
explain (costs off)
select * from gtest33 where b < 10;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Result
+ Replaces: Scan on gtest33
One-Time Filter: false
-(2 rows)
+(3 rows)
-- should get a dummy Result, not a seq scan
explain (costs off)
select * from gtest33 where b is null;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Result
+ Replaces: Scan on gtest33
One-Time Filter: false
-(2 rows)
+(3 rows)
reset constraint_exclusion;
drop table gtest33;
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 35e4cb47ebe..210bbe307a7 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -591,11 +591,12 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 IS NOT NULL)
-(5 rows)
+(6 rows)
-- Views with GROUPING SET queries
CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c)
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 5b5055babdc..031dd87424a 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -580,8 +580,9 @@ update some_tab set a = a + 1 where false;
Update on public.some_tab
-> Result
Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Replaces: Scan on some_tab
One-Time Filter: false
-(4 rows)
+(5 rows)
update some_tab set a = a + 1 where false;
explain (verbose, costs off)
@@ -592,8 +593,9 @@ update some_tab set a = a + 1 where false returning b, a;
Output: some_tab.b, some_tab.a
-> Result
Output: (some_tab.a + 1), NULL::oid, NULL::tid
+ Replaces: Scan on some_tab
One-Time Filter: false
-(5 rows)
+(6 rows)
update some_tab set a = a + 1 where false returning b, a;
b | a
@@ -699,8 +701,9 @@ explain update parted_tab set a = 2 where false;
--------------------------------------------------------
Update on parted_tab (cost=0.00..0.00 rows=0 width=0)
-> Result (cost=0.00..0.00 rows=0 width=10)
+ Replaces: Scan on parted_tab
One-Time Filter: false
-(3 rows)
+(4 rows)
drop table parted_tab;
-- Check UPDATE with multi-level partitioned inherited target
@@ -1756,6 +1759,7 @@ explain (verbose, costs off) select min(1-id) from matest0;
---------------------------------------------------------------------------------
Result
Output: (InitPlan 1).col1
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
Output: ((1 - matest0.id))
@@ -1779,7 +1783,7 @@ explain (verbose, costs off) select min(1-id) from matest0;
-> Index Scan using matest3i on public.matest3 matest0_4
Output: matest0_4.id, (1 - matest0_4.id)
Index Cond: ((1 - matest0_4.id) IS NOT NULL)
-(25 rows)
+(26 rows)
select min(1-id) from matest0;
min
@@ -1943,6 +1947,7 @@ SELECT min(x) FROM
QUERY PLAN
--------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Merge Append
@@ -1951,7 +1956,7 @@ SELECT min(x) FROM
Index Cond: (unique1 IS NOT NULL)
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 IS NOT NULL)
-(9 rows)
+(10 rows)
explain (costs off)
SELECT min(y) FROM
@@ -1961,6 +1966,7 @@ SELECT min(y) FROM
QUERY PLAN
--------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Merge Append
@@ -1969,7 +1975,7 @@ SELECT min(y) FROM
Index Cond: (unique1 IS NOT NULL)
-> Index Only Scan using tenk1_unique2 on tenk1 b
Index Cond: (unique2 IS NOT NULL)
-(9 rows)
+(10 rows)
-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
explain (costs off)
@@ -3087,11 +3093,12 @@ explain (costs off) select * from range_list_parted where a between 3 and 23 and
/* Should select no rows because range partition key cannot be null */
explain (costs off) select * from range_list_parted where a is null;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+---------------------------------------
Result
+ Replaces: Scan on range_list_parted
One-Time Filter: false
-(2 rows)
+(3 rows)
/* Should only select rows from the null-accepting partition */
explain (costs off) select * from range_list_parted where b is null;
@@ -3252,6 +3259,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
QUERY PLAN
------------------------------------------------------------------------------------------------
Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax
@@ -3260,7 +3268,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345';
-> Limit
-> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1
Index Cond: ((a IS NOT NULL) AND (b = '12345'::text))
-(9 rows)
+(10 rows)
select min(a), max(a) from parted_minmax where b = '12345';
min | max
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 04079268b98..cd37f549b5a 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2264,11 +2264,12 @@ explain (costs off)
select aa, bb, unique1, unique1
from tenk1 right join b_star on aa = unique1
where bb < bb and bb is null;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------------
Result
+ Replaces: Join on tenk1, b_star
One-Time Filter: false
-(2 rows)
+(3 rows)
select aa, bb, unique1, unique1
from tenk1 right join b_star on aa = unique1
@@ -2386,11 +2387,12 @@ order by t1.unique1;
Index Cond: (unique1 < 10)
SubPlan 2
-> Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Only Scan using tenk1_unique1 on tenk1
Index Cond: ((unique1 IS NOT NULL) AND (unique1 = t2.unique1))
-(19 rows)
+(20 rows)
-- Ensure we get the expected result
select t1.unique1,t2.unique1 from tenk1 t1
@@ -2655,8 +2657,8 @@ select * from int8_tbl t1 left join
(int8_tbl t2 left join int8_tbl t3 full join int8_tbl t4 on false on false)
left join int8_tbl t5 on t2.q1 = t5.q1
on t2.q2 = 123;
- QUERY PLAN
---------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------
Nested Loop Left Join
-> Seq Scan on int8_tbl t1
-> Materialize
@@ -2667,9 +2669,10 @@ on t2.q2 = 123;
-> Seq Scan on int8_tbl t2
Filter: (q2 = 123)
-> Result
+ Replaces: Join on t3, t4
One-Time Filter: false
-> Seq Scan on int8_tbl t5
-(12 rows)
+(13 rows)
explain (costs off)
select * from int8_tbl t1
@@ -4148,9 +4151,9 @@ select * from t t1
QUERY PLAN
----------------------------------------------------------
Nested Loop Left Join
- Output: t1.i, (1), t2.i2, i3, t4.i4
+ Output: t1.i, (1), t2.i2, t3.i3, t4.i4
-> Nested Loop Left Join
- Output: t1.i, t2.i2, (1), i3
+ Output: t1.i, t2.i2, (1), t3.i3
Join Filter: false
-> Hash Left Join
Output: t1.i, t2.i2, (1)
@@ -4163,7 +4166,8 @@ select * from t t1
-> Seq Scan on pg_temp.t t2
Output: t2.i2, 1
-> Result
- Output: i3
+ Output: t3.i3
+ Replaces: Scan on t3
One-Time Filter: false
-> Memoize
Output: t4.i4
@@ -4172,7 +4176,7 @@ select * from t t1
-> Index Only Scan using t_pkey on pg_temp.t t4
Output: t4.i4
Index Cond: (t4.i4 > (1))
-(25 rows)
+(26 rows)
explain (verbose, costs off)
select * from
@@ -4362,8 +4366,9 @@ from int4_tbl t1
-------------------------------------------------------------------------------------------------------------------
Result
Output: (current_database())::information_schema.sql_identifier, (c.relname)::information_schema.sql_identifier
+ Replaces: Join on t1, t2, a, c, nc, t, nt, bt, nbt
One-Time Filter: false
-(3 rows)
+(4 rows)
-- Test handling of qual pushdown to appendrel members with non-Var outputs
explain (verbose, costs off)
@@ -4435,11 +4440,12 @@ select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1;
explain (costs off)
select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17);
- QUERY PLAN
---------------------------
+ QUERY PLAN
+---------------------------
Result
+ Replaces: Scan on tenk1
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off)
select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x;
@@ -4485,11 +4491,12 @@ select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x;
-- check that pullup of a const function allows further const-folding
explain (costs off)
select unique1 from tenk1, f_immutable_int4(1) x where x = 42;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+---------------------------
Result
+ Replaces: Scan on tenk1
One-Time Filter: false
-(2 rows)
+(3 rows)
-- test inlining of immutable functions with PlaceHolderVars
explain (costs off)
@@ -5345,8 +5352,9 @@ left join
Join Filter: false
-> Result
-> Result
+ Replaces: Join on c, n
One-Time Filter: false
-(5 rows)
+(6 rows)
-- check handling of apparently-commutable outer joins with non-commutable
-- joins between them
@@ -5540,12 +5548,13 @@ select 1 from
right join (select 1 as z) as ss2 on true)
on false,
lateral (select i4.f1, ss1.n from int8_tbl as i8 limit 1) as ss3;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------------------------
Result
Output: 1
+ Replaces: Join on i4, ss3, x1, x2, *RESULT*
One-Time Filter: false
-(3 rows)
+(4 rows)
select 1 from
int4_tbl as i4
@@ -5574,11 +5583,12 @@ select 1 from t t1
on false
where t3.a = coalesce(t5.a,1)) as s2
on true;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------------------
Result
+ Replaces: Join on t1, s1, t2, t3, t4, t5
One-Time Filter: false
-(2 rows)
+(3 rows)
rollback;
--
@@ -5975,14 +5985,15 @@ from int4_tbl as t1
inner join int8_tbl as t7 on null)
on t5.q1 = t7.q2)
on false;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop Left Join
Join Filter: false
-> Seq Scan on int4_tbl t1
-> Result
+ Replaces: Join on t2, t3, t4, t5, t7, t6
One-Time Filter: false
-(5 rows)
+(6 rows)
-- variant with Var rather than PHV coming from t6
explain (costs off)
@@ -5997,14 +6008,15 @@ from int4_tbl as t1
inner join int8_tbl as t7 on null)
on t5.q1 = t7.q2)
on false;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop Left Join
Join Filter: false
-> Seq Scan on int4_tbl t1
-> Result
+ Replaces: Join on t2, t3, t4, t5, t7, t6
One-Time Filter: false
-(5 rows)
+(6 rows)
-- per further discussion of bug #17781
explain (costs off)
@@ -6054,15 +6066,16 @@ select * from int8_tbl t1 left join
(int8_tbl t2 inner join int8_tbl t3 on false
left join int8_tbl t4 on t2.q2 = t4.q2)
on t1.q1 = t2.q1;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+--------------------------------------------
Hash Left Join
- Hash Cond: (t1.q1 = q1)
+ Hash Cond: (t1.q1 = t2.q1)
-> Seq Scan on int8_tbl t1
-> Hash
-> Result
+ Replaces: Join on t2, t3, t4
One-Time Filter: false
-(6 rows)
+(7 rows)
-- deduce constant-false from an EquivalenceClass
explain (costs off)
@@ -6070,15 +6083,16 @@ select * from int8_tbl t1 left join
(int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) = 1
left join int8_tbl t4 on t2.q2 = t4.q2)
on t1.q1 = t2.q1;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+--------------------------------------------
Hash Left Join
- Hash Cond: (t1.q1 = q1)
+ Hash Cond: (t1.q1 = t2.q1)
-> Seq Scan on int8_tbl t1
-> Hash
-> Result
+ Replaces: Join on t2, t3, t4
One-Time Filter: false
-(6 rows)
+(7 rows)
-- pseudoconstant based on an outer-level Param
explain (costs off)
@@ -6317,8 +6331,9 @@ select p.* from
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on p
One-Time Filter: false
-(2 rows)
+(3 rows)
select p.* from
(parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k
@@ -6334,8 +6349,9 @@ select p.* from
QUERY PLAN
--------------------------
Result
+ Replaces: Join on p, x
One-Time Filter: false
-(2 rows)
+(3 rows)
-- bug 5255: this is not optimizable by join removal
begin;
@@ -6400,15 +6416,16 @@ SELECT q2 FROM
QUERY PLAN
------------------------------------------------------
Nested Loop Left Join
- Output: q2
+ Output: int8_tbl.q2
Join Filter: NULL::boolean
Filter: (('constant'::text) >= ('constant'::text))
-> Seq Scan on public.int4_tbl
Output: int4_tbl.f1
-> Result
- Output: q2, 'constant'::text
+ Output: int8_tbl.q2, 'constant'::text
+ Replaces: Scan on int8_tbl
One-Time Filter: false
-(9 rows)
+(10 rows)
-- join removal bug #17786: check that OR conditions are cleaned up
EXPLAIN (COSTS OFF)
@@ -6427,8 +6444,9 @@ FROM int4_tbl
Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42)))
-> Seq Scan on tenk1
-> Result
+ Replaces: Scan on int8_tbl
One-Time Filter: false
-(9 rows)
+(10 rows)
rollback;
-- another join removal bug: we must clean up correctly when removing a PHV
@@ -6878,8 +6896,9 @@ where q1.x = q2.y;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on sj
One-Time Filter: false
-(2 rows)
+(3 rows)
-- We can't use a cross-EC generated self join qual because of current logic of
-- the generate_join_implied_equalities routine.
@@ -7703,11 +7722,12 @@ select 1 from emp1 full join
on true
where false) s on true
where false;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------------
Result
+ Replaces: Join on emp1, t1, t3
One-Time Filter: false
-(2 rows)
+(3 rows)
select 1 from emp1 full join
(select * from emp1 t1 join
@@ -7936,8 +7956,9 @@ where false;
--------------------------
Result
Output: 1
+ Replaces: Scan on ss
One-Time Filter: false
-(3 rows)
+(4 rows)
--
-- Test LATERAL
@@ -8866,31 +8887,33 @@ select * from int4_tbl t1,
explain (verbose, costs off)
select * from int8_tbl i8 left join lateral
(select *, i8.q2 from int4_tbl where false) ss on true;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+----------------------------------------------
Nested Loop Left Join
- Output: i8.q1, i8.q2, f1, (i8.q2)
+ Output: i8.q1, i8.q2, int4_tbl.f1, (i8.q2)
Join Filter: false
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
-> Result
- Output: f1, i8.q2
+ Output: int4_tbl.f1, i8.q2
+ Replaces: Scan on int4_tbl
One-Time Filter: false
-(8 rows)
+(9 rows)
explain (verbose, costs off)
select * from int8_tbl i8 left join lateral
(select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true;
- QUERY PLAN
------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Nested Loop Left Join
- Output: i8.q1, i8.q2, f1, f1, (i8.q2)
+ Output: i8.q1, i8.q2, i1.f1, i2.f1, (i8.q2)
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
-> Result
- Output: f1, f1, i8.q2
+ Output: i1.f1, i2.f1, i8.q2
+ Replaces: Join on i1, i2
One-Time Filter: false
-(7 rows)
+(8 rows)
-- check handling of nested appendrels inside LATERAL
select * from
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index cf2219df754..44df626c40c 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -2426,8 +2426,9 @@ MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
Output: t.tid, t.ctid
-> Result
Output: t.tid, t.ctid
+ Replaces: Scan on t
One-Time Filter: false
-(12 rows)
+(13 rows)
MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index 5f2c0cf5786..cb12bf53719 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -146,13 +146,14 @@ SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c;
-- Test when input relation for grouping is dummy
EXPLAIN (COSTS OFF)
SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+------------------------------------
HashAggregate
Group Key: c
-> Result
+ Replaces: Scan on pagg_tab
One-Time Filter: false
-(4 rows)
+(5 rows)
SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c;
c | sum
@@ -161,12 +162,13 @@ SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c;
EXPLAIN (COSTS OFF)
SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+------------------------------------
GroupAggregate
-> Result
+ Replaces: Scan on pagg_tab
One-Time Filter: false
-(3 rows)
+(4 rows)
SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c;
c | sum
@@ -804,15 +806,16 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI
-- Empty join relation because of empty outer side, no partitionwise agg plan
EXPLAIN (COSTS OFF)
SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+----------------------------------------------
GroupAggregate
Group Key: pagg_tab1.y
-> Sort
Sort Key: pagg_tab1.y
-> Result
+ Replaces: Join on b, pagg_tab1
One-Time Filter: false
-(6 rows)
+(7 rows)
SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2;
x | y | count
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 24e06845f92..713828be335 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1609,26 +1609,28 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, pl
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------
Result
+ Replaces: Join on t1, t2
One-Time Filter: false
-(2 rows)
+(3 rows)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Join on t2, prt1
One-Time Filter: false
-(2 rows)
+(3 rows)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a;
QUERY PLAN
--------------------------------------------------
Hash Left Join
- Hash Cond: (t2.b = a)
+ Hash Cond: (t2.b = prt1.a)
-> Append
-> Hash Join
Hash Cond: (t3_1.a = t2_1.b)
@@ -1647,17 +1649,18 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
-> Seq Scan on prt2_p3 t2_3
-> Hash
-> Result
+ Replaces: Scan on prt1
One-Time Filter: false
-(21 rows)
+(22 rows)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
--------------------------------------------
Sort
- Sort Key: a, t2.b
+ Sort Key: prt1.a, t2.b
-> Hash Left Join
- Hash Cond: (t2.b = a)
+ Hash Cond: (t2.b = prt1.a)
-> Append
-> Seq Scan on prt2_p1 t2_1
Filter: (a = 0)
@@ -1667,8 +1670,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
Filter: (a = 0)
-> Hash
-> Result
+ Replaces: Scan on prt1
One-Time Filter: false
-(14 rows)
+(15 rows)
--
-- tests for hash partitioned tables.
@@ -2242,10 +2246,10 @@ SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL
-- join with one side empty
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
Hash Left Join
- Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text))
+ Hash Cond: ((t2.b = prt1_l.a) AND (t2.a = prt1_l.b) AND ((t2.c)::text = (prt1_l.c)::text))
-> Append
-> Seq Scan on prt2_l_p1 t2_1
-> Seq Scan on prt2_l_p2_p1 t2_2
@@ -2254,8 +2258,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
-> Seq Scan on prt2_l_p3_p2 t2_5
-> Hash
-> Result
+ Replaces: Scan on prt1_l
One-Time Filter: false
-(11 rows)
+(12 rows)
-- Test case to verify proper handling of subqueries in a partitioned delete.
-- The weird-looking lateral join is just there to force creation of a
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 68ecd951809..7499cdb2cdf 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -627,8 +627,9 @@ explain (costs off) select * from rlp3 where a = 20; /* empty */
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on rlp3
One-Time Filter: false
-(2 rows)
+(3 rows)
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
@@ -670,8 +671,9 @@ explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on rlp
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
QUERY PLAN
@@ -1254,25 +1256,28 @@ select * from boolpart where a is not unknown;
-- check that all partitions are pruned when faced with conflicting clauses
explain (costs off) select * from boolpart where a is not unknown and a is unknown;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on boolpart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from boolpart where a is false and a is unknown;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on boolpart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from boolpart where a is true and a is unknown;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------
Result
+ Replaces: Scan on boolpart
One-Time Filter: false
-(2 rows)
+(3 rows)
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
-- code for it, so we'd better test it.
@@ -1568,11 +1573,12 @@ explain (costs off) select * from coercepart where a = any ('{ab,null}');
(2 rows)
explain (costs off) select * from coercepart where a = any (null::text[]);
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on coercepart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from coercepart where a = all ('{ab}');
QUERY PLAN
@@ -1582,25 +1588,28 @@ explain (costs off) select * from coercepart where a = all ('{ab}');
(2 rows)
explain (costs off) select * from coercepart where a = all ('{ab,bc}');
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on coercepart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from coercepart where a = all ('{ab,null}');
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on coercepart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from coercepart where a = all (null::text[]);
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on coercepart
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table coercepart;
CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a);
@@ -1772,8 +1781,9 @@ explain (costs off) select * from lp where a <> 'a' and a is null;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on lp
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
QUERY PLAN
@@ -1866,22 +1876,24 @@ create table lparted_by_int2 (a smallint) partition by list (a);
create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384);
explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------------
Result
+ Replaces: Scan on lparted_by_int2
One-Time Filter: false
-(2 rows)
+(3 rows)
create table rparted_by_int2 (a smallint) partition by range (a);
create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10);
create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384);
-- all partitions pruned
explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------------
Result
+ Replaces: Scan on rparted_by_int2
One-Time Filter: false
-(2 rows)
+(3 rows)
create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue);
-- all partitions but rparted_by_int2_maxvalue pruned
@@ -2131,8 +2143,9 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde' and
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on hp
One-Time Filter: false
-(2 rows)
+(3 rows)
--
-- Test runtime partition pruning
@@ -3390,11 +3403,12 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
explain (analyze, costs off, summary off, timing off, buffers off)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+-----------------------------------------
Result (actual rows=0.00 loops=1)
+ Replaces: Scan on stable_qual_pruning
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (analyze, costs off, summary off, timing off, buffers off)
select * from stable_qual_pruning
@@ -3642,6 +3656,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from
Sort Key: ma_test.b
InitPlan 2
-> Result (actual rows=1.00 loops=1)
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit (actual rows=1.00 loops=1)
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1)
@@ -3656,7 +3671,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1)
Filter: (a >= (InitPlan 2).col1)
Index Searches: 1
-(18 rows)
+(19 rows)
reset enable_seqscan;
reset enable_sort;
@@ -3678,11 +3693,12 @@ explain (costs off) select * from pp_arrpart where a = '{1}';
(2 rows)
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on pp_arrpart
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
QUERY PLAN
@@ -3764,11 +3780,12 @@ explain (costs off) select * from pp_enumpart where a = 'blue';
(2 rows)
explain (costs off) select * from pp_enumpart where a = 'black';
- QUERY PLAN
---------------------------
+ QUERY PLAN
+---------------------------------
Result
+ Replaces: Scan on pp_enumpart
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table pp_enumpart;
drop type pp_colors;
@@ -3785,11 +3802,12 @@ explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
(2 rows)
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+--------------------------------
Result
+ Replaces: Scan on pp_recpart
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table pp_recpart;
drop type pp_rectype;
@@ -3805,11 +3823,12 @@ explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
(2 rows)
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------------
Result
+ Replaces: Scan on pp_intrangepart
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table pp_intrangepart;
--
@@ -4125,19 +4144,21 @@ explain (costs off) update listp1 set a = 1 where a = 2;
-- constraint exclusion enabled
set constraint_exclusion to 'on';
explain (costs off) select * from listp1 where a = 2;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------
Result
+ Replaces: Scan on listp1
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) update listp1 set a = 1 where a = 2;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+----------------------------------
Update on listp1
-> Result
+ Replaces: Scan on listp1
One-Time Filter: false
-(3 rows)
+(4 rows)
reset constraint_exclusion;
reset enable_partition_pruning;
@@ -4524,18 +4545,20 @@ create table hp_contradict_test (a int, b int) partition by hash (a part_test_in
create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0);
create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1);
explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------------------
Result
+ Replaces: Scan on hp_contradict_test
One-Time Filter: false
-(2 rows)
+(3 rows)
explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+----------------------------------------
Result
+ Replaces: Scan on hp_contradict_test
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table hp_contradict_test;
drop operator class part_test_int4_ops2 using hash;
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 1aff0b59ff8..304b6868b90 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -36,8 +36,9 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on t
One-Time Filter: false
-(2 rows)
+(3 rows)
-- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable
-- columns
@@ -77,8 +78,9 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on t
One-Time Filter: false
-(2 rows)
+(3 rows)
-- Ensure the OR clause is not reduced to constant-FALSE when not all branches
-- are provably false
@@ -139,8 +141,9 @@ SELECT * FROM pred_tab t1
Join Filter: false
-> Seq Scan on pred_tab t1
-> Result
+ Replaces: Scan on t2
One-Time Filter: false
-(5 rows)
+(6 rows)
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
-- nullable by an outer join
@@ -209,8 +212,9 @@ SELECT * FROM pred_tab t1
Join Filter: false
-> Seq Scan on pred_tab t1
-> Result
+ Replaces: Scan on t2
One-Time Filter: false
-(5 rows)
+(6 rows)
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
-- made nullable from an outer join
@@ -267,17 +271,18 @@ SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON EXISTS
(SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6
WHERE t1.a = t3.a AND t6.a IS NULL);
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------------------------
Nested Loop Left Join
Join Filter: (InitPlan 1).col1
InitPlan 1
-> Result
+ Replaces: Join on t3, t4, t5, t6
One-Time Filter: false
-> Seq Scan on pred_tab t1
-> Materialize
-> Seq Scan on pred_tab t2
-(8 rows)
+(9 rows)
DROP TABLE pred_tab;
-- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
@@ -418,20 +423,22 @@ SET constraint_exclusion TO ON;
-- Ensure that we get a dummy plan
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab1, pred_tab2 WHERE pred_tab2.a IS NULL;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------------------
Result
+ Replaces: Join on pred_tab1, pred_tab2
One-Time Filter: false
-(2 rows)
+(3 rows)
-- Ensure that we get a dummy plan
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab2, pred_tab1 WHERE pred_tab1.a IS NULL OR pred_tab1.b < 2;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+------------------------------------------
Result
+ Replaces: Join on pred_tab2, pred_tab1
One-Time Filter: false
-(2 rows)
+(3 rows)
RESET constraint_exclusion;
DROP TABLE pred_tab1;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 8c879509313..7153ebba521 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -3606,8 +3606,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on t1
One-Time Filter: false
-(2 rows)
+(3 rows)
SET SESSION AUTHORIZATION regress_rls_bob;
SELECT * FROM t1;
@@ -3619,8 +3620,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1;
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on t1
One-Time Filter: false
-(2 rows)
+(3 rows)
--
-- COPY TO/FROM
@@ -4524,11 +4526,12 @@ SELECT * FROM rls_tbl WHERE a <<< 1000;
(0 rows)
EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-----------------------------
Result
+ Replaces: Scan on rls_tbl
One-Time Filter: false
-(2 rows)
+(3 rows)
RESET SESSION AUTHORIZATION;
CREATE TABLE rls_child_tbl () INHERITS (rls_tbl);
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index d84122881af..dd52d96d50f 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1325,8 +1325,9 @@ where false;
--------------------------
Result
Output: (a).f1, (a).f2
+ Replaces: Scan on ss
One-Time Filter: false
-(3 rows)
+(4 rows)
explain (verbose, costs off)
with cte(c) as materialized (select row(1, 2)),
@@ -1350,11 +1351,12 @@ where false;
-----------------------------------
Result
Output: (cte.c).f1
+ Replaces: Scan on cte
One-Time Filter: false
CTE cte
-> Result
Output: '(1,2)'::record
-(6 rows)
+(7 rows)
--
-- Tests for component access / FieldSelect
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index bab0cc93ff5..2cdb78d1e9a 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -962,10 +962,11 @@ create table list_parted_tbl (a int,b int) partition by list (a);
create table list_parted_tbl1 partition of list_parted_tbl
for values in (1) partition by list(b);
explain (costs off) select * from list_parted_tbl;
- QUERY PLAN
---------------------------
+ QUERY PLAN
+-------------------------------------
Result
+ Replaces: Scan on list_parted_tbl
One-Time Filter: false
-(2 rows)
+(3 rows)
drop table list_parted_tbl;
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 76ffb2b9027..47b2af7b2e1 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1998,17 +1998,18 @@ create temp table json_tab (a int);
insert into json_tab values (1);
explain (verbose, costs off)
select * from json_tab t1 left join (select json_array(1, a) from json_tab t2) s on false;
- QUERY PLAN
----------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Nested Loop Left Join
- Output: t1.a, (JSON_ARRAY(1, a RETURNING json))
+ Output: t1.a, (JSON_ARRAY(1, t2.a RETURNING json))
Join Filter: false
-> Seq Scan on pg_temp.json_tab t1
Output: t1.a
-> Result
- Output: JSON_ARRAY(1, a RETURNING json)
+ Output: JSON_ARRAY(1, t2.a RETURNING json)
+ Replaces: Scan on t2
One-Time Filter: false
-(8 rows)
+(9 rows)
select * from json_tab t1 left join (select json_array(1, a) from json_tab t2) s on false;
a | json_array
@@ -2899,12 +2900,13 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
-> Subquery Scan on unnamed_subquery
Filter: (b.hundred = unnamed_subquery.min)
-> Result
+ Replaces: MinMaxAggregate
InitPlan 1
-> Limit
-> Index Scan using tenk2_hundred on tenk2 c
Index Cond: (hundred IS NOT NULL)
Filter: (odd = b.odd)
-(16 rows)
+(17 rows)
--
-- Test VALUES to ARRAY (VtA) transformation
@@ -3067,8 +3069,9 @@ EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL);
QUERY PLAN
--------------------------
Result
+ Replaces: Scan on onek
One-Time Filter: false
-(2 rows)
+(3 rows)
EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5');
QUERY PLAN
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index d47b5f6ec57..c4f7b187f5b 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -91,8 +91,9 @@ SELECT unnest(ARRAY[1, 2]) FROM few WHERE false;
ProjectSet
Output: unnest('{1,2}'::integer[])
-> Result
+ Replaces: Scan on few
One-Time Filter: false
-(4 rows)
+(5 rows)
SELECT unnest(ARRAY[1, 2]) FROM few WHERE false;
unnest
@@ -107,8 +108,9 @@ SELECT * FROM few f1,
------------------------------------------------
Result
Output: f1.id, f1.dataa, f1.datab, ss.unnest
+ Replaces: Join on f1, ss
One-Time Filter: false
-(3 rows)
+(4 rows)
SELECT * FROM few f1,
(SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss;
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 7842d25ded3..ebdab42604b 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -61,6 +61,7 @@ set track_io_timing = off;
-- Simple cases
+explain (costs off) select 1 as a, 2 as b having false;
select explain_filter('explain select * from int8_tbl i8');
select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8');
select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8');
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-24 14:23:39 +0000