From f076f861abb9c4d7a3f8943830aa98792b642609 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Thu, 21 Dec 2023 19:03:15 +0200 Subject: [PATCH v1 5/5] Fix regression tests caused by additional Result nodes on top of Sorts et al. We not add an extra Result node to project away the unneeded ORDER BY junk columns. This may seem bad from a performance point of view, but remember that previously the junk filter in the executor was doing basically the same work anyway. It's just represented in the plan tree now. Perhaps we should teach Sort and MergeAppend nodes how to project to avoid this churn. (This commit should be squashed with the previous commit that caused these diffs, but I'm keeping them separate for now to make it easier to see the effect of the previous commit.) --- src/test/regress/expected/aggregates.out | 35 +-- src/test/regress/expected/collate.out | 13 +- src/test/regress/expected/create_am.out | 15 +- src/test/regress/expected/create_index.out | 34 +-- src/test/regress/expected/geometry.out | 30 +- src/test/regress/expected/groupingsets.out | 44 +-- src/test/regress/expected/inherit.out | 256 +++++++++--------- src/test/regress/expected/join.out | 48 ++-- src/test/regress/expected/limit.out | 22 +- .../regress/expected/partition_aggregate.out | 180 ++++++------ src/test/regress/expected/partition_join.out | 37 +-- src/test/regress/expected/partition_prune.out | 57 ++-- src/test/regress/expected/rowsecurity.out | 18 +- src/test/regress/expected/select_parallel.out | 63 +++-- src/test/regress/expected/tsrf.out | 24 +- 15 files changed, 459 insertions(+), 417 deletions(-) diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index e7814b7527b..2388176579d 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -735,22 +735,24 @@ explain (verbose, costs off) select array(select sum(x+y) s from generate_series(1,3) y group by y order by s) from generate_series(1,3) x; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Function Scan on pg_catalog.generate_series x Output: (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 - Group Key: y.y - -> Function Scan on pg_catalog.generate_series y - Output: y.y - Function Call: generate_series(1, 3) -(13 rows) + -> Result + Output: (sum((x.x + y.y))) + -> 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 + Group Key: y.y + -> Function Scan on pg_catalog.generate_series y + Output: y.y + Function Call: generate_series(1, 3) +(15 rows) select array(select sum(x+y) s from generate_series(1,3) y group by y order by s) @@ -1147,14 +1149,15 @@ explain (costs off) select max(unique2) from tenk1 order by max(unique2)+1; QUERY PLAN --------------------------------------------------------------------- - Sort - Sort Key: (($0 + 1)) + Result InitPlan 1 (returns $0) -> Limit -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) - -> Result -(7 rows) + -> Sort + Sort Key: (($0 + 1)) + -> Result +(8 rows) select max(unique2) from tenk1 order by max(unique2)+1; max diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out index 06495644852..2cf3e371179 100644 --- a/src/test/regress/expected/collate.out +++ b/src/test/regress/expected/collate.out @@ -642,12 +642,13 @@ EXPLAIN (COSTS OFF) EXPLAIN (COSTS OFF) SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST; - QUERY PLAN ------------------------------------------------------------ - Sort - Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST - -> Seq Scan on collate_test10 -(3 rows) + QUERY PLAN +----------------------------------------------------------------- + Result + -> Sort + Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST + -> Seq Scan on collate_test10 +(4 rows) -- CREATE/DROP COLLATION CREATE COLLATION mycoll1 FROM "C"; diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out index b50293d514f..6d70b1f0418 100644 --- a/src/test/regress/expected/create_am.out +++ b/src/test/regress/expected/create_am.out @@ -46,13 +46,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM fast_emp4000 WHERE home_base <@ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; - QUERY PLAN ------------------------------------------------------------------ - Sort - Sort Key: ((home_base[0])[0]) - -> Index Only Scan using grect2ind2 on fast_emp4000 - Index Cond: (home_base <@ '(2000,1000),(200,200)'::box) -(4 rows) + QUERY PLAN +----------------------------------------------------------------------- + Result + -> Sort + Sort Key: ((home_base[0])[0]) + -> Index Only Scan using grect2ind2 on fast_emp4000 + Index Cond: (home_base <@ '(2000,1000),(200,200)'::box) +(5 rows) SELECT * FROM fast_emp4000 WHERE home_base <@ '(200,200),(2000,1000)'::box diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index a12d38a8c8e..908e030d2d7 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -247,13 +247,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM fast_emp4000 WHERE home_base <@ '(200,200),(2000,1000)'::box ORDER BY (home_base[0])[0]; - QUERY PLAN ------------------------------------------------------------------ - Sort - Sort Key: ((home_base[0])[0]) - -> Index Only Scan using grect2ind on fast_emp4000 - Index Cond: (home_base <@ '(2000,1000),(200,200)'::box) -(4 rows) + QUERY PLAN +----------------------------------------------------------------------- + Result + -> Sort + Sort Key: ((home_base[0])[0]) + -> Index Only Scan using grect2ind on fast_emp4000 + Index Cond: (home_base <@ '(2000,1000),(200,200)'::box) +(5 rows) SELECT * FROM fast_emp4000 WHERE home_base <@ '(200,200),(2000,1000)'::box @@ -622,15 +623,16 @@ SET enable_indexscan = OFF; SET enable_bitmapscan = ON; EXPLAIN (COSTS OFF) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; - QUERY PLAN ------------------------------------------------------------- - Sort - Sort Key: ((f1 <-> '(0,1)'::point)) - -> Bitmap Heap Scan on point_tbl - Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box) - -> Bitmap Index Scan on gpointind - Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) -(6 rows) + QUERY PLAN +------------------------------------------------------------------ + Result + -> Sort + Sort Key: ((f1 <-> '(0,1)'::point)) + -> Bitmap Heap Scan on point_tbl + Recheck Cond: (f1 <@ '(10,10),(-10,-10)'::box) + -> Bitmap Index Scan on gpointind + Index Cond: (f1 <@ '(10,10),(-10,-10)'::box) +(7 rows) SELECT * FROM point_tbl WHERE f1 <@ '(-10,-10),(10,10)':: box ORDER BY f1 <-> '0,1'; f1 diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out index 8be694f46be..843b3664b4f 100644 --- a/src/test/regress/expected/geometry.out +++ b/src/test/regress/expected/geometry.out @@ -5250,13 +5250,14 @@ SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) EXPLAIN (COSTS OFF) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); - QUERY PLAN ----------------------------------------------- - Sort - Sort Key: (area(f1)) - -> Seq Scan on circle_tbl - Filter: (f1 && '<(1,-2),1>'::circle) -(4 rows) + QUERY PLAN +---------------------------------------------------- + Result + -> Sort + Sort Key: (area(f1)) + -> Seq Scan on circle_tbl + Filter: (f1 && '<(1,-2),1>'::circle) +(5 rows) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) ORDER BY area(f1); @@ -5280,13 +5281,14 @@ SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon EXPLAIN (COSTS OFF) SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; - QUERY PLAN --------------------------------------------------------- - Sort - Sort Key: ((poly_center(f1))[0]) - -> Seq Scan on polygon_tbl - Filter: (f1 @> '((1,1),(2,2),(2,1))'::polygon) -(4 rows) + QUERY PLAN +-------------------------------------------------------------- + Result + -> Sort + Sort Key: ((poly_center(f1))[0]) + -> Seq Scan on polygon_tbl + Filter: (f1 @> '((1,1),(2,2),(2,1))'::polygon) +(5 rows) SELECT * FROM polygon_tbl WHERE f1 @> '((1,1),(2,2),(2,1))'::polygon ORDER BY (poly_center(f1))[0]; diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 113f7a72b1f..77bc41d9dd9 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -1204,17 +1204,18 @@ explain (costs off) count(*), sum(v) from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) order by 3,5; - QUERY PLAN ------------------------------------------------------------------- - Sort - Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) - -> MixedAggregate - Hash Key: v, unsortable_col - Group Key: v, unhashable_col - -> Sort - Sort Key: v, unhashable_col - -> Seq Scan on gstest4 -(8 rows) + QUERY PLAN +------------------------------------------------------------------------ + Result + -> Sort + Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) + -> MixedAggregate + Hash Key: v, unsortable_col + Group Key: v, unhashable_col + -> Sort + Sort Key: v, unhashable_col + -> Seq Scan on gstest4 +(9 rows) -- empty input: first is 0 rows, second 1, third 3 etc. select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); @@ -1376,16 +1377,17 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; - QUERY PLAN -------------------------------------------------------------------------------------------- - Sort - Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) - -> HashAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) - Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) - -> Values Scan on "*VALUES*" -(7 rows) + QUERY PLAN +------------------------------------------------------------------------------------------------- + Result + -> Sort + Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) + -> HashAggregate + Hash Key: "*VALUES*".column1, "*VALUES*".column2 + Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) + Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) + -> Values Scan on "*VALUES*" +(8 rows) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 0f1aa831f64..9ff8edac407 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1539,23 +1539,25 @@ 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 ------------------------------------------------------------- - Sort - Output: matest0.id, matest0.name, ((1 - matest0.id)) - Sort Key: ((1 - matest0.id)) - -> Result - Output: matest0.id, matest0.name, (1 - matest0.id) - -> Append - -> Seq Scan on public.matest0 matest0_1 - Output: matest0_1.id, matest0_1.name - -> Seq Scan on public.matest1 matest0_2 - Output: matest0_2.id, matest0_2.name - -> Seq Scan on public.matest2 matest0_3 - Output: matest0_3.id, matest0_3.name - -> Seq Scan on public.matest3 matest0_4 - Output: matest0_4.id, matest0_4.name -(14 rows) + QUERY PLAN +------------------------------------------------------------------ + Result + Output: matest0.id, matest0.name + -> Sort + Output: matest0.id, matest0.name, ((1 - matest0.id)) + Sort Key: ((1 - matest0.id)) + -> Result + Output: matest0.id, matest0.name, (1 - matest0.id) + -> Append + -> Seq Scan on public.matest0 matest0_1 + Output: matest0_1.id, matest0_1.name + -> Seq Scan on public.matest1 matest0_2 + Output: matest0_2.id, matest0_2.name + -> Seq Scan on public.matest2 matest0_3 + Output: matest0_3.id, matest0_3.name + -> Seq Scan on public.matest3 matest0_4 + Output: matest0_4.id, matest0_4.name +(16 rows) select * from matest0 order by 1-id; id | name @@ -1594,22 +1596,24 @@ 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 ------------------------------------------------------------------------- - Merge Append - Sort Key: ((1 - matest0.id)) - -> Index Scan using matest0i on public.matest0 matest0_1 - Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id) - -> Index Scan using matest1i on public.matest1 matest0_2 - Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id) - -> Sort - Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id)) - Sort Key: ((1 - matest0_3.id)) - -> Seq Scan on public.matest2 matest0_3 - Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id) - -> Index Scan using matest3i on public.matest3 matest0_4 - Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id) -(13 rows) + QUERY PLAN +------------------------------------------------------------------------------ + Result + Output: matest0.id, matest0.name + -> Merge Append + Sort Key: ((1 - matest0.id)) + -> Index Scan using matest0i on public.matest0 matest0_1 + Output: matest0_1.id, matest0_1.name, (1 - matest0_1.id) + -> Index Scan using matest1i on public.matest1 matest0_2 + Output: matest0_2.id, matest0_2.name, (1 - matest0_2.id) + -> Sort + Output: matest0_3.id, matest0_3.name, ((1 - matest0_3.id)) + Sort Key: ((1 - matest0_3.id)) + -> Seq Scan on public.matest2 matest0_3 + Output: matest0_3.id, matest0_3.name, (1 - matest0_3.id) + -> Index Scan using matest3i on public.matest3 matest0_4 + Output: matest0_4.id, matest0_4.name, (1 - matest0_4.id) +(15 rows) select * from matest0 order by 1-id; id | name @@ -2827,46 +2831,49 @@ drop table parted_minmax; create index mcrparted_a_abs_c_idx on mcrparted (a, abs(b), c); -- MergeAppend must be used when a default partition exists explain (costs off) select * from mcrparted order by a, abs(b), c; - QUERY PLAN -------------------------------------------------------------------------------- - Merge Append - Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c - -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 - -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 - -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7 -(9 rows) + QUERY PLAN +------------------------------------------------------------------------------------- + Result + -> Merge Append + Sort Key: mcrparted.a, (abs(mcrparted.b)), mcrparted.c + -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 + -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 + -> Index Scan using mcrparted_def_a_abs_c_idx on mcrparted_def mcrparted_7 +(10 rows) drop table mcrparted_def; -- Append is used for a RANGE partitioned table with no default -- and no subpartitions explain (costs off) select * from mcrparted order by a, abs(b), c; - QUERY PLAN -------------------------------------------------------------------------- - Append - -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 - -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 -(7 rows) + QUERY PLAN +------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 + -> Index Scan using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 +(8 rows) -- Append is used with subpaths in reverse order with backwards index scans explain (costs off) select * from mcrparted order by a desc, abs(b) desc, c desc; - QUERY PLAN ----------------------------------------------------------------------------------- - Append - -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 - -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 - -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 -(7 rows) + QUERY PLAN +---------------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan Backward using mcrparted5_a_abs_c_idx on mcrparted5 mcrparted_6 + -> Index Scan Backward using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 + -> Index Scan Backward using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + -> Index Scan Backward using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + -> Index Scan Backward using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + -> Index Scan Backward using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 +(8 rows) -- check that Append plan is used containing a MergeAppend for sub-partitions -- that are unordered. @@ -2875,51 +2882,54 @@ create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to ( create table mcrparted5a partition of mcrparted5 for values in(20); create table mcrparted5_def partition of mcrparted5 default; explain (costs off) select * from mcrparted order by a, abs(b), c; - QUERY PLAN ---------------------------------------------------------------------------------------- - Append - -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 - -> Merge Append - Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c - -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7 - -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8 -(10 rows) + QUERY PLAN +--------------------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 + -> Merge Append + Sort Key: mcrparted_7.a, (abs(mcrparted_7.b)), mcrparted_7.c + -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_7 + -> Index Scan using mcrparted5_def_a_abs_c_idx on mcrparted5_def mcrparted_8 +(11 rows) drop table mcrparted5_def; -- check that an Append plan is used and the sub-partitions are flattened -- into the main Append when the sub-partition is unordered but contains -- just a single sub-partition. explain (costs off) select a, abs(b) from mcrparted order by a, abs(b), c; - QUERY PLAN ---------------------------------------------------------------------------- - Append - -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 - -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6 -(7 rows) + QUERY PLAN +--------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + -> Index Scan using mcrparted4_a_abs_c_idx on mcrparted4 mcrparted_5 + -> Index Scan using mcrparted5a_a_abs_c_idx on mcrparted5a mcrparted_6 +(8 rows) -- check that Append is used when the sub-partitioned tables are pruned -- during planning. explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c; - QUERY PLAN -------------------------------------------------------------------------- - Append - -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 - Index Cond: (a < 20) - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - Index Cond: (a < 20) - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - Index Cond: (a < 20) - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - Index Cond: (a < 20) -(9 rows) + QUERY PLAN +------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan using mcrparted0_a_abs_c_idx on mcrparted0 mcrparted_1 + Index Cond: (a < 20) + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + Index Cond: (a < 20) + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + Index Cond: (a < 20) + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + Index Cond: (a < 20) +(10 rows) set enable_bitmapscan to off; set enable_sort to off; @@ -3066,34 +3076,36 @@ create index on mcrparted2 (a, abs(b), c); create index on mcrparted3 (a, abs(b), c); create index on mcrparted4 (a, abs(b), c); explain (costs off) select * from mcrparted where a < 20 order by a, abs(b), c limit 1; - QUERY PLAN -------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------- Limit - -> Append - -> Sort - Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c - -> Seq Scan on mcrparted0 mcrparted_1 - Filter: (a < 20) - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 - Index Cond: (a < 20) - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 - Index Cond: (a < 20) - -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 - Index Cond: (a < 20) -(12 rows) + -> Result + -> Append + -> Sort + Sort Key: mcrparted_1.a, (abs(mcrparted_1.b)), mcrparted_1.c + -> Seq Scan on mcrparted0 mcrparted_1 + Filter: (a < 20) + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_2 + Index Cond: (a < 20) + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_3 + Index Cond: (a < 20) + -> Index Scan using mcrparted3_a_abs_c_idx on mcrparted3 mcrparted_4 + Index Cond: (a < 20) +(13 rows) set enable_bitmapscan = 0; -- Ensure Append node can be used when the partition is ordered by some -- pathkeys which were deemed redundant. explain (costs off) select * from mcrparted where a = 10 order by a, abs(b), c; - QUERY PLAN -------------------------------------------------------------------------- - Append - -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1 - Index Cond: (a = 10) - -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2 - Index Cond: (a = 10) -(5 rows) + QUERY PLAN +------------------------------------------------------------------------------- + Result + -> Append + -> Index Scan using mcrparted1_a_abs_c_idx on mcrparted1 mcrparted_1 + Index Cond: (a = 10) + -> Index Scan using mcrparted2_a_abs_c_idx on mcrparted2 mcrparted_2 + Index Cond: (a = 10) +(6 rows) reset enable_bitmapscan; drop table mcrparted; diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 2c73270143b..9708afefbfc 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -8209,30 +8209,32 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss on t1.a = ss.t2a order by t1.a; - QUERY PLAN --------------------------------------------------------------------- - Sort - Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a - Sort Key: t1.a - -> Nested Loop Left Join + QUERY PLAN +-------------------------------------------------------------------------- + Result + Output: t1.b, (LEAST(t1.a, t2.a, t3.a)) + -> Sort Output: 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) - Hash Cond: (t3.b = t2.a) - -> Seq Scan on public.join_ut1 t3 - Output: t3.a, t3.b, t3.c - -> Hash - Output: t2.a - -> Append - -> Seq Scan on public.join_pt1p1p1 t2_1 - Output: t2_1.a - Filter: (t1.a = t2_1.a) - -> Seq Scan on public.join_pt1p2 t2_2 - Output: t2_2.a - Filter: (t1.a = t2_2.a) -(21 rows) + Sort Key: t1.a + -> Nested Loop Left Join + Output: 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) + Hash Cond: (t3.b = t2.a) + -> Seq Scan on public.join_ut1 t3 + Output: t3.a, t3.b, t3.c + -> Hash + Output: t2.a + -> Append + -> Seq Scan on public.join_pt1p1p1 t2_1 + Output: t2_1.a + Filter: (t1.a = t2_1.a) + -> Seq Scan on public.join_pt1p2 t2_2 + Output: t2_2.a + Filter: (t1.a = t2_2.a) +(23 rows) select t1.b, ss.phv from join_ut1 t1 left join lateral (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index 60e572c53f2..5aab9cdd6f7 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -465,18 +465,20 @@ select unique1, unique2, generate_series(1,10) explain (verbose, costs off) select unique1, unique2, generate_series(1,10) from tenk1 order by tenthous limit 7; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Limit - Output: unique1, unique2, (generate_series(1, 10)), tenthous - -> ProjectSet - Output: unique1, unique2, generate_series(1, 10), tenthous - -> Sort - Output: unique1, unique2, tenthous - Sort Key: tenk1.tenthous - -> Seq Scan on public.tenk1 + Output: unique1, unique2, (generate_series(1, 10)) + -> Result + Output: unique1, unique2, (generate_series(1, 10)) + -> ProjectSet + Output: unique1, unique2, generate_series(1, 10), tenthous + -> Sort Output: unique1, unique2, tenthous -(9 rows) + Sort Key: tenk1.tenthous + -> Seq Scan on public.tenk1 + Output: unique1, unique2, tenthous +(11 rows) select unique1, unique2, generate_series(1,10) from tenk1 order by tenthous limit 7; diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index 1b900fddf8e..462fe6cb775 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -862,24 +862,25 @@ SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 -- Full aggregation as GROUP BY clause matches with PARTITION KEY EXPLAIN (COSTS OFF) SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c)) - -> Append - -> HashAggregate - Group Key: pagg_tab_m.a, ((pagg_tab_m.a + pagg_tab_m.b) / 2) - Filter: (sum(pagg_tab_m.b) < 50) - -> Seq Scan on pagg_tab_m_p1 pagg_tab_m - -> HashAggregate - Group Key: pagg_tab_m_1.a, ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2) - Filter: (sum(pagg_tab_m_1.b) < 50) - -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 - -> HashAggregate - Group Key: pagg_tab_m_2.a, ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2) - Filter: (sum(pagg_tab_m_2.b) < 50) - -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 -(15 rows) + QUERY PLAN +---------------------------------------------------------------------------------------- + Result + -> Sort + Sort Key: pagg_tab_m.a, (sum(pagg_tab_m.b)), (avg(pagg_tab_m.c)) + -> Append + -> HashAggregate + Group Key: pagg_tab_m.a, ((pagg_tab_m.a + pagg_tab_m.b) / 2) + Filter: (sum(pagg_tab_m.b) < 50) + -> Seq Scan on pagg_tab_m_p1 pagg_tab_m + -> HashAggregate + Group Key: pagg_tab_m_1.a, ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2) + Filter: (sum(pagg_tab_m_1.b) < 50) + -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 + -> HashAggregate + Group Key: pagg_tab_m_2.a, ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2) + Filter: (sum(pagg_tab_m_2.b) < 50) + -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 +(16 rows) SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; a | sum | avg | count @@ -895,24 +896,25 @@ SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING su -- Full aggregation as PARTITION KEY is part of GROUP BY clause EXPLAIN (COSTS OFF) SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; - QUERY PLAN --------------------------------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_m.a, pagg_tab_m.c, (sum(pagg_tab_m.b)) - -> Append - -> HashAggregate - Group Key: ((pagg_tab_m.a + pagg_tab_m.b) / 2), pagg_tab_m.c, pagg_tab_m.a - Filter: ((sum(pagg_tab_m.b) = 50) AND (avg(pagg_tab_m.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p1 pagg_tab_m - -> HashAggregate - Group Key: ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2), pagg_tab_m_1.c, pagg_tab_m_1.a - Filter: ((sum(pagg_tab_m_1.b) = 50) AND (avg(pagg_tab_m_1.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 - -> HashAggregate - Group Key: ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2), pagg_tab_m_2.c, pagg_tab_m_2.a - Filter: ((sum(pagg_tab_m_2.b) = 50) AND (avg(pagg_tab_m_2.c) > '25'::numeric)) - -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 -(15 rows) + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Result + -> Sort + Sort Key: pagg_tab_m.a, pagg_tab_m.c, (sum(pagg_tab_m.b)) + -> Append + -> HashAggregate + Group Key: ((pagg_tab_m.a + pagg_tab_m.b) / 2), pagg_tab_m.c, pagg_tab_m.a + Filter: ((sum(pagg_tab_m.b) = 50) AND (avg(pagg_tab_m.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p1 pagg_tab_m + -> HashAggregate + Group Key: ((pagg_tab_m_1.a + pagg_tab_m_1.b) / 2), pagg_tab_m_1.c, pagg_tab_m_1.a + Filter: ((sum(pagg_tab_m_1.b) = 50) AND (avg(pagg_tab_m_1.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p2 pagg_tab_m_1 + -> HashAggregate + Group Key: ((pagg_tab_m_2.a + pagg_tab_m_2.b) / 2), pagg_tab_m_2.c, pagg_tab_m_2.a + Filter: ((sum(pagg_tab_m_2.b) = 50) AND (avg(pagg_tab_m_2.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p3 pagg_tab_m_2 +(16 rows) SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; a | c | sum | avg | count @@ -1123,32 +1125,33 @@ SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------- - Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Append - -> HashAggregate - Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c - Filter: (avg(pagg_tab_ml.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> HashAggregate - Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c - Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> HashAggregate - Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c - Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 - -> HashAggregate - Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c - Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> HashAggregate - Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c - Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 -(23 rows) + QUERY PLAN +---------------------------------------------------------------------------------- + Result + -> Sort + Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) + -> Append + -> HashAggregate + Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c + Filter: (avg(pagg_tab_ml.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml + -> HashAggregate + Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c + Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 + -> HashAggregate + Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c + Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 + -> HashAggregate + Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c + Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 + -> HashAggregate + Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c + Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 +(24 rows) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; a | sum | count @@ -1279,34 +1282,35 @@ SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER -- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY EXPLAIN (COSTS OFF) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------- Gather Merge Workers Planned: 2 - -> Sort - Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) - -> Parallel Append - -> HashAggregate - Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c - Filter: (avg(pagg_tab_ml.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml - -> HashAggregate - Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c - Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 - -> HashAggregate - Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c - Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 - -> HashAggregate - Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c - Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 - -> HashAggregate - Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c - Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) - -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 -(25 rows) + -> Result + -> Sort + Sort Key: pagg_tab_ml.a, (sum(pagg_tab_ml.b)), (count(*)) + -> Parallel Append + -> HashAggregate + Group Key: pagg_tab_ml.a, pagg_tab_ml.b, pagg_tab_ml.c + Filter: (avg(pagg_tab_ml.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml + -> HashAggregate + Group Key: pagg_tab_ml_3.a, pagg_tab_ml_3.b, pagg_tab_ml_3.c + Filter: (avg(pagg_tab_ml_3.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3 + -> HashAggregate + Group Key: pagg_tab_ml_1.a, pagg_tab_ml_1.b, pagg_tab_ml_1.c + Filter: (avg(pagg_tab_ml_1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1 + -> HashAggregate + Group Key: pagg_tab_ml_4.a, pagg_tab_ml_4.b, pagg_tab_ml_4.c + Filter: (avg(pagg_tab_ml_4.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4 + -> HashAggregate + Group Key: pagg_tab_ml_2.a, pagg_tab_ml_2.b, pagg_tab_ml_2.c + Filter: (avg(pagg_tab_ml_2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2 +(26 rows) SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; a | sum | count diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6560fe2416f..c0edb08cbad 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -111,25 +111,26 @@ SELECT COUNT(*) FROM prt1 t1 -- left outer join, with whole-row reference; partitionwise join does not apply EXPLAIN (COSTS OFF) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; - QUERY PLAN --------------------------------------------------- - Sort - Sort Key: t1.a, t2.b - -> Hash Right Join - Hash Cond: (t2.b = t1.a) - -> Append - -> Seq Scan on prt2_p1 t2_1 - -> Seq Scan on prt2_p2 t2_2 - -> Seq Scan on prt2_p3 t2_3 - -> Hash + QUERY PLAN +-------------------------------------------------------- + Result + -> Sort + Sort Key: t1.a, t2.b + -> Hash Right Join + Hash Cond: (t2.b = t1.a) -> Append - -> Seq Scan on prt1_p1 t1_1 - Filter: (b = 0) - -> Seq Scan on prt1_p2 t1_2 - Filter: (b = 0) - -> Seq Scan on prt1_p3 t1_3 - Filter: (b = 0) -(16 rows) + -> Seq Scan on prt2_p1 t2_1 + -> Seq Scan on prt2_p2 t2_2 + -> Seq Scan on prt2_p3 t2_3 + -> Hash + -> Append + -> Seq Scan on prt1_p1 t1_1 + Filter: (b = 0) + -> Seq Scan on prt1_p2 t1_2 + Filter: (b = 0) + -> Seq Scan on prt1_p3 t1_3 + Filter: (b = 0) +(17 rows) SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; t1 | t2 diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 9a4c48c0556..249825c4f33 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -3316,18 +3316,19 @@ create index on ma_test (b); analyze ma_test; prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b; explain (analyze, costs off, summary off, timing off) execute mt_q1(15); - QUERY PLAN ------------------------------------------------------------------------------------------ - Merge Append (actual rows=2 loops=1) - Sort Key: ma_test.b - Subplans Removed: 1 - -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(9 rows) + QUERY PLAN +----------------------------------------------------------------------------------------------- + Result (actual rows=2 loops=1) + -> Merge Append (actual rows=2 loops=1) + Sort Key: ma_test.b + Subplans Removed: 1 + -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 + -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 +(10 rows) execute mt_q1(15); a @@ -3337,15 +3338,16 @@ execute mt_q1(15); (2 rows) explain (analyze, costs off, summary off, timing off) execute mt_q1(25); - QUERY PLAN ------------------------------------------------------------------------------------------ - Merge Append (actual rows=1 loops=1) - Sort Key: ma_test.b - Subplans Removed: 2 - -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) - Filter: ((a >= $1) AND ((a % 10) = 5)) - Rows Removed by Filter: 9 -(6 rows) + QUERY PLAN +----------------------------------------------------------------------------------------------- + Result (actual rows=1 loops=1) + -> Merge Append (actual rows=1 loops=1) + Sort Key: ma_test.b + Subplans Removed: 2 + -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1) + Filter: ((a >= $1) AND ((a % 10) = 5)) + Rows Removed by Filter: 9 +(7 rows) execute mt_q1(25); a @@ -3355,12 +3357,13 @@ execute mt_q1(25); -- Ensure MergeAppend behaves correctly when no subplans match explain (analyze, costs off, summary off, timing off) execute mt_q1(35); - QUERY PLAN --------------------------------------- - Merge Append (actual rows=0 loops=1) - Sort Key: ma_test.b - Subplans Removed: 3 -(3 rows) + QUERY PLAN +-------------------------------------------- + Result (actual rows=0 loops=1) + -> Merge Append (actual rows=0 loops=1) + Sort Key: ma_test.b + Subplans Removed: 3 +(4 rows) execute mt_q1(35); a diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 6988128aa4c..fe82211f974 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -4437,18 +4437,20 @@ INSERT INTO rls_tbl SELECT 10, 20, 30; EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO rls_tbl SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss; - QUERY PLAN --------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------- Insert on regress_rls_schema.rls_tbl -> Subquery Scan on ss Output: 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 + -> Result + Output: rls_tbl_1.b, rls_tbl_1.c + -> Sort Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a - Filter: (rls_tbl_1.* >= '(1,1,1)'::record) -(9 rows) + 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 + Filter: (rls_tbl_1.* >= '(1,1,1)'::record) +(11 rows) INSERT INTO rls_tbl SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss; diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index d88353d496c..da6cdf00fed 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -699,19 +699,20 @@ drop function sp_simple_func(integer); -- test handling of SRFs in targetlist (bug in 10.0) explain (costs off) select count(*), generate_series(1,2) from tenk1 group by twenty; - QUERY PLAN ----------------------------------------------------------- - ProjectSet - -> Finalize GroupAggregate - Group Key: twenty - -> Gather Merge - Workers Planned: 4 - -> Partial GroupAggregate - Group Key: twenty - -> Sort - Sort Key: twenty - -> Parallel Seq Scan on tenk1 -(10 rows) + QUERY PLAN +---------------------------------------------------------------- + Result + -> ProjectSet + -> Finalize GroupAggregate + Group Key: twenty + -> Gather Merge + Workers Planned: 4 + -> Partial GroupAggregate + Group Key: twenty + -> Sort + Sort Key: twenty + -> Parallel Seq Scan on tenk1 +(11 rows) select count(*), generate_series(1,2) from tenk1 group by twenty; count | generate_series @@ -1129,25 +1130,27 @@ ORDER BY 1, 2, 3; EXPLAIN (VERBOSE, COSTS OFF) SELECT generate_series(1, two), array(select generate_series(1, two)) FROM tenk1 ORDER BY tenthous; - QUERY PLAN ----------------------------------------------------------------------- - ProjectSet - Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous - -> Gather Merge - Output: tenk1.two, tenk1.tenthous - Workers Planned: 4 - -> Result + QUERY PLAN +---------------------------------------------------------------------------- + Result + Output: (generate_series(1, tenk1.two)), ((SubPlan 1)) + -> ProjectSet + Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous + -> Gather Merge Output: tenk1.two, tenk1.tenthous - -> Sort - Output: tenk1.tenthous, tenk1.two - Sort Key: tenk1.tenthous - -> Parallel Seq Scan on public.tenk1 + Workers Planned: 4 + -> Result + Output: tenk1.two, tenk1.tenthous + -> Sort Output: tenk1.tenthous, tenk1.two - SubPlan 1 - -> ProjectSet - Output: generate_series(1, tenk1.two) - -> Result -(16 rows) + Sort Key: tenk1.tenthous + -> Parallel Seq Scan on public.tenk1 + Output: tenk1.tenthous, tenk1.two + SubPlan 1 + -> ProjectSet + Output: generate_series(1, tenk1.two) + -> Result +(18 rows) -- must disallow pushing sort below gather when pathkey contains an SRF EXPLAIN (VERBOSE, COSTS OFF) diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index d47b5f6ec57..98195e8c8c1 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -666,17 +666,19 @@ 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 ------------------------------------------------------------------------- - 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)) - -> ProjectSet - Output: generate_series(1, 3) - -> Result -(8 rows) + QUERY PLAN +------------------------------------------------------------------------------ + Result + Output: (((generate_series(1, 3)) + 1)) + -> 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)) + -> ProjectSet + Output: generate_series(1, 3) + -> Result +(10 rows) select generate_series(1,3)+1 order by generate_series(1,3); ?column? -- 2.39.2