From ef85c8213d5fbc04a7d641c506675a055004a2fb Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Thu, 26 Sep 2019 12:02:11 -0700 Subject: [PATCH v2 3/8] Explain: Differentiate between a node projecting or not. Author: Reviewed-By: Discussion: https://postgr.es/m/ Backpatch: --- src/backend/commands/explain.c | 5 +- src/test/regress/expected/aggregates.out | 12 +- src/test/regress/expected/alter_table.out | 12 +- .../regress/expected/create_function_3.out | 6 +- src/test/regress/expected/domain.out | 12 +- src/test/regress/expected/fast_default.out | 10 +- src/test/regress/expected/inherit.out | 64 ++-- src/test/regress/expected/join.out | 280 +++++++++--------- src/test/regress/expected/join_hash.out | 40 +-- src/test/regress/expected/limit.out | 22 +- src/test/regress/expected/plpgsql.out | 14 +- src/test/regress/expected/rangefuncs.out | 10 +- src/test/regress/expected/rowsecurity.out | 4 +- src/test/regress/expected/rowtypes.out | 8 +- src/test/regress/expected/select_distinct.out | 10 +- src/test/regress/expected/select_parallel.out | 14 +- src/test/regress/expected/subselect.out | 118 ++++---- src/test/regress/expected/tsrf.out | 24 +- src/test/regress/expected/updatable_views.out | 30 +- src/test/regress/expected/update.out | 10 +- src/test/regress/expected/with.out | 30 +- src/test/regress/expected/xml.out | 8 +- src/test/regress/expected/xml_2.out | 8 +- 23 files changed, 377 insertions(+), 374 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 48283ba82a6..ea6b39d5abb 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2140,7 +2140,10 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es) } /* Print results */ - ExplainPropertyList("Output", result, es); + if (planstate->ps_ProjInfo) + ExplainPropertyList("Project", result, es); + else + ExplainPropertyList("Output", result, es); } /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index be4ddf86a43..683bcaedf5f 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -510,12 +510,12 @@ order by 1, 2; Output: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) Sort Key: s1.s1, s2.s2 -> Nested Loop - Output: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) + Project: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) -> Function Scan on pg_catalog.generate_series s1 Output: s1.s1 Function Call: generate_series(1, 3) -> HashAggregate - Output: s2.s2, sum((s1.s1 + s2.s2)) + Project: s2.s2, sum((s1.s1 + s2.s2)) Group Key: s2.s2 -> Function Scan on pg_catalog.generate_series s2 Output: s2.s2 @@ -547,14 +547,14 @@ select array(select sum(x+y) s QUERY PLAN ------------------------------------------------------------------- Function Scan on pg_catalog.generate_series x - Output: (SubPlan 1) + Project: (SubPlan 1) Function Call: generate_series(1, 3) SubPlan 1 -> Sort Output: (sum((x.x + y.y))), y.y Sort Key: (sum((x.x + y.y))) -> HashAggregate - Output: sum((x.x + y.y)), y.y + Project: sum((x.x + y.y)), y.y Group Key: y.y -> Function Scan on pg_catalog.generate_series y Output: y.y @@ -2253,12 +2253,12 @@ EXPLAIN (COSTS OFF, VERBOSE) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate - Output: variance(unique1), sum((unique1)::bigint), regr_count((unique1)::double precision, (unique1)::double precision) + Project: variance(unique1), sum((unique1)::bigint), regr_count((unique1)::double precision, (unique1)::double precision) -> Gather Output: (PARTIAL variance(unique1)), (PARTIAL sum((unique1)::bigint)), (PARTIAL regr_count((unique1)::double precision, (unique1)::double precision)) Workers Planned: 4 -> Partial Aggregate - Output: PARTIAL variance(unique1), PARTIAL sum((unique1)::bigint), PARTIAL regr_count((unique1)::double precision, (unique1)::double precision) + Project: PARTIAL variance(unique1), PARTIAL sum((unique1)::bigint), PARTIAL regr_count((unique1)::double precision, (unique1)::double precision) -> Parallel Seq Scan on public.tenk1 Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4 (9 rows) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 5189fd81887..bebd41b5f5b 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2381,10 +2381,10 @@ View definition: FROM at_view_1 v1; explain (verbose, costs off) select * from at_view_2; - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------- Seq Scan on public.at_base_table bt - Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff)) + Project: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff)) (2 rows) select * from at_view_2; @@ -2421,10 +2421,10 @@ View definition: FROM at_view_1 v1; explain (verbose, costs off) select * from at_view_2; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Seq Scan on public.at_base_table bt - Output: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff, NULL)) + Project: bt.id, bt.stuff, to_json(ROW(bt.id, bt.stuff, NULL)) (2 rows) select * from at_view_2; diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out index ba260df9960..4def18f0e0b 100644 --- a/src/test/regress/expected/create_function_3.out +++ b/src/test/regress/expected/create_function_3.out @@ -303,10 +303,10 @@ SELECT voidtest2(11,22); -- currently, we can inline voidtest2 but not voidtest1 EXPLAIN (verbose, costs off) SELECT voidtest2(11,22); - QUERY PLAN -------------------------- + QUERY PLAN +-------------------------- Result - Output: voidtest1(33) + Project: voidtest1(33) (2 rows) CREATE TEMP TABLE sometable(f1 int); diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 4ff1b4af418..346ccac9279 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -261,11 +261,11 @@ select * from dcomptable; explain (verbose, costs off) update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; - QUERY PLAN ------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------ Update on public.dcomptable -> Seq Scan on public.dcomptable - Output: ROW(((d1).r - '1'::double precision), ((d1).i + '1'::double precision)), ctid + Project: ROW(((d1).r - '1'::double precision), ((d1).i + '1'::double precision)), ctid Filter: ((dcomptable.d1).i > '0'::double precision) (4 rows) @@ -397,11 +397,11 @@ select * from dcomptable; explain (verbose, costs off) update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 where d1[1].i > 0; - QUERY PLAN ----------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------- Update on public.dcomptable -> Seq Scan on public.dcomptable - Output: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), ctid + Project: (d1[1].r := (d1[1].r - '1'::double precision))[1].i := (d1[1].i + '1'::double precision), ctid Filter: (dcomptable.d1[1].i > '0'::double precision) (4 rows) diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index 10bc5ff757c..177f8911a94 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -300,7 +300,7 @@ SELECT c_bigint, c_text FROM T WHERE c_bigint = -1 LIMIT 1; Limit Output: c_bigint, c_text -> Seq Scan on fast_default.t - Output: c_bigint, c_text + Project: c_bigint, c_text Filter: (t.c_bigint = '-1'::integer) (5 rows) @@ -316,7 +316,7 @@ EXPLAIN (VERBOSE TRUE, COSTS FALSE) SELECT c_bigint, c_text FROM T WHERE c_text Limit Output: c_bigint, c_text -> Seq Scan on fast_default.t - Output: c_bigint, c_text + Project: c_bigint, c_text Filter: (t.c_text = 'hello'::text) (5 rows) @@ -371,7 +371,7 @@ SELECT * FROM T ORDER BY c_bigint, c_text, pk LIMIT 10; Output: pk, c_bigint, c_text Sort Key: t.c_bigint, t.c_text, t.pk -> Seq Scan on fast_default.t - Output: pk, c_bigint, c_text + Project: pk, c_bigint, c_text (7 rows) -- LIMIT @@ -400,7 +400,7 @@ SELECT * FROM T WHERE c_bigint > -1 ORDER BY c_bigint, c_text, pk LIMIT 10; Output: pk, c_bigint, c_text Sort Key: t.c_bigint, t.c_text, t.pk -> Seq Scan on fast_default.t - Output: pk, c_bigint, c_text + Project: pk, c_bigint, c_text Filter: (t.c_bigint > '-1'::integer) (8 rows) @@ -428,7 +428,7 @@ DELETE FROM T WHERE pk BETWEEN 10 AND 20 RETURNING *; Delete on fast_default.t Output: pk, c_bigint, c_text -> Bitmap Heap Scan on fast_default.t - Output: ctid + Project: ctid Recheck Cond: ((t.pk >= 10) AND (t.pk <= 20)) -> Bitmap Index Scan on t_pkey Index Cond: ((t.pk >= 10) AND (t.pk <= 20)) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 44d51ed7110..4b8351839a8 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -545,25 +545,25 @@ create table some_tab_child () inherits (some_tab); insert into some_tab_child values(1,2); explain (verbose, costs off) update some_tab set a = a + 1 where false; - QUERY PLAN ----------------------------------- + QUERY PLAN +----------------------------------- Update on public.some_tab Update on public.some_tab -> Result - Output: (a + 1), b, ctid + Project: (a + 1), b, ctid One-Time Filter: false (5 rows) update some_tab set a = a + 1 where false; explain (verbose, costs off) update some_tab set a = a + 1 where false returning b, a; - QUERY PLAN ----------------------------------- + QUERY PLAN +----------------------------------- Update on public.some_tab Output: b, a Update on public.some_tab -> Result - Output: (a + 1), b, ctid + Project: (a + 1), b, ctid One-Time Filter: false (6 rows) @@ -792,17 +792,17 @@ select NULL::derived::base; -- remove redundant conversions. explain (verbose on, costs off) select row(i, b)::more_derived::derived::base from more_derived; - QUERY PLAN -------------------------------------------- + QUERY PLAN +-------------------------------------------- Seq Scan on public.more_derived - Output: (ROW(i, b)::more_derived)::base + Project: (ROW(i, b)::more_derived)::base (2 rows) explain (verbose on, costs off) select (1, 2)::more_derived::derived::base; - QUERY PLAN ------------------------ + QUERY PLAN +------------------------ Result - Output: '(1)'::base + Project: '(1)'::base (2 rows) drop table more_derived; @@ -1405,13 +1405,13 @@ insert into matest3 (name) values ('Test 5'); insert into matest3 (name) values ('Test 6'); set enable_indexscan = off; -- force use of seqscan/sort, so no merge explain (verbose, costs off) select * from matest0 order by 1-id; - QUERY PLAN ------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------- Sort Output: matest0.id, matest0.name, ((1 - matest0.id)) Sort Key: ((1 - matest0.id)) -> Result - Output: matest0.id, matest0.name, (1 - matest0.id) + Project: matest0.id, matest0.name, (1 - matest0.id) -> Append -> Seq Scan on public.matest0 Output: matest0.id, matest0.name @@ -1438,16 +1438,16 @@ explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN ---------------------------------------- Aggregate - Output: min((1 - matest0.id)) + Project: min((1 - matest0.id)) -> Append -> Seq Scan on public.matest0 - Output: matest0.id + Project: matest0.id -> Seq Scan on public.matest1 - Output: matest1.id + Project: matest1.id -> Seq Scan on public.matest2 - Output: matest2.id + Project: matest2.id -> Seq Scan on public.matest3 - Output: matest3.id + Project: matest3.id (11 rows) select min(1-id) from matest0; @@ -1460,21 +1460,21 @@ reset enable_indexscan; set enable_seqscan = off; -- plan with fewest seqscans should be merge set enable_parallel_append = off; -- Don't let parallel-append interfere explain (verbose, costs off) select * from matest0 order by 1-id; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Merge Append Sort Key: ((1 - matest0.id)) -> Index Scan using matest0i on public.matest0 - Output: matest0.id, matest0.name, (1 - matest0.id) + Project: matest0.id, matest0.name, (1 - matest0.id) -> Index Scan using matest1i on public.matest1 - Output: matest1.id, matest1.name, (1 - matest1.id) + Project: matest1.id, matest1.name, (1 - matest1.id) -> Sort Output: matest2.id, matest2.name, ((1 - matest2.id)) Sort Key: ((1 - matest2.id)) -> Seq Scan on public.matest2 - Output: matest2.id, matest2.name, (1 - matest2.id) + Project: matest2.id, matest2.name, (1 - matest2.id) -> Index Scan using matest3i on public.matest3 - Output: matest3.id, matest3.name, (1 - matest3.id) + Project: matest3.id, matest3.name, (1 - matest3.id) (13 rows) select * from matest0 order by 1-id; @@ -1492,29 +1492,29 @@ explain (verbose, costs off) select min(1-id) from matest0; QUERY PLAN -------------------------------------------------------------------------- Result - Output: $0 + Project: $0 InitPlan 1 (returns $0) -> Limit Output: ((1 - matest0.id)) -> Result - Output: ((1 - matest0.id)) + Project: ((1 - matest0.id)) -> Merge Append Sort Key: ((1 - matest0.id)) -> Index Scan using matest0i on public.matest0 - Output: matest0.id, (1 - matest0.id) + Project: matest0.id, (1 - matest0.id) Index Cond: ((1 - matest0.id) IS NOT NULL) -> Index Scan using matest1i on public.matest1 - Output: matest1.id, (1 - matest1.id) + Project: matest1.id, (1 - matest1.id) Index Cond: ((1 - matest1.id) IS NOT NULL) -> Sort Output: matest2.id, ((1 - matest2.id)) Sort Key: ((1 - matest2.id)) -> Bitmap Heap Scan on public.matest2 - Output: matest2.id, (1 - matest2.id) + Project: matest2.id, (1 - matest2.id) Filter: ((1 - matest2.id) IS NOT NULL) -> Bitmap Index Scan on matest2_pkey -> Index Scan using matest3i on public.matest3 - Output: matest3.id, (1 - matest3.id) + Project: matest3.id, (1 - matest3.id) Index Cond: ((1 - matest3.id) IS NOT NULL) (25 rows) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index b58d560163b..7f319a79938 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3264,9 +3264,9 @@ where x = unique1; QUERY PLAN ----------------------------------------------------------- Nested Loop - Output: tenk1.unique1, (1), (random()) + Project: tenk1.unique1, (1), (random()) -> Result - Output: 1, random() + Project: 1, random() -> Index Only Scan using tenk1_unique1 on public.tenk1 Output: tenk1.unique1 Index Cond: (tenk1.unique1 = (1)) @@ -3740,14 +3740,14 @@ using (join_key); QUERY PLAN -------------------------------------------------------------------------- Nested Loop Left Join - Output: "*VALUES*".column1, i1.f1, (666) + Project: "*VALUES*".column1, i1.f1, (666) Join Filter: ("*VALUES*".column1 = i1.f1) -> Values Scan on "*VALUES*" Output: "*VALUES*".column1 -> Materialize Output: i1.f1, (666) -> Nested Loop Left Join - Output: i1.f1, 666 + Project: i1.f1, 666 -> Seq Scan on public.int4_tbl i1 Output: i1.f1 -> Index Only Scan using tenk1_unique2 on public.tenk1 i2 @@ -3787,34 +3787,34 @@ select t1.* from on (t1.f1 = b1.d1) left join int4_tbl i4 on (i8.q2 = i4.f1); - QUERY PLAN ----------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Hash Left Join - Output: t1.f1 + Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join - Output: t1.f1, i8.q2 + Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize Output: i8.q2 -> Hash Right Join - Output: i8.q2 + Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) -> Hash Join - Output: i8.q2, (NULL::integer) + Project: i8.q2, (NULL::integer) Hash Cond: (i8.q1 = i8b2.q1) -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Hash Output: i8b2.q1, (NULL::integer) -> Seq Scan on public.int8_tbl i8b2 - Output: i8b2.q1, NULL::integer + Project: i8b2.q1, NULL::integer -> Hash Output: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 - Output: i8b1.q2 + Project: i8b1.q2 -> Hash Output: i4.f1 -> Seq Scan on public.int4_tbl i4 @@ -3851,23 +3851,23 @@ select t1.* from QUERY PLAN ---------------------------------------------------------------------------- Hash Left Join - Output: t1.f1 + Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join - Output: t1.f1, i8.q2 + Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize Output: i8.q2 -> Hash Right Join - Output: i8.q2 + Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) -> Hash Right Join - Output: i8.q2, (NULL::integer) + Project: i8.q2, (NULL::integer) Hash Cond: (i8b2.q1 = i8.q1) -> Nested Loop - Output: i8b2.q1, NULL::integer + Project: i8b2.q1, NULL::integer -> Seq Scan on public.int8_tbl i8b2 Output: i8b2.q1, i8b2.q2 -> Materialize @@ -3879,7 +3879,7 @@ select t1.* from -> Hash Output: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 - Output: i8b1.q2 + Project: i8b1.q2 -> Hash Output: i4.f1 -> Seq Scan on public.int4_tbl i4 @@ -3917,23 +3917,23 @@ select t1.* from QUERY PLAN ---------------------------------------------------------------------------- Hash Left Join - Output: t1.f1 + Project: t1.f1 Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join - Output: t1.f1, i8.q2 + Project: t1.f1, i8.q2 Join Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize Output: i8.q2 -> Hash Right Join - Output: i8.q2 + Project: i8.q2 Hash Cond: ((NULL::integer) = i8b1.q2) -> Hash Right Join - Output: i8.q2, (NULL::integer) + Project: i8.q2, (NULL::integer) Hash Cond: (i8b2.q1 = i8.q1) -> Hash Join - Output: i8b2.q1, NULL::integer + Project: i8b2.q1, NULL::integer Hash Cond: (i8b2.q1 = i4b2.f1) -> Seq Scan on public.int8_tbl i8b2 Output: i8b2.q1, i8b2.q2 @@ -3948,7 +3948,7 @@ select t1.* from -> Hash Output: i8b1.q2 -> Seq Scan on public.int8_tbl i8b1 - Output: i8b1.q2 + Project: i8b1.q2 -> Hash Output: i4.f1 -> Seq Scan on public.int4_tbl i4 @@ -3984,15 +3984,15 @@ select * from QUERY PLAN -------------------------------------------------------- Nested Loop Left Join - Output: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1 + Project: t1.f1, i8.q1, i8.q2, t2.f1, i4.f1 -> Seq Scan on public.text_tbl t2 Output: t2.f1 -> Materialize Output: i8.q1, i8.q2, i4.f1, t1.f1 -> Nested Loop - Output: i8.q1, i8.q2, i4.f1, t1.f1 + Project: i8.q1, i8.q2, i4.f1, t1.f1 -> Nested Loop Left Join - Output: i8.q1, i8.q2, i4.f1 + Project: i8.q1, i8.q2, i4.f1 Join Filter: (i8.q1 = i4.f1) -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 @@ -4031,10 +4031,10 @@ where t1.f1 = ss.f1; QUERY PLAN -------------------------------------------------- Nested Loop - Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1 + Project: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1 Join Filter: (t1.f1 = t2.f1) -> Nested Loop Left Join - Output: t1.f1, i8.q1, i8.q2 + Project: t1.f1, i8.q1, i8.q2 -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize @@ -4045,7 +4045,7 @@ where t1.f1 = ss.f1; -> Limit Output: (i8.q1), t2.f1 -> Seq Scan on public.text_tbl t2 - Output: i8.q1, t2.f1 + Project: i8.q1, t2.f1 (16 rows) select * from @@ -4067,15 +4067,15 @@ select * from lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, lateral (select ss1.* from text_tbl t3 limit 1) as ss2 where t1.f1 = ss2.f1; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Nested Loop - Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1) + Project: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1) Join Filter: (t1.f1 = (t2.f1)) -> Nested Loop - Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1 + Project: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1 -> Nested Loop Left Join - Output: t1.f1, i8.q1, i8.q2 + Project: t1.f1, i8.q1, i8.q2 -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize @@ -4086,11 +4086,11 @@ where t1.f1 = ss2.f1; -> Limit Output: (i8.q1), t2.f1 -> Seq Scan on public.text_tbl t2 - Output: i8.q1, t2.f1 + Project: i8.q1, t2.f1 -> Limit Output: ((i8.q1)), (t2.f1) -> Seq Scan on public.text_tbl t3 - Output: (i8.q1), t2.f1 + Project: (i8.q1), t2.f1 (22 rows) select * from @@ -4116,11 +4116,11 @@ where tt1.f1 = ss1.c0; QUERY PLAN ---------------------------------------------------------- Nested Loop - Output: 1 + Project: 1 -> Nested Loop Left Join - Output: tt1.f1, tt4.f1 + Project: tt1.f1, tt4.f1 -> Nested Loop - Output: tt1.f1 + Project: tt1.f1 -> Seq Scan on public.text_tbl tt1 Output: tt1.f1 Filter: (tt1.f1 = 'foo'::text) @@ -4129,7 +4129,7 @@ where tt1.f1 = ss1.c0; -> Materialize Output: tt4.f1 -> Nested Loop Left Join - Output: tt4.f1 + Project: tt4.f1 Join Filter: (tt3.f1 = tt4.f1) -> Seq Scan on public.text_tbl tt3 Output: tt3.f1 @@ -4143,7 +4143,7 @@ where tt1.f1 = ss1.c0; -> Limit Output: (tt4.f1) -> Seq Scan on public.text_tbl tt5 - Output: tt4.f1 + Project: tt4.f1 (29 rows) select 1 from @@ -4173,14 +4173,14 @@ where ss1.c2 = 0; QUERY PLAN ------------------------------------------------------------------------ Nested Loop - Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) + Project: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) -> Hash Join - Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42 + Project: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42 Hash Cond: (i41.f1 = i42.f1) -> Nested Loop - Output: i8.q1, i8.q2, i43.f1, i41.f1 + Project: i8.q1, i8.q2, i43.f1, i41.f1 -> Nested Loop - Output: i8.q1, i8.q2, i43.f1 + Project: i8.q1, i8.q2, i43.f1 -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 Filter: (i8.q1 = 0) @@ -4196,7 +4196,7 @@ where ss1.c2 = 0; -> Limit Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42)) -> Seq Scan on public.text_tbl - Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42) + Project: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42) (25 rows) select ss2.* from @@ -4281,22 +4281,22 @@ explain (verbose, costs off) select a.q2, b.q1 from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) where coalesce(b.q1, 1) > 0; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Merge Left Join - Output: a.q2, b.q1 + Project: a.q2, b.q1 Merge Cond: (a.q2 = (COALESCE(b.q1, '1'::bigint))) Filter: (COALESCE(b.q1, '1'::bigint) > 0) -> Sort Output: a.q2 Sort Key: a.q2 -> Seq Scan on public.int8_tbl a - Output: a.q2 + Project: a.q2 -> Sort Output: b.q1, (COALESCE(b.q1, '1'::bigint)) Sort Key: (COALESCE(b.q1, '1'::bigint)) -> Seq Scan on public.int8_tbl b - Output: b.q1, COALESCE(b.q1, '1'::bigint) + Project: b.q1, COALESCE(b.q1, '1'::bigint) (14 rows) select a.q2, b.q1 @@ -5189,14 +5189,14 @@ explain (verbose, costs off) select * from int8_tbl a left join lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------- Nested Loop Left Join - Output: a.q1, a.q2, b.q1, b.q2, (a.q2) + Project: a.q1, a.q2, b.q1, b.q2, (a.q2) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl b - Output: b.q1, b.q2, a.q2 + Project: b.q1, b.q2, a.q2 Filter: (a.q2 = b.q1) (7 rows) @@ -5221,14 +5221,14 @@ explain (verbose, costs off) select * from int8_tbl a left join lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Nested Loop Left Join - Output: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, '42'::bigint)) + Project: a.q1, a.q2, b.q1, b.q2, (COALESCE(a.q2, '42'::bigint)) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl b - Output: b.q1, b.q2, COALESCE(a.q2, '42'::bigint) + Project: b.q1, b.q2, COALESCE(a.q2, '42'::bigint) Filter: (a.q2 = b.q1) (7 rows) @@ -5257,7 +5257,7 @@ select * from int4_tbl i left join QUERY PLAN ------------------------------------------- Hash Left Join - Output: i.f1, j.f1 + Project: i.f1, j.f1 Hash Cond: (i.f1 = j.f1) -> Seq Scan on public.int4_tbl i Output: i.f1 @@ -5281,14 +5281,14 @@ select * from int4_tbl i left join explain (verbose, costs off) select * from int4_tbl i left join lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------------- Nested Loop Left Join - Output: i.f1, (COALESCE(i.*)) + Project: i.f1, (COALESCE(i.*)) -> Seq Scan on public.int4_tbl i - Output: i.f1, i.* + Project: i.f1, i.* -> Seq Scan on public.int2_tbl j - Output: j.f1, COALESCE(i.*) + Project: j.f1, COALESCE(i.*) Filter: (i.f1 = j.f1) (7 rows) @@ -5311,11 +5311,11 @@ select * from int4_tbl a, QUERY PLAN ------------------------------------------------- Nested Loop - Output: a.f1, b.f1, c.q1, c.q2 + Project: a.f1, b.f1, c.q1, c.q2 -> Seq Scan on public.int4_tbl a Output: a.f1 -> Hash Left Join - Output: b.f1, c.q1, c.q2 + Project: b.f1, c.q1, c.q2 Hash Cond: (b.f1 = c.q1) -> Seq Scan on public.int4_tbl b Output: b.f1 @@ -5366,14 +5366,14 @@ select * from (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from int8_tbl b cross join int8_tbl c) ss on a.q2 = ss.bq1; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Nested Loop Left Join - Output: a.q1, a.q2, b.q1, c.q1, (LEAST(a.q1, b.q1, c.q1)) + Project: a.q1, a.q2, b.q1, c.q1, (LEAST(a.q1, b.q1, c.q1)) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Nested Loop - Output: b.q1, c.q1, LEAST(a.q1, b.q1, c.q1) + Project: b.q1, c.q1, LEAST(a.q1, b.q1, c.q1) -> Seq Scan on public.int8_tbl b Output: b.q1, b.q2 Filter: (a.q2 = b.q1) @@ -5442,32 +5442,32 @@ select * from lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select ss2.y offset 0) ss3; - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))) + Project: c.q1, c.q2, a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)), d.q1, (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)), ((COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2))) -> Hash Right Join - Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) + Project: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop - Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) + Project: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, '42'::bigint)), (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) -> Hash Left Join - Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)) + Project: a.q1, a.q2, b.q1, (COALESCE(b.q2, '42'::bigint)) Hash Cond: (a.q2 = b.q1) -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Hash Output: b.q1, (COALESCE(b.q2, '42'::bigint)) -> Seq Scan on public.int8_tbl b - Output: b.q1, COALESCE(b.q2, '42'::bigint) + Project: b.q1, COALESCE(b.q2, '42'::bigint) -> Seq Scan on public.int8_tbl d - Output: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2) + Project: d.q1, COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2) -> Hash Output: c.q1, c.q2 -> Seq Scan on public.int8_tbl c Output: c.q1, c.q2 -> Result - Output: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) + Project: (COALESCE((COALESCE(b.q2, '42'::bigint)), d.q2)) (24 rows) -- case that breaks the old ph_may_need optimization @@ -5482,21 +5482,21 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 ) on c.q2 = ss2.q1, lateral (select * from int4_tbl i where ss2.y > f1) ss3; - QUERY PLAN ---------------------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------------------- Nested Loop - Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1 + Project: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1 Join Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1) -> Hash Right Join - Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) + Project: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) Hash Cond: (d.q1 = c.q2) -> Nested Loop - Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) + Project: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) -> Hash Right Join - Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) + Project: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint)) Hash Cond: (b.q1 = a.q2) -> Nested Loop - Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint) + Project: b.q1, COALESCE(b.q2, (b2.f1)::bigint) Join Filter: (b.q1 < b2.f1) -> Seq Scan on public.int8_tbl b Output: b.q1, b.q2 @@ -5509,7 +5509,7 @@ select c.*,a.*,ss1.q1,ss2.q1,ss3.* from -> Seq Scan on public.int8_tbl a Output: a.q1, a.q2 -> Seq Scan on public.int8_tbl d - Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) + Project: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2) -> Hash Output: c.q1, c.q2 -> Seq Scan on public.int8_tbl c @@ -5530,16 +5530,16 @@ select * from QUERY PLAN ---------------------------------------------- Nested Loop Left Join - Output: (1), (2), (3) + Project: (1), (2), (3) Join Filter: (((3) = (1)) AND ((3) = (2))) -> Nested Loop - Output: (1), (2) + Project: (1), (2) -> Result - Output: 1 + Project: 1 -> Result - Output: 2 + Project: 2 -> Result - Output: 3 + Project: 3 (11 rows) -- check dummy rels with lateral references (bug #15694) @@ -5549,25 +5549,25 @@ select * from int8_tbl i8 left join lateral QUERY PLAN -------------------------------------- Nested Loop Left Join - Output: i8.q1, i8.q2, f1, (i8.q2) + Project: i8.q1, i8.q2, f1, (i8.q2) -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result - Output: f1, i8.q2 + Project: f1, i8.q2 One-Time Filter: false (7 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) + Project: i8.q1, i8.q2, f1, f1, (i8.q2) -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result - Output: f1, f1, i8.q2 + Project: f1, f1, i8.q2 One-Time Filter: false (7 rows) @@ -5600,18 +5600,18 @@ select * from QUERY PLAN ---------------------------------------------------------------------- Nested Loop - Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1 + Project: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1 -> Values Scan on "*VALUES*" Output: "*VALUES*".column1, "*VALUES*".column2 -> Nested Loop Semi Join - Output: int4_tbl.f1 + Project: int4_tbl.f1 Join Filter: (int4_tbl.f1 = tenk1.unique1) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 -> Materialize Output: tenk1.unique1 -> Index Scan using tenk1_unique2 on public.tenk1 - Output: tenk1.unique1 + Project: tenk1.unique1 Index Cond: (tenk1.unique2 = "*VALUES*".column2) (14 rows) @@ -5636,14 +5636,14 @@ lateral (select * from int8_tbl t1, where q2 = (select greatest(t1.q1,t2.q2)) and (select v.id=0)) offset 0) ss2) ss where t1.q1 = ss.q2) ss0; - QUERY PLAN ------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------ Nested Loop - Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2 + Project: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2 -> Seq Scan on public.int8_tbl t1 Output: t1.q1, t1.q2 -> Nested Loop - Output: "*VALUES*".column1, ss2.q1, ss2.q2 + Project: "*VALUES*".column1, ss2.q1, ss2.q2 -> Values Scan on "*VALUES*" Output: "*VALUES*".column1 -> Subquery Scan on ss2 @@ -5654,14 +5654,14 @@ lateral (select * from int8_tbl t1, Filter: (SubPlan 3) SubPlan 3 -> Result - Output: t3.q2 + Project: t3.q2 One-Time Filter: $4 InitPlan 1 (returns $2) -> Result - Output: GREATEST($0, t2.q2) + Project: GREATEST($0, t2.q2) InitPlan 2 (returns $4) -> Result - Output: ($3 = 0) + Project: ($3 = 0) -> Seq Scan on public.int8_tbl t3 Output: t3.q1, t3.q2 Filter: (t3.q2 = $2) @@ -5785,11 +5785,11 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a Sort Key: t1.a -> Nested Loop Left Join - Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a + Project: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a -> Seq Scan on public.join_ut1 t1 Output: t1.a, t1.b, t1.c -> Hash Join - Output: t2.a, LEAST(t1.a, t2.a, t3.a) + Project: t2.a, LEAST(t1.a, t2.a, t3.a) Hash Cond: (t3.b = t2.a) -> Seq Scan on public.join_ut1 t3 Output: t3.a, t3.b, t3.c @@ -5797,10 +5797,10 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral Output: t2.a -> Append -> Seq Scan on public.join_pt1p1p1 t2 - Output: t2.a + Project: t2.a Filter: (t1.a = t2.a) -> Seq Scan on public.join_pt1p2 t2_1 - Output: t2_1.a + Project: t2_1.a Filter: (t1.a = t2_1.a) (21 rows) @@ -5869,7 +5869,7 @@ select * from j1 inner join j2 on j1.id = j2.id; QUERY PLAN ----------------------------------- Hash Join - Output: j1.id, j2.id + Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) -> Seq Scan on public.j1 @@ -5886,7 +5886,7 @@ select * from j1 inner join j2 on j1.id > j2.id; QUERY PLAN ----------------------------------- Nested Loop - Output: j1.id, j2.id + Project: j1.id, j2.id Join Filter: (j1.id > j2.id) -> Seq Scan on public.j1 Output: j1.id @@ -5902,7 +5902,7 @@ select * from j1 inner join j3 on j1.id = j3.id; QUERY PLAN ----------------------------------- Hash Join - Output: j1.id, j3.id + Project: j1.id, j3.id Inner Unique: true Hash Cond: (j3.id = j1.id) -> Seq Scan on public.j3 @@ -5919,7 +5919,7 @@ select * from j1 left join j2 on j1.id = j2.id; QUERY PLAN ----------------------------------- Hash Left Join - Output: j1.id, j2.id + Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) -> Seq Scan on public.j1 @@ -5936,7 +5936,7 @@ select * from j1 right join j2 on j1.id = j2.id; QUERY PLAN ----------------------------------- Hash Left Join - Output: j1.id, j2.id + Project: j1.id, j2.id Inner Unique: true Hash Cond: (j2.id = j1.id) -> Seq Scan on public.j2 @@ -5953,7 +5953,7 @@ select * from j1 full join j2 on j1.id = j2.id; QUERY PLAN ----------------------------------- Hash Full Join - Output: j1.id, j2.id + Project: j1.id, j2.id Inner Unique: true Hash Cond: (j1.id = j2.id) -> Seq Scan on public.j1 @@ -5970,7 +5970,7 @@ select * from j1 cross join j2; QUERY PLAN ----------------------------------- Nested Loop - Output: j1.id, j2.id + Project: j1.id, j2.id -> Seq Scan on public.j1 Output: j1.id -> Materialize @@ -5985,7 +5985,7 @@ select * from j1 natural join j2; QUERY PLAN ----------------------------------- Hash Join - Output: j1.id + Project: j1.id Inner Unique: true Hash Cond: (j1.id = j2.id) -> Seq Scan on public.j1 @@ -6003,7 +6003,7 @@ inner join (select distinct id from j3) j3 on j1.id = j3.id; QUERY PLAN ----------------------------------------- Nested Loop - Output: j1.id, j3.id + Project: j1.id, j3.id Inner Unique: true Join Filter: (j1.id = j3.id) -> Unique @@ -6024,11 +6024,11 @@ inner join (select id from j3 group by id) j3 on j1.id = j3.id; QUERY PLAN ----------------------------------------- Nested Loop - Output: j1.id, j3.id + Project: j1.id, j3.id Inner Unique: true Join Filter: (j1.id = j3.id) -> Group - Output: j3.id + Project: j3.id Group Key: j3.id -> Sort Output: j3.id @@ -6057,10 +6057,10 @@ analyze j3; explain (verbose, costs off) select * from j1 inner join j2 on j1.id1 = j2.id1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------- Nested Loop - Output: j1.id1, j1.id2, j2.id1, j2.id2 + Project: j1.id1, j1.id2, j2.id1, j2.id2 Join Filter: (j1.id1 = j2.id1) -> Seq Scan on public.j2 Output: j2.id1, j2.id2 @@ -6075,7 +6075,7 @@ inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2; QUERY PLAN ---------------------------------------------------------- Nested Loop - Output: j1.id1, j1.id2, j2.id1, j2.id2 + Project: j1.id1, j1.id2, j2.id1, j2.id2 Inner Unique: true Join Filter: ((j1.id1 = j2.id1) AND (j1.id2 = j2.id2)) -> Seq Scan on public.j2 @@ -6089,10 +6089,10 @@ inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2; explain (verbose, costs off) select * from j1 inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------- Nested Loop - Output: j1.id1, j1.id2, j2.id1, j2.id2 + Project: j1.id1, j1.id2, j2.id1, j2.id2 Join Filter: (j1.id1 = j2.id1) -> Seq Scan on public.j1 Output: j1.id1, j1.id2 @@ -6105,10 +6105,10 @@ inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1; explain (verbose, costs off) select * from j1 left join j2 on j1.id1 = j2.id1 where j1.id2 = 1; - QUERY PLAN ------------------------------------------- + QUERY PLAN +------------------------------------------- Nested Loop Left Join - Output: j1.id1, j1.id2, j2.id1, j2.id2 + Project: j1.id1, j1.id2, j2.id1, j2.id2 Join Filter: (j1.id1 = j2.id1) -> Seq Scan on public.j1 Output: j1.id1, j1.id2 @@ -6166,12 +6166,12 @@ where exists (select 1 from tenk1 t3 QUERY PLAN --------------------------------------------------------------------------------- Nested Loop - Output: t1.unique1, t2.hundred + Project: t1.unique1, t2.hundred -> Hash Join - Output: t1.unique1, t3.tenthous + Project: t1.unique1, t3.tenthous Hash Cond: (t3.thousand = t1.unique1) -> HashAggregate - Output: t3.thousand, t3.tenthous + Project: t3.thousand, t3.tenthous Group Key: t3.thousand, t3.tenthous -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3 Output: t3.thousand, t3.tenthous @@ -6198,9 +6198,9 @@ where exists (select 1 from j3 QUERY PLAN ------------------------------------------------------------------------ Nested Loop - Output: t1.unique1, t2.hundred + Project: t1.unique1, t2.hundred -> Nested Loop - Output: t1.unique1, j3.tenthous + Project: t1.unique1, j3.tenthous -> Index Only Scan using onek_unique1 on public.onek t1 Output: t1.unique1 Index Cond: (t1.unique1 < 1) diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out index 3a91c144a27..4e405ebbd76 100644 --- a/src/test/regress/expected/join_hash.out +++ b/src/test/regress/expected/join_hash.out @@ -913,36 +913,36 @@ WHERE AND (SELECT hjtest_1.b * 5) < 50 AND (SELECT hjtest_2.c * 5) < 55 AND hjtest_1.a <> hjtest_2.b; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Hash Join - Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass + Project: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass Hash Cond: ((hjtest_1.id = (SubPlan 1)) AND ((SubPlan 2) = (SubPlan 3))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_1 - Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b + Project: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b Filter: ((SubPlan 4) < 50) SubPlan 4 -> Result - Output: (hjtest_1.b * 5) + Project: (hjtest_1.b * 5) -> Hash Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b -> Seq Scan on public.hjtest_2 - Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b + Project: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b Filter: ((SubPlan 5) < 55) SubPlan 5 -> Result - Output: (hjtest_2.c * 5) + Project: (hjtest_2.c * 5) SubPlan 1 -> Result - Output: 1 + Project: 1 One-Time Filter: (hjtest_2.id = 1) SubPlan 3 -> Result - Output: (hjtest_2.c * 5) + Project: (hjtest_2.c * 5) SubPlan 2 -> Result - Output: (hjtest_1.b * 5) + Project: (hjtest_1.b * 5) (28 rows) SELECT hjtest_1.a a1, hjtest_2.a a2,hjtest_1.tableoid::regclass t1, hjtest_2.tableoid::regclass t2 @@ -967,36 +967,36 @@ WHERE AND (SELECT hjtest_1.b * 5) < 50 AND (SELECT hjtest_2.c * 5) < 55 AND hjtest_1.a <> hjtest_2.b; - QUERY PLAN ------------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------- Hash Join - Output: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass + Project: hjtest_1.a, hjtest_2.a, (hjtest_1.tableoid)::regclass, (hjtest_2.tableoid)::regclass Hash Cond: (((SubPlan 1) = hjtest_1.id) AND ((SubPlan 3) = (SubPlan 2))) Join Filter: (hjtest_1.a <> hjtest_2.b) -> Seq Scan on public.hjtest_2 - Output: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b + Project: hjtest_2.a, hjtest_2.tableoid, hjtest_2.id, hjtest_2.c, hjtest_2.b Filter: ((SubPlan 5) < 55) SubPlan 5 -> Result - Output: (hjtest_2.c * 5) + Project: (hjtest_2.c * 5) -> Hash Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b -> Seq Scan on public.hjtest_1 - Output: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b + Project: hjtest_1.a, hjtest_1.tableoid, hjtest_1.id, hjtest_1.b Filter: ((SubPlan 4) < 50) SubPlan 4 -> Result - Output: (hjtest_1.b * 5) + Project: (hjtest_1.b * 5) SubPlan 2 -> Result - Output: (hjtest_1.b * 5) + Project: (hjtest_1.b * 5) SubPlan 1 -> Result - Output: 1 + Project: 1 One-Time Filter: (hjtest_2.id = 1) SubPlan 3 -> Result - Output: (hjtest_2.c * 5) + Project: (hjtest_2.c * 5) (28 rows) SELECT hjtest_1.a a1, hjtest_2.a a2,hjtest_1.tableoid::regclass t1, hjtest_2.tableoid::regclass t2 diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index c18f547cbd3..5b247e74b77 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -316,12 +316,12 @@ create temp sequence testseq; explain (verbose, costs off) select unique1, unique2, nextval('testseq') from tenk1 order by unique2 limit 10; - QUERY PLAN ----------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------- Limit Output: unique1, unique2, (nextval('testseq'::regclass)) -> Index Scan using tenk1_unique2 on public.tenk1 - Output: unique1, unique2, nextval('testseq'::regclass) + Project: unique1, unique2, nextval('testseq'::regclass) (4 rows) select unique1, unique2, nextval('testseq') @@ -349,17 +349,17 @@ select currval('testseq'); explain (verbose, costs off) select unique1, unique2, nextval('testseq') from tenk1 order by tenthous limit 10; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Limit Output: unique1, unique2, (nextval('testseq'::regclass)), tenthous -> Result - Output: unique1, unique2, nextval('testseq'::regclass), tenthous + Project: unique1, unique2, nextval('testseq'::regclass), tenthous -> Sort Output: unique1, unique2, tenthous Sort Key: tenk1.tenthous -> Seq Scan on public.tenk1 - Output: unique1, unique2, tenthous + Project: unique1, unique2, tenthous (9 rows) select unique1, unique2, nextval('testseq') @@ -423,7 +423,7 @@ select unique1, unique2, generate_series(1,10) Output: unique1, unique2, tenthous Sort Key: tenk1.tenthous -> Seq Scan on public.tenk1 - Output: unique1, unique2, tenthous + Project: unique1, unique2, tenthous (9 rows) select unique1, unique2, generate_series(1,10) @@ -483,12 +483,12 @@ order by s2 desc; explain (verbose, costs off) select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 from tenk1 group by thousand order by thousand limit 3; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- Limit Output: (sum(tenthous)), (((sum(tenthous))::double precision + (random() * '0'::double precision))), thousand -> GroupAggregate - Output: sum(tenthous), ((sum(tenthous))::double precision + (random() * '0'::double precision)), thousand + Project: sum(tenthous), ((sum(tenthous))::double precision + (random() * '0'::double precision)), thousand Group Key: tenk1.thousand -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 Output: thousand, tenthous diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index e85b29455e5..92421090755 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -4832,9 +4832,9 @@ select i, a from QUERY PLAN ----------------------------------------------------------------- Nested Loop - Output: i.i, (returns_rw_array(1)) + Project: i.i, (returns_rw_array(1)) -> Result - Output: returns_rw_array(1) + Project: returns_rw_array(1) -> Function Scan on public.consumes_rw_array i Output: i.i Function Call: consumes_rw_array((returns_rw_array(1))) @@ -4853,7 +4853,7 @@ select consumes_rw_array(a), a from returns_rw_array(1) a; QUERY PLAN -------------------------------------------- Function Scan on public.returns_rw_array a - Output: consumes_rw_array(a), a + Project: consumes_rw_array(a), a Function Call: returns_rw_array(1) (3 rows) @@ -4866,10 +4866,10 @@ select consumes_rw_array(a), a from returns_rw_array(1) a; explain (verbose, costs off) select consumes_rw_array(a), a from (values (returns_rw_array(1)), (returns_rw_array(2))) v(a); - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------- Values Scan on "*VALUES*" - Output: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1 + Project: consumes_rw_array("*VALUES*".column1), "*VALUES*".column1 (2 rows) select consumes_rw_array(a), a from @@ -5207,7 +5207,7 @@ UPDATE transition_table_base SET val = '*' || val || '*' WHERE id BETWEEN 2 AND 3; INFO: Hash Full Join - Output: COALESCE(ot.id, nt.id), ot.val, nt.val + Project: COALESCE(ot.id, nt.id), ot.val, nt.val Hash Cond: (ot.id = nt.id) -> Named Tuplestore Scan Output: ot.id, ot.val diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 36a59291139..175e568ab40 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -2003,7 +2003,7 @@ select x from int8_tbl, extractq2(int8_tbl) f(x); QUERY PLAN ------------------------------------------ Nested Loop - Output: f.x + Project: f.x -> Seq Scan on public.int8_tbl Output: int8_tbl.q1, int8_tbl.q2 -> Function Scan on f @@ -2029,11 +2029,11 @@ select x from int8_tbl, extractq2_2(int8_tbl) f(x); QUERY PLAN ----------------------------------- Nested Loop - Output: ((int8_tbl.*).q2) + Project: ((int8_tbl.*).q2) -> Seq Scan on public.int8_tbl - Output: int8_tbl.* + Project: int8_tbl.* -> Result - Output: (int8_tbl.*).q2 + Project: (int8_tbl.*).q2 (6 rows) select x from int8_tbl, extractq2_2(int8_tbl) f(x); @@ -2055,7 +2055,7 @@ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); QUERY PLAN ----------------------------- Seq Scan on public.int8_tbl - Output: int8_tbl.q2 + Project: int8_tbl.q2 (2 rows) select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index d01769299e4..e2ae42f78ac 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -3972,12 +3972,12 @@ INSERT INTO rls_tbl -------------------------------------------------------------------- Insert on regress_rls_schema.rls_tbl -> Subquery Scan on ss - Output: ss.b, ss.c, NULL::integer + Project: ss.b, ss.c, NULL::integer -> Sort Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a Sort Key: rls_tbl_1.a -> Seq Scan on regress_rls_schema.rls_tbl rls_tbl_1 - Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a + Project: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a Filter: (rls_tbl_1.* >= '(1,1,1)'::record) (9 rows) diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index 2a273f84049..b8e902570fd 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1166,10 +1166,10 @@ explain (verbose, costs off) select r, r is null as isnull, r is not null as isnotnull from (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b); - QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Values Scan on "*VALUES*" - Output: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NOT DISTINCT FROM NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS DISTINCT FROM NULL)) + Project: ROW("*VALUES*".column1, "*VALUES*".column2), (("*VALUES*".column1 IS NULL) AND ("*VALUES*".column2 IS NOT DISTINCT FROM NULL)), (("*VALUES*".column1 IS NOT NULL) AND ("*VALUES*".column2 IS DISTINCT FROM NULL)) (2 rows) select r, r is null as isnull, r is not null as isnotnull @@ -1193,7 +1193,7 @@ select r, r is null as isnull, r is not null as isnotnull from r; QUERY PLAN ---------------------------------------------------------- CTE Scan on r - Output: r.*, (r.* IS NULL), (r.* IS NOT NULL) + Project: r.*, (r.* IS NULL), (r.* IS NOT NULL) CTE r -> Values Scan on "*VALUES*" Output: "*VALUES*".column1, "*VALUES*".column2 diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out index f3696c6d1de..fc93b33ee2b 100644 --- a/src/test/regress/expected/select_distinct.out +++ b/src/test/regress/expected/select_distinct.out @@ -130,15 +130,15 @@ SELECT DISTINCT p.age FROM person* p ORDER BY age using >; EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM (SELECT DISTINCT two, four, two FROM tenk1) ss; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------- Aggregate - Output: count(*) + Project: count(*) -> HashAggregate - Output: tenk1.two, tenk1.four, tenk1.two + Project: tenk1.two, tenk1.four, tenk1.two Group Key: tenk1.two, tenk1.four, tenk1.two -> Seq Scan on public.tenk1 - Output: tenk1.two, tenk1.four, tenk1.two + Project: tenk1.two, tenk1.four, tenk1.two (7 rows) SELECT count(*) FROM diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 0eca76cb41e..3c03b171707 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -212,10 +212,10 @@ select sp_parallel_restricted(unique1) from tenk1 Output: (sp_parallel_restricted(unique1)) Sort Key: (sp_parallel_restricted(tenk1.unique1)) -> Gather - Output: sp_parallel_restricted(unique1) + Project: sp_parallel_restricted(unique1) Workers Planned: 4 -> Parallel Seq Scan on public.tenk1 - Output: unique1 + Project: unique1 Filter: (tenk1.stringu1 = 'GRAAAA'::name) (9 rows) @@ -649,12 +649,12 @@ explain (costs off, verbose) Output: ten, (sp_simple_func(ten)) Workers Planned: 4 -> Result - Output: ten, sp_simple_func(ten) + Project: ten, sp_simple_func(ten) -> Sort Output: ten Sort Key: tenk1.ten -> Parallel Seq Scan on public.tenk1 - Output: ten + Project: ten Filter: (tenk1.ten < 100) (11 rows) @@ -965,18 +965,18 @@ explain (costs off, verbose) QUERY PLAN ---------------------------------------------------------------------------------------------- Aggregate - Output: count(*) + Project: count(*) -> Hash Semi Join Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?)))) -> Gather Output: a.unique1, a.two Workers Planned: 4 -> Parallel Seq Scan on public.tenk1 a - Output: a.unique1, a.two + Project: a.unique1, a.two -> Hash Output: b.unique1, (row_number() OVER (?)) -> WindowAgg - Output: b.unique1, row_number() OVER (?) + Project: b.unique1, row_number() OVER (?) -> Gather Output: b.unique1 Workers Planned: 4 diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index ee9c5db0d51..90fe9fe9802 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -233,23 +233,23 @@ SELECT *, pg_typeof(f1) FROM -- ... unless there's context to suggest differently explain (verbose, costs off) select '42' union all select '43'; - QUERY PLAN ----------------------------- + QUERY PLAN +----------------------------- Append -> Result - Output: '42'::text + Project: '42'::text -> Result - Output: '43'::text + Project: '43'::text (5 rows) explain (verbose, costs off) select '42' union all select 43; - QUERY PLAN --------------------- + QUERY PLAN +--------------------- Append -> Result - Output: 42 + Project: 42 -> Result - Output: 43 + Project: 43 (5 rows) -- check materialization of an initplan reference (bug #14524) @@ -258,15 +258,15 @@ select 1 = all (select (select 1)); QUERY PLAN ----------------------------------- Result - Output: (SubPlan 2) + Project: (SubPlan 2) SubPlan 2 -> Materialize Output: ($0) InitPlan 1 (returns $0) -> Result - Output: 1 + Project: 1 -> Result - Output: $0 + Project: $0 (10 rows) select 1 = all (select (select 1)); @@ -770,16 +770,16 @@ select * from outer_text where (f1, f2) not in (select * from inner_text); -- explain (verbose, costs off) select 'foo'::text in (select 'bar'::name union all select 'bar'::name); - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------------- Result - Output: (hashed SubPlan 1) + Project: (hashed SubPlan 1) SubPlan 1 -> Append -> Result - Output: 'bar'::name + Project: 'bar'::name -> Result - Output: 'bar'::name + Project: 'bar'::name (8 rows) select 'foo'::text in (select 'bar'::name union all select 'bar'::name); @@ -818,27 +818,27 @@ explain (verbose, costs off) QUERY PLAN --------------------------- Values Scan on "*VALUES*" - Output: $0, $1 + Project: $0, $1 InitPlan 1 (returns $0) -> Result - Output: now() + Project: now() InitPlan 2 (returns $1) -> Result - Output: now() + Project: now() (8 rows) explain (verbose, costs off) select x, x from (select (select random()) as x from (values(1),(2)) v(y)) ss; - QUERY PLAN ----------------------------------- + QUERY PLAN +----------------------------------- Subquery Scan on ss - Output: ss.x, ss.x + Project: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: $0 + Project: $0 InitPlan 1 (returns $0) -> Result - Output: random() + Project: random() (7 rows) explain (verbose, costs off) @@ -847,14 +847,14 @@ explain (verbose, costs off) QUERY PLAN ---------------------------------------------------------------------- Values Scan on "*VALUES*" - Output: (SubPlan 1), (SubPlan 2) + Project: (SubPlan 1), (SubPlan 2) SubPlan 1 -> Result - Output: now() + Project: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) SubPlan 2 -> Result - Output: now() + Project: now() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) (10 rows) @@ -864,12 +864,12 @@ explain (verbose, costs off) QUERY PLAN ---------------------------------------------------------------------------- Subquery Scan on ss - Output: ss.x, ss.x + Project: ss.x, ss.x -> Values Scan on "*VALUES*" - Output: (SubPlan 1) + Project: (SubPlan 1) SubPlan 1 -> Result - Output: random() + Project: random() One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) (8 rows) @@ -936,7 +936,7 @@ select * from int4_tbl where QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Semi Join - Output: int4_tbl.f1 + Project: int4_tbl.f1 Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 @@ -961,10 +961,10 @@ select * from int4_tbl where explain (verbose, costs off) select * from int4_tbl o where (f1, f1) in (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------- Nested Loop Semi Join - Output: o.f1 + Project: o.f1 Join Filter: (o.f1 = "ANY_subquery".f1) -> Seq Scan on public.int4_tbl o Output: o.f1 @@ -974,11 +974,11 @@ select * from int4_tbl o where (f1, f1) in Output: "ANY_subquery".f1, "ANY_subquery".g Filter: ("ANY_subquery".f1 = "ANY_subquery".g) -> Result - Output: i.f1, ((generate_series(1, 50)) / 10) + Project: i.f1, ((generate_series(1, 50)) / 10) -> ProjectSet Output: generate_series(1, 50), i.f1 -> HashAggregate - Output: i.f1 + Project: i.f1 Group Key: i.f1 -> Seq Scan on public.int4_tbl i Output: i.f1 @@ -1220,7 +1220,7 @@ select * from x where f1 = 1; QUERY PLAN ---------------------------------- Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1 + Project: subselect_tbl.f1 Filter: (subselect_tbl.f1 = 1) (3 rows) @@ -1235,17 +1235,17 @@ select * from x where f1 = 1; Filter: (x.f1 = 1) CTE x -> Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1 + Project: subselect_tbl.f1 (6 rows) -- Stable functions are safe to inline explain (verbose, costs off) with x as (select * from (select f1, now() from subselect_tbl) ss) select * from x where f1 = 1; - QUERY PLAN ------------------------------------ + QUERY PLAN +------------------------------------ Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1, now() + Project: subselect_tbl.f1, now() Filter: (subselect_tbl.f1 = 1) (3 rows) @@ -1253,46 +1253,46 @@ select * from x where f1 = 1; explain (verbose, costs off) with x as (select * from (select f1, random() from subselect_tbl) ss) select * from x where f1 = 1; - QUERY PLAN ----------------------------------------------- + QUERY PLAN +----------------------------------------------- CTE Scan on x Output: x.f1, x.random Filter: (x.f1 = 1) CTE x -> Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1, random() + Project: subselect_tbl.f1, random() (6 rows) -- SELECT FOR UPDATE cannot be inlined explain (verbose, costs off) with x as (select * from (select f1 from subselect_tbl for update) ss) select * from x where f1 = 1; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- CTE Scan on x Output: x.f1 Filter: (x.f1 = 1) CTE x -> Subquery Scan on ss - Output: ss.f1 + Project: ss.f1 -> LockRows Output: subselect_tbl.f1, subselect_tbl.ctid -> Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1, subselect_tbl.ctid + Project: subselect_tbl.f1, subselect_tbl.ctid (10 rows) -- Multiply-referenced CTEs are inlined only when requested explain (verbose, costs off) with x as (select * from (select f1, now() as n from subselect_tbl) ss) select * from x, x x2 where x.n = x2.n; - QUERY PLAN -------------------------------------------- + QUERY PLAN +-------------------------------------------- Merge Join - Output: x.f1, x.n, x2.f1, x2.n + Project: x.f1, x.n, x2.f1, x2.n Merge Cond: (x.n = x2.n) CTE x -> Seq Scan on public.subselect_tbl - Output: subselect_tbl.f1, now() + Project: subselect_tbl.f1, now() -> Sort Output: x.f1, x.n Sort Key: x.n @@ -1311,16 +1311,16 @@ select * from x, x x2 where x.n = x2.n; QUERY PLAN ---------------------------------------------------------------------------- Result - Output: subselect_tbl.f1, now(), subselect_tbl_1.f1, now() + Project: subselect_tbl.f1, now(), subselect_tbl_1.f1, now() One-Time Filter: (now() = now()) -> Nested Loop - Output: subselect_tbl.f1, subselect_tbl_1.f1 + Project: subselect_tbl.f1, subselect_tbl_1.f1 -> Seq Scan on public.subselect_tbl Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 -> Materialize Output: subselect_tbl_1.f1 -> Seq Scan on public.subselect_tbl subselect_tbl_1 - Output: subselect_tbl_1.f1 + Project: subselect_tbl_1.f1 (11 rows) -- Multiply-referenced CTEs can't be inlined if they contain outer self-refs @@ -1341,7 +1341,7 @@ select * from x; -> Values Scan on "*VALUES*" Output: "*VALUES*".column1 -> Nested Loop - Output: (z.a || z1.a) + Project: (z.a || z1.a) Join Filter: (length((z.a || z1.a)) < 5) CTE z -> WorkTable Scan on x x_1 @@ -1450,10 +1450,10 @@ select * from (with y as (select * from x) select * from y) ss; explain (verbose, costs off) with x as (select 1 as y) select * from (with x as (select 2 as y) select * from x) ss; - QUERY PLAN -------------- + QUERY PLAN +-------------- Result - Output: 2 + Project: 2 (2 rows) -- Row marks are not pushed into CTEs diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index d47b5f6ec57..9fd94e24664 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -103,10 +103,10 @@ SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; explain (verbose, costs off) SELECT * FROM few f1, (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +------------------------------------------------- Result - Output: f1.id, f1.dataa, f1.datab, ss.unnest + Project: f1.id, f1.dataa, f1.datab, ss.unnest One-Time Filter: false (3 rows) @@ -647,10 +647,10 @@ SELECT |@|ARRAY[1,2,3]; -- Some fun cases involving duplicate SRF calls explain (verbose, costs off) select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Result - Output: (generate_series(1, 3)), ((generate_series(1, 3)) + 1) + Project: (generate_series(1, 3)), ((generate_series(1, 3)) + 1) -> ProjectSet Output: generate_series(1, 3) -> Result @@ -666,13 +666,13 @@ select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; explain (verbose, costs off) select generate_series(1,3)+1 order by generate_series(1,3); - QUERY PLAN ------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Sort Output: (((generate_series(1, 3)) + 1)), (generate_series(1, 3)) Sort Key: (generate_series(1, 3)) -> Result - Output: ((generate_series(1, 3)) + 1), (generate_series(1, 3)) + Project: ((generate_series(1, 3)) + 1), (generate_series(1, 3)) -> ProjectSet Output: generate_series(1, 3) -> Result @@ -689,10 +689,10 @@ select generate_series(1,3)+1 order by generate_series(1,3); -- Check that SRFs of same nesting level run in lockstep explain (verbose, costs off) select generate_series(1,3) as x, generate_series(3,6) + 1 as y; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Result - Output: (generate_series(1, 3)), ((generate_series(3, 6)) + 1) + Project: (generate_series(1, 3)), ((generate_series(3, 6)) + 1) -> ProjectSet Output: generate_series(1, 3), generate_series(3, 6) -> Result diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 8443c24f18b..1060e31408b 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -1262,12 +1262,12 @@ SELECT * FROM rw_view1; (4 rows) EXPLAIN (verbose, costs off) UPDATE rw_view1 SET b = b + 1 RETURNING *; - QUERY PLAN -------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Update on public.base_tbl Output: base_tbl.a, base_tbl.b -> Seq Scan on public.base_tbl - Output: base_tbl.a, (base_tbl.b + 1), base_tbl.ctid + Project: base_tbl.a, (base_tbl.b + 1), base_tbl.ctid (4 rows) UPDATE rw_view1 SET b = b + 1 RETURNING *; @@ -2288,7 +2288,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; Update on public.t12 Update on public.t111 -> Index Scan using t1_a_idx on public.t1 - Output: 100, t1.b, t1.c, t1.ctid + Project: 100, t1.b, t1.c, t1.ctid Index Cond: ((t1.a > 5) AND (t1.a < 7)) Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a)) SubPlan 1 @@ -2300,19 +2300,19 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; SubPlan 2 -> Append -> Seq Scan on public.t12 t12_2 - Output: t12_2.a + Project: t12_2.a -> Seq Scan on public.t111 t111_2 - Output: t111_2.a + Project: t111_2.a -> Index Scan using t11_a_idx on public.t11 - Output: 100, t11.b, t11.c, t11.d, t11.ctid + Project: 100, t11.b, t11.c, t11.d, t11.ctid Index Cond: ((t11.a > 5) AND (t11.a < 7)) Filter: ((t11.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a)) -> Index Scan using t12_a_idx on public.t12 - Output: 100, t12.b, t12.c, t12.e, t12.ctid + Project: 100, t12.b, t12.c, t12.e, t12.ctid Index Cond: ((t12.a > 5) AND (t12.a < 7)) Filter: ((t12.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a)) -> Index Scan using t111_a_idx on public.t111 - Output: 100, t111.b, t111.c, t111.d, t111.e, t111.ctid + Project: 100, t111.b, t111.c, t111.d, t111.e, t111.ctid Index Cond: ((t111.a > 5) AND (t111.a < 7)) Filter: ((t111.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a)) (33 rows) @@ -2338,7 +2338,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; Update on public.t12 Update on public.t111 -> Index Scan using t1_a_idx on public.t1 - Output: (t1.a + 1), t1.b, t1.c, t1.ctid + Project: (t1.a + 1), t1.b, t1.c, t1.ctid Index Cond: ((t1.a > 5) AND (t1.a = 8)) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a)) SubPlan 1 @@ -2350,19 +2350,19 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; SubPlan 2 -> Append -> Seq Scan on public.t12 t12_2 - Output: t12_2.a + Project: t12_2.a -> Seq Scan on public.t111 t111_2 - Output: t111_2.a + Project: t111_2.a -> Index Scan using t11_a_idx on public.t11 - Output: (t11.a + 1), t11.b, t11.c, t11.d, t11.ctid + Project: (t11.a + 1), t11.b, t11.c, t11.d, t11.ctid Index Cond: ((t11.a > 5) AND (t11.a = 8)) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a)) -> Index Scan using t12_a_idx on public.t12 - Output: (t12.a + 1), t12.b, t12.c, t12.e, t12.ctid + Project: (t12.a + 1), t12.b, t12.c, t12.e, t12.ctid Index Cond: ((t12.a > 5) AND (t12.a = 8)) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a)) -> Index Scan using t111_a_idx on public.t111 - Output: (t111.a + 1), t111.b, t111.c, t111.d, t111.e, t111.ctid + Project: (t111.a + 1), t111.b, t111.c, t111.d, t111.e, t111.ctid Index Cond: ((t111.a > 5) AND (t111.a = 8)) Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a)) (33 rows) diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index a24ecd61df8..59419ec692d 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -172,17 +172,17 @@ EXPLAIN (VERBOSE, COSTS OFF) UPDATE update_test t SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a) WHERE CURRENT_USER = SESSION_USER; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Update on public.update_test t -> Result - Output: $1, $2, t.c, (SubPlan 1 (returns $1,$2)), t.ctid + Project: $1, $2, t.c, (SubPlan 1 (returns $1,$2)), t.ctid One-Time Filter: (CURRENT_USER = SESSION_USER) -> Seq Scan on public.update_test t - Output: t.c, t.a, t.ctid + Project: t.c, t.a, t.ctid SubPlan 1 (returns $1,$2) -> Seq Scan on public.update_test s - Output: s.b, s.a + Project: s.b, s.a Filter: (s.a = t.a) (10 rows) diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 2a2085556bb..05d2847dc84 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -2181,8 +2181,8 @@ SELECT * FROM parent; EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) DELETE FROM a USING wcte WHERE aa = q2; - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Delete on public.a Delete on public.a Delete on public.b @@ -2192,35 +2192,35 @@ DELETE FROM a USING wcte WHERE aa = q2; -> Insert on public.int8_tbl Output: int8_tbl.q2 -> Result - Output: '42'::bigint, '47'::bigint + Project: '42'::bigint, '47'::bigint -> Nested Loop - Output: a.ctid, wcte.* + Project: a.ctid, wcte.* Join Filter: (a.aa = wcte.q2) -> Seq Scan on public.a - Output: a.ctid, a.aa + Project: a.ctid, a.aa -> CTE Scan on wcte - Output: wcte.*, wcte.q2 + Project: wcte.*, wcte.q2 -> Nested Loop - Output: b.ctid, wcte.* + Project: b.ctid, wcte.* Join Filter: (b.aa = wcte.q2) -> Seq Scan on public.b - Output: b.ctid, b.aa + Project: b.ctid, b.aa -> CTE Scan on wcte - Output: wcte.*, wcte.q2 + Project: wcte.*, wcte.q2 -> Nested Loop - Output: c.ctid, wcte.* + Project: c.ctid, wcte.* Join Filter: (c.aa = wcte.q2) -> Seq Scan on public.c - Output: c.ctid, c.aa + Project: c.ctid, c.aa -> CTE Scan on wcte - Output: wcte.*, wcte.q2 + Project: wcte.*, wcte.q2 -> Nested Loop - Output: d.ctid, wcte.* + Project: d.ctid, wcte.* Join Filter: (d.aa = wcte.q2) -> Seq Scan on public.d - Output: d.ctid, d.aa + Project: d.ctid, d.aa -> CTE Scan on wcte - Output: wcte.*, wcte.q2 + Project: wcte.*, wcte.q2 (38 rows) -- error cases diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index 55b65ef324d..d0b69103eb3 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -1137,7 +1137,7 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1313,7 +1313,7 @@ SELECT xmltable.* QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1333,7 +1333,7 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" + Project: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1436,7 +1436,7 @@ SELECT xmltable.* QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" diff --git a/src/test/regress/expected/xml_2.out b/src/test/regress/expected/xml_2.out index 04842602817..95d56b3324e 100644 --- a/src/test/regress/expected/xml_2.out +++ b/src/test/regress/expected/xml_2.out @@ -1117,7 +1117,7 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM xmltableview1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1293,7 +1293,7 @@ SELECT xmltable.* QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1313,7 +1313,7 @@ SELECT xmltable.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" + Project: "xmltable"."COUNTRY_NAME", "xmltable"."REGION_ID" -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" @@ -1416,7 +1416,7 @@ SELECT xmltable.* QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop - Output: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name + Project: "xmltable".id, "xmltable"._id, "xmltable".country_name, "xmltable".country_id, "xmltable".region_id, "xmltable".size, "xmltable".unit, "xmltable".premier_name -> Seq Scan on public.xmldata Output: xmldata.data -> Table Function Scan on "xmltable" -- 2.23.0.385.gbc12974a89