On 3/28/25 09:19, Alexander Pyhalov wrote:
Andy Fan писал(а) 2024-10-17 03:33:
I've updated patch. One more interesting case which we found - when
fractional path is selected, it still can be more expensive than sorted
cheapest total path (as we look only on indexes whith necessary
pathkeys, not on indexes which allow efficiently fetch data).
So far couldn't find artificial example, but we've seen inadequate index
selection due to this issue - instead of using index suited for filters
in where, index, suitable for sorting was selected as one having the
cheapest fractional cost.
I think it is necessary to generalise the approach a little.
Each MergeAppend subpath candidate that fits pathkeys should be compared
to the overall-optimal path + explicit Sort node. Let's label this
two-node composition as base_path. There are three cases exist:
startup-optimal, total-optimal and fractional-optimal.
In the startup case, base_path should use cheapest_startup_path, the
total-optimal case - cheapest_total_path and for a fractional case, we
may employ the get_cheapest_fractional_path routine to detect proper
base_path.
It may provide a more effective plan either in full, fractional and
partial scan cases:
1. The Sort node may be pushed down to subpaths under a parallel or
async Append.
2. 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, I see
that the cost model prefers a series of small sortings instead of a
single massive one. May be it will show some profit for execution time.
I am not afraid of any palpable planning overhead here because we just
do cheap cost estimation and comparison operations that don't need
additional memory allocations. The caller is responsible for building a
proper Sort node if this method is chosen as optimal.
In the attachment, see the patch written according to the idea. There
are I introduced two new routines:
get_cheapest_path_for_pathkeys_ext
get_cheapest_fractional_path_for_pathkeys_ext
I have designed the code that way to reduce duplicated code in the
generate_orderedappend_paths routine. But the main point is that I
envision these new routines may be reused elsewhere.
This feature looks сlose to the one we discussed before [1]. So, let me
CC the people from the previous conversation to the discussion.
[1]
https://www.postgresql.org/message-id/flat/CAN-LCVPxnWB39CUBTgOQ9O7Dd8DrA_tpT1EY3LNVnUuvAX1NjA%40mail.gmail.com
--
regards, Andrei Lepikhov
From 98306f0e14c12b6dee92ef5977d85fc1dd324898 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 v0] 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 is 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, I see that
the cost model prefers a series of small sortings instead of a single massive
one.
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 | 35 ++---
src/backend/optimizer/path/pathkeys.c | 112 ++++++++++++++
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, 255 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..f48f5b94c0a 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1855,29 +1855,13 @@ 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_total->param_info == NULL);
/*
* When building a fractional path, determine a cheapest
@@ -1894,10 +1878,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..4a5e37b493c 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,72 @@ 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;
+ 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 rel->cheapest_total_path;
+
+ switch (cost_criterion)
+ {
+ case STARTUP_COST:
+ base_path = rel->cheapest_startup_path;
+ break;
+ case TOTAL_COST:
+ base_path = rel->cheapest_total_path;
+ break;
+ default:
+ /* In case of new criteries */
+ elog(ERROR, "unrecognized cost criterion: %d", cost_criterion);
+ break;
+ }
+
+ /* 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 +758,50 @@ 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 = get_cheapest_fractional_path(rel, root->tuple_fraction);
+
+ /* 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
