On 4/25/25 17:13, Andrei Lepikhov wrote:
After a second thought I have caught your idea. I agree that for a fractional path it have no sense to choose any other path except a cheapest total one.On 4/25/25 11:16, Alexander Pyhalov wrote:Usually, sorted cheapest_total_path will be cheaper than sorted fractional/startup path at least by startup cost (as after sorting it includes total_cost of input path). But we ignore this case when selecting cheapest_startup and cheapest_fractional subpaths. As result selected cheapest_startup and cheapest_fractional can be not cheapest for startup or selecting a fraction of rows.I don't know what you mean by that. The cheapest_total_path is considered when we chose optimal cheapest_total path. The same works for the fractional path - get_cheapest_fractional_path gives us the most optimal fractional path and probes cheapest_total_path too. As above, not sure about min-startup case for now. I can imagine MergeAppend over sophisticated subquery: non-sorted includes highly parameterised JOINs and the alternative (with pathkeys) includes HashJoin, drastically increasing startup cost. It is only a theory, of course. So, lets discover how min-startup works.
There are the modified patch in the attachment.
Also, to be more objective, I propose to use examples in argumentation - something like in attached test2.sql script.
-- regards, Andrei Lepikhov
From efdea920593e232b9732e8f5288a6fbffd4ce4ce Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepi...@gmail.com>
Date: Thu, 24 Apr 2025 14:03:02 +0200
Subject: [PATCH v1] Consider explicit sort of the MergeAppend subpaths.
Expand the optimiser search scope a little: fetching optimal subpath matching
pathkeys of the planning MergeAppend, consider the extra case of
overall-optimal path plus explicit Sort node at the top.
It may provide a more effective plan in both full and fractional scan cases:
1. The Sort node may be pushed down to subpaths under a parallel or
async Append.
2. The case when a minor set of subpaths doesn't have a proper index, and it
is profitable to sort them instead of switching to plain Append.
In general, analysing the regression tests changed by this code, it seems that
the cost model prefers a series of small sortings instead of a single massive
one. This feature increases a little the number of such paths.
Overhead:
It seems multiple subpaths may be encountered, as well as many pathkeys.
So, to be as careful as possible here, only cost estimation is performed.
---
.../postgres_fdw/expected/postgres_fdw.out | 6 +-
src/backend/optimizer/path/allpaths.c | 36 ++---
src/backend/optimizer/path/pathkeys.c | 97 ++++++++++++
src/include/optimizer/paths.h | 10 ++
.../regress/expected/collate.icu.utf8.out | 9 +-
src/test/regress/expected/inherit.out | 19 ++-
src/test/regress/expected/partition_join.out | 139 +++++++++++-------
src/test/regress/expected/partition_prune.out | 16 +-
src/test/regress/expected/union.out | 6 +-
src/test/regress/sql/inherit.sql | 4 +-
10 files changed, 241 insertions(+), 101 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d1acee5a5fa..11b42f18cb6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10277,13 +10277,15 @@ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a
-> Nested Loop
Join Filter: (t1.a = t2.b)
-> Append
- -> Foreign Scan on ftprt1_p1 t1_1
+ -> Sort
+ Sort Key: t1_1.a
+ -> Foreign Scan on ftprt1_p1 t1_1
-> Foreign Scan on ftprt1_p2 t1_2
-> Materialize
-> Append
-> Foreign Scan on ftprt2_p1 t2_1
-> Foreign Scan on ftprt2_p2 t2_2
-(10 rows)
+(12 rows)
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
a | b
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 905250b3325..0ffb13dffec 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1855,29 +1855,14 @@ generate_orderedappend_paths(PlannerInfo *root, RelOptInfo *rel,
/* Locate the right paths, if they are available. */
cheapest_startup =
- get_cheapest_path_for_pathkeys(childrel->pathlist,
- pathkeys,
- NULL,
- STARTUP_COST,
- false);
+ get_cheapest_path_for_pathkeys_ext(root, childrel, pathkeys,
+ NULL, STARTUP_COST, false);
cheapest_total =
- get_cheapest_path_for_pathkeys(childrel->pathlist,
- pathkeys,
- NULL,
- TOTAL_COST,
- false);
+ get_cheapest_path_for_pathkeys_ext(root, childrel, pathkeys,
+ NULL, TOTAL_COST, false);
- /*
- * If we can't find any paths with the right order just use the
- * cheapest-total path; we'll have to sort it later.
- */
- if (cheapest_startup == NULL || cheapest_total == NULL)
- {
- cheapest_startup = cheapest_total =
- childrel->cheapest_total_path;
- /* Assert we do have an unparameterized path for this child */
- Assert(cheapest_total->param_info == NULL);
- }
+ Assert(cheapest_startup != NULL && cheapest_total != NULL);
+ Assert(cheapest_total->param_info == NULL);
/*
* When building a fractional path, determine a cheapest
@@ -1894,10 +1879,11 @@ generate_orderedappend_paths(PlannerInfo *root, RelOptInfo *rel,
double path_fraction = (1.0 / root->tuple_fraction);
cheapest_fractional =
- get_cheapest_fractional_path_for_pathkeys(childrel->pathlist,
- pathkeys,
- NULL,
- path_fraction);
+ get_cheapest_fractional_path_for_pathkeys_ext(root,
+ childrel,
+ pathkeys,
+ NULL,
+ path_fraction);
/*
* If we found no path with matching pathkeys, use the
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 8b04d40d36d..a39059c5bc6 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -19,11 +19,13 @@
#include "access/stratnum.h"
#include "catalog/pg_opfamily.h"
+#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/planner.h"
#include "partitioning/partbounds.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
@@ -648,6 +650,58 @@ get_cheapest_path_for_pathkeys(List *paths, List *pathkeys,
return matched_path;
}
+/*
+ * get_cheapest_path_for_pathkeys_ext
+ * Calls get_cheapest_path_for_pathkeys to obtain cheapest path that
+ * satisfies defined criterias and сonsiders one more option: choose
+ * overall-optimal path (according the criterion) and explicitly sort its
+ * output to satisfy the pathkeys.
+ *
+ * Caller is responsible to insert corresponding sort path at the top of
+ * returned path if it will be chosen to be used.
+ *
+ * Return NULL if no such path.
+ */
+Path *
+get_cheapest_path_for_pathkeys_ext(PlannerInfo *root, RelOptInfo *rel,
+ List *pathkeys, Relids required_outer,
+ CostSelector cost_criterion,
+ bool require_parallel_safe)
+{
+ Path sort_path;
+ Path *base_path = rel->cheapest_total_path;
+ Path *path;
+
+ path = get_cheapest_path_for_pathkeys(rel->pathlist, pathkeys,
+ required_outer, cost_criterion,
+ require_parallel_safe);
+
+
+ if (path == NULL)
+ /*
+ * Current pathlist doesn't fit the pathkeys. No need to check extra
+ * sort path ways.
+ */
+ return base_path;
+
+ /* Stop here if the path doesn't satisfy necessary conditions */
+ if ((require_parallel_safe && !base_path->parallel_safe) ||
+ !bms_is_subset(PATH_REQ_OUTER(base_path), required_outer))
+ return path;
+
+ /* Consider the most startup-optimal path with extra sort */
+ if (base_path && path != base_path)
+ {
+ cost_sort(&sort_path, root, pathkeys, base_path->disabled_nodes,
+ base_path->total_cost, base_path->rows,
+ base_path->pathtarget->width, 0.0, work_mem, -1.0);
+
+ if (compare_path_costs(&sort_path, path, cost_criterion) < 0)
+ return base_path;
+ }
+ return path;
+}
+
/*
* get_cheapest_fractional_path_for_pathkeys
* Find the cheapest path (for retrieving a specified fraction of all
@@ -690,6 +744,49 @@ get_cheapest_fractional_path_for_pathkeys(List *paths,
return matched_path;
}
+/*
+ * get_cheapest_fractional_path_for_pathkeys_ext
+ * obtain cheapest fractional path that satisfies defined criterias excluding
+ * pathkeys and explicitly sort its output to satisfy the pathkeys.
+ *
+ * Caller is responsible to insert corresponding sort path at the top of
+ * returned path if it will be chosen to be used.
+ *
+ * Return NULL if no such path.
+ */
+Path *
+get_cheapest_fractional_path_for_pathkeys_ext(PlannerInfo *root,
+ RelOptInfo *rel,
+ List *pathkeys,
+ Relids required_outer,
+ double fraction)
+{
+ Path sort_path;
+ Path *base_path;
+ Path *path;
+
+ path = get_cheapest_fractional_path_for_pathkeys(rel->pathlist, pathkeys,
+ required_outer, fraction);
+
+ base_path = rel->cheapest_total_path;
+
+ /* Stop here if the path doesn't satisfy necessary conditions */
+ if (!base_path || !bms_is_subset(PATH_REQ_OUTER(base_path), required_outer))
+ return path;
+
+ if (path != base_path)
+ {
+ cost_sort(&sort_path, root, pathkeys, base_path->disabled_nodes,
+ base_path->total_cost, base_path->rows,
+ base_path->pathtarget->width, 0.0, work_mem, -1.0);
+
+ if (!path ||
+ compare_fractional_path_costs(&sort_path, path, fraction) <= 0)
+ return base_path;
+ }
+
+ return path;
+}
/*
* get_cheapest_parallel_safe_total_inner
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index a48c9721797..4bdc85afca9 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -224,10 +224,20 @@ extern Path *get_cheapest_path_for_pathkeys(List *paths, List *pathkeys,
Relids required_outer,
CostSelector cost_criterion,
bool require_parallel_safe);
+extern Path *get_cheapest_path_for_pathkeys_ext(PlannerInfo *root,
+ RelOptInfo *rel, List *pathkeys,
+ Relids required_outer,
+ CostSelector cost_criterion,
+ bool require_parallel_safe);
extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
List *pathkeys,
Relids required_outer,
double fraction);
+extern Path *get_cheapest_fractional_path_for_pathkeys_ext(PlannerInfo *root,
+ RelOptInfo *rel,
+ List *pathkeys,
+ Relids required_outer,
+ double fraction);
extern Path *get_cheapest_parallel_safe_total_inner(List *paths);
extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
ScanDirection scandir);
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 69805d4b9ec..48d47bb7455 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2412,16 +2412,19 @@ EXPLAIN (COSTS OFF)
SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1;
QUERY PLAN
--------------------------------------------------------
- Sort
+ Merge Append
Sort Key: ((pagg_tab3.c)::text) COLLATE "C"
- -> Append
+ -> Sort
+ Sort Key: ((pagg_tab3.c)::text) COLLATE "C"
-> HashAggregate
Group Key: (pagg_tab3.c)::text
-> Seq Scan on pagg_tab3_p2 pagg_tab3
+ -> Sort
+ Sort Key: ((pagg_tab3_1.c)::text) COLLATE "C"
-> HashAggregate
Group Key: (pagg_tab3_1.c)::text
-> Seq Scan on pagg_tab3_p1 pagg_tab3_1
-(9 rows)
+(12 rows)
SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1;
c | count
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f9b0c415cfd..71036dc938f 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1665,11 +1665,13 @@ insert into matest2 (name) values ('Test 3');
insert into matest2 (name) values ('Test 4');
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
+set enable_indexscan = off; -- force use of seqscan/sort
+set enable_sort = off; -- since merge append may employ sort in children we need to disable sort
explain (verbose, costs off) select * from matest0 order by 1-id;
QUERY PLAN
------------------------------------------------------------
Sort
+ Disabled: true
Output: matest0.id, matest0.name, ((1 - matest0.id))
Sort Key: ((1 - matest0.id))
-> Result
@@ -1683,7 +1685,7 @@ explain (verbose, costs off) select * from matest0 order by 1-id;
Output: matest0_3.id, matest0_3.name
-> Seq Scan on public.matest3 matest0_4
Output: matest0_4.id, matest0_4.name
-(14 rows)
+(15 rows)
select * from matest0 order by 1-id;
id | name
@@ -1719,6 +1721,7 @@ select min(1-id) from matest0;
(1 row)
reset enable_indexscan;
+reset enable_sort;
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;
@@ -1844,16 +1847,20 @@ order by t1.b limit 10;
Merge Cond: (t1.b = t2.b)
-> Merge Append
Sort Key: t1.b
- -> Index Scan using matest0i on matest0 t1_1
+ -> Sort
+ Sort Key: t1_1.b
+ -> Seq Scan on matest0 t1_1
-> Index Scan using matest1i on matest1 t1_2
-> Materialize
-> Merge Append
Sort Key: t2.b
- -> Index Scan using matest0i on matest0 t2_1
- Filter: (c = d)
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on matest0 t2_1
+ Filter: (c = d)
-> Index Scan using matest1i on matest1 t2_2
Filter: (c = d)
-(14 rows)
+(18 rows)
reset enable_nestloop;
drop table matest0 cascade;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6101c8c7cf1..d41979367c9 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -65,31 +65,34 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
-- inner join with partially-redundant join clauses
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------
- Sort
+ QUERY PLAN
+---------------------------------------------------------
+ Merge Append
Sort Key: t1.a
- -> Append
- -> Merge Join
- Merge Cond: (t1_1.a = t2_1.a)
- -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
- -> Sort
- Sort Key: t2_1.b
- -> Seq Scan on prt2_p1 t2_1
- Filter: (a = b)
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.a)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p1 t2_1
+ Filter: (a = b)
+ -> Sort
+ Sort Key: t1_2.a
-> Hash Join
Hash Cond: (t1_2.a = t2_2.a)
-> Seq Scan on prt1_p2 t1_2
-> Hash
-> Seq Scan on prt2_p2 t2_2
Filter: (a = b)
+ -> Sort
+ Sort Key: t1_3.a
-> Hash Join
Hash Cond: (t1_3.a = t2_3.a)
-> Seq Scan on prt1_p3 t1_3
-> Hash
-> Seq Scan on prt2_p3 t2_3
Filter: (a = b)
-(22 rows)
+(25 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
a | c | b | c
@@ -1383,28 +1386,32 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-- This should generate a partitionwise join, but currently fails to
EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
------------------------------------------------------------
- Incremental Sort
+ QUERY PLAN
+-----------------------------------------------------------------
+ Sort
Sort Key: prt1.a, prt2.b
- Presorted Key: prt1.a
- -> Merge Left Join
- Merge Cond: (prt1.a = prt2.b)
- -> Sort
- Sort Key: prt1.a
- -> Append
- -> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))
- -> Seq Scan on prt1_p2 prt1_2
- Filter: ((a < 450) AND (b = 0))
- -> Sort
- Sort Key: prt2.b
- -> Append
+ -> Merge Right Join
+ Merge Cond: (prt2.b = prt1.a)
+ -> Append
+ -> Sort
+ Sort Key: prt2_1.b
-> Seq Scan on prt2_p2 prt2_1
Filter: (b > 250)
+ -> Sort
+ Sort Key: prt2_2.b
-> Seq Scan on prt2_p3 prt2_2
Filter: (b > 250)
-(19 rows)
+ -> Materialize
+ -> Append
+ -> Sort
+ Sort Key: prt1_1.a
+ -> Seq Scan on prt1_p1 prt1_1
+ Filter: ((a < 450) AND (b = 0))
+ -> Sort
+ Sort Key: prt1_2.a
+ -> Seq Scan on prt1_p2 prt1_2
+ Filter: ((a < 450) AND (b = 0))
+(23 rows)
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
a | b
@@ -1424,25 +1431,33 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
-- partitionwise join does not apply
EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
- QUERY PLAN
------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Merge Join
- Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text)))
- -> Sort
- Sort Key: t1.a, ((((t1.*)::prt1))::text)
- -> Result
- -> Append
- -> Seq Scan on prt1_p1 t1_1
- -> Seq Scan on prt1_p2 t1_2
- -> Seq Scan on prt1_p3 t1_3
- -> Sort
- Sort Key: t2.b, ((((t2.*)::prt2))::text)
- -> Result
- -> Append
+ Merge Cond: (t1.a = t2.b)
+ Join Filter: ((((t2.*)::prt2))::text = (((t1.*)::prt1))::text)
+ -> Append
+ -> Sort
+ Sort Key: t1_1.a
+ -> Seq Scan on prt1_p1 t1_1
+ -> Sort
+ Sort Key: t1_2.a
+ -> Seq Scan on prt1_p2 t1_2
+ -> Sort
+ Sort Key: t1_3.a
+ -> Seq Scan on prt1_p3 t1_3
+ -> Materialize
+ -> Append
+ -> Sort
+ Sort Key: t2_1.b
-> Seq Scan on prt2_p1 t2_1
+ -> Sort
+ Sort Key: t2_2.b
-> Seq Scan on prt2_p2 t2_2
+ -> Sort
+ Sort Key: t2_3.b
-> Seq Scan on prt2_p3 t2_3
-(16 rows)
+(24 rows)
SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
a | b
@@ -4508,9 +4523,10 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt1_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
QUERY PLAN
--------------------------------------------------------------------------------
- Sort
+ Merge Append
Sort Key: t1.a
- -> Append
+ -> Sort
+ Sort Key: t1_1.a
-> Hash Right Join
Hash Cond: ((t3_1.a = t1_1.a) AND (t3_1.c = t1_1.c))
-> Seq Scan on plt1_adv_p1 t3_1
@@ -4521,6 +4537,8 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2
-> Hash
-> Seq Scan on plt1_adv_p1 t1_1
Filter: (b < 10)
+ -> Sort
+ Sort Key: t1_2.a
-> Hash Right Join
Hash Cond: ((t3_2.a = t1_2.a) AND (t3_2.c = t1_2.c))
-> Seq Scan on plt1_adv_p2 t3_2
@@ -4531,6 +4549,8 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2
-> Hash
-> Seq Scan on plt1_adv_p2 t1_2
Filter: (b < 10)
+ -> Sort
+ Sort Key: t1_3.a
-> Hash Right Join
Hash Cond: ((t3_3.a = t1_3.a) AND (t3_3.c = t1_3.c))
-> Seq Scan on plt1_adv_p3 t3_3
@@ -4541,15 +4561,19 @@ SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2
-> Hash
-> Seq Scan on plt1_adv_p3 t1_3
Filter: (b < 10)
- -> Nested Loop Left Join
- Join Filter: ((t1_4.a = t3_4.a) AND (t1_4.c = t3_4.c))
- -> Nested Loop Left Join
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
+ -> Nested Loop Left Join
+ Join Filter: ((t1_4.a = t3_4.a) AND (t1_4.c = t3_4.c))
+ -> Merge Left Join
+ Merge Cond: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
+ -> Sort
+ Sort Key: t1_4.a, t1_4.c
-> Seq Scan on plt1_adv_extra t1_4
Filter: (b < 10)
+ -> Sort
+ Sort Key: t2_4.a, t2_4.c
-> Seq Scan on plt2_adv_extra t2_4
- -> Seq Scan on plt1_adv_extra t3_4
-(41 rows)
+ -> Seq Scan on plt1_adv_extra t3_4
+(50 rows)
SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt1_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
a | c | a | c | a | c
@@ -5037,21 +5061,26 @@ EXPLAIN (COSTS OFF)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b) WHERE t1.b >= 125 AND t1.b < 225 ORDER BY t1.a, t1.b;
QUERY PLAN
--------------------------------------------------------------------
- Sort
+ Merge Append
Sort Key: t1.a, t1.b
- -> Append
+ -> Sort
+ Sort Key: t1_1.a, t1_1.b
-> Hash Join
Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b))
-> Seq Scan on alpha_neg_p1 t1_1
Filter: ((b >= 125) AND (b < 225))
-> Hash
-> Seq Scan on beta_neg_p1 t2_1
+ -> Sort
+ Sort Key: t1_2.a, t1_2.b
-> Hash Join
Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b))
-> Seq Scan on beta_neg_p2 t2_2
-> Hash
-> Seq Scan on alpha_neg_p2 t1_2
Filter: ((b >= 125) AND (b < 225))
+ -> Sort
+ Sort Key: t1_4.a, t1_4.b
-> Hash Join
Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
-> Append
@@ -5066,7 +5095,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
Filter: ((b >= 125) AND (b < 225))
-> Seq Scan on alpha_pos_p3 t1_6
Filter: ((b >= 125) AND (b < 225))
-(29 rows)
+(34 rows)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b) WHERE t1.b >= 125 AND t1.b < 225 ORDER BY t1.a, t1.b;
a | b | c | a | b | c
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 0bf35260b46..b25aa73e946 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -4763,9 +4763,10 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc.a ORDER BY part_abc.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_1
- Index Cond: (a >= (stable_one() + 1))
- Filter: (d <= stable_one())
+ -> Sort
+ Sort Key: part_abc_1.a
+ -> Seq Scan on part_abc_2 part_abc_1
+ Filter: ((d <= stable_one()) AND (a >= (stable_one() + 1)))
-> Merge Append
Sort Key: part_abc_3.a
Subplans Removed: 1
@@ -4780,9 +4781,10 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
Window: w1 AS (PARTITION BY part_abc_5.a ORDER BY part_abc_5.a)
-> Append
Subplans Removed: 1
- -> Index Scan using part_abc_2_a_idx on part_abc_2 part_abc_6
- Index Cond: (a >= (stable_one() + 1))
- Filter: (d >= stable_one())
+ -> Sort
+ Sort Key: part_abc_6.a
+ -> Seq Scan on part_abc_2 part_abc_6
+ Filter: ((d >= stable_one()) AND (a >= (stable_one() + 1)))
-> Merge Append
Sort Key: a
Subplans Removed: 1
@@ -4792,7 +4794,7 @@ select min(a) over (partition by a order by a) from part_abc where a >= stable_o
-> Index Scan using part_abc_3_3_a_idx on part_abc_3_3 part_abc_9
Index Cond: (a >= (stable_one() + 1))
Filter: (d >= stable_one())
-(35 rows)
+(37 rows)
drop view part_abc_view;
drop table part_abc;
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 96962817ed4..0ccadea910c 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1207,12 +1207,14 @@ select event_id
----------------------------------------------------------
Merge Append
Sort Key: events.event_id
- -> Index Scan using events_pkey on events
+ -> Sort
+ Sort Key: events.event_id
+ -> Seq Scan on events
-> Sort
Sort Key: events_1.event_id
-> Seq Scan on events_child events_1
-> Index Scan using other_events_pkey on other_events
-(7 rows)
+(9 rows)
drop table events_child, events, other_events;
reset enable_indexonlyscan;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..c58beebbd1e 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -641,12 +641,14 @@ insert into matest2 (name) values ('Test 4');
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
+set enable_indexscan = off; -- force use of seqscan/sort
+set enable_sort = off; -- since merge append may employ sort in children we need to disable sort
explain (verbose, costs off) select * from matest0 order by 1-id;
select * from matest0 order by 1-id;
explain (verbose, costs off) select min(1-id) from matest0;
select min(1-id) from matest0;
reset enable_indexscan;
+reset enable_sort;
set enable_seqscan = off; -- plan with fewest seqscans should be merge
set enable_parallel_append = off; -- Don't let parallel-append interfere
--
2.39.5
test2.sql
Description: application/sql
