Here is a rebased version of the patch set.
Since the last version, production use has exposed that the 'optimistic'
strategy doesn't work safely: we can't suppose during planning that the outer of
the LEFT JOIN returns no more than 'limit_tuples' rows. Some pushed-down clause
that lands as a join filter in the LEFT JOIN chain might cause the scan node to
return (and sort) far more tuples than planned.
This patch set employs an opportunistic approach: the plan estimation part
assumes full sort of the rows returned by the Scan, and the executor's
ExecSetTupleBound applies boundaries on the Sort node if no one LEFT JOIN node
in the chain contains filters.
The most beneficial strategy should employ something like a Top-Down 'smoothing'
pass after the planning stage, as mentioned [1] in earlier threads. An
alternative approach is a kind of subtree replanning, as the commit [2] has
demonstrated recently.
[1]
https://www.postgresql.org/message-id/494586a2-fd9b-44ad-9bb5-4b6cc18bdf53%40gmail.com
[2]
https://www.postgresql.org/message-id/[email protected]
--
regards, Andrei Lepikhov,
pgEdge
From dd51073a86aafb8d94d0b3b1c232fe8a6bca05fd Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 15:35:16 +0200
Subject: [PATCH v1 1/3] Extend the ExecSetTupleBound to LEFT JOIN outer side
---
src/backend/executor/execProcnode.c | 16 ++++++++++++++++
1 file changed, 16 insertions(+)
diff --git a/src/backend/executor/execProcnode.c
b/src/backend/executor/execProcnode.c
index 7c4c66e323f..06d2be90478 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -958,6 +958,22 @@ ExecSetTupleBound(int64 tuples_needed, PlanState
*child_node)
ExecSetTupleBound(tuples_needed, outerPlanState(child_node));
}
+ else if (IsA(child_node, NestLoopState))
+ {
+ /*
+ * A nestloop left join returns at least one row for every
outer row:
+ * if no inner row passes the joinqual, the outer row is emitted
+ * null-extended instead of being dropped. So the bound
carries over
+ * unchanged to the outer input.
+ * This logic works unless otherqual can discard an outer tuple
during
+ * the join. Hence, check it before propagating boundaries
downstairs.
+ */
+ NestLoopState *nlstate = (NestLoopState *) child_node;
+ JoinType jointype = nlstate->js.jointype;
+
+ if (jointype == JOIN_LEFT && nlstate->js.ps.qual == NULL)
+ ExecSetTupleBound(tuples_needed,
outerPlanState(child_node));
+ }
/*
* In principle we could descend through any plan node type that is
--
2.55.0
From aa74162a10bb31514a2f9e94a327fb81c51d7595 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 18:38:49 +0200
Subject: [PATCH v1 2/3] Try pre-sorted outer path for a JOIN.
Check whether the outer rel can be sorted on a useful prefix of
query_pathkeys, and if no such path already exists, build one with
create_sort_path() and submit it to try_nestloop_path().
For LEFT JOIN, every outer row produces at least one output row, so
root->limit_tuples translates directly
to a bound on the outer sort and is passed to cost_sort() to activate
the bounded heap-sort cost model. For INNER, SEMI, and ANTI joins,
outer rows may be discarded by the join condition, so -1.0 is used and a
conservative full-sort cost estimate is produced instead.
The get_cheapest_path_for_pathkeys() guard ensures no redundant work is
done for base relations whose sorted path already exist.
---
src/backend/optimizer/path/joinpath.c | 108 ++++++++++++++++++++++++++
1 file changed, 108 insertions(+)
diff --git a/src/backend/optimizer/path/joinpath.c
b/src/backend/optimizer/path/joinpath.c
index dfd08e7aeb1..611b690450c 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2007,6 +2007,114 @@ match_unsorted_outer(PlannerInfo *root,
false);
}
+ /*
+ * If the query has ORDER BY (possibly with LIMIT) and nestloop is
+ * applicable, consider pre-sorting the outer relation on the query
+ * pathkeys. The pre-sorted scan logic in set_plain_rel_pathlist()
+ * handles plain base relations; here we extend that to join rels as
+ * outer sides, covering ORDER BY keys that span multiple tables.
+ * For base relations the sorted path is already in outerrel->pathlist
+ * and was tried in the loop above; the get_cheapest_path_for_pathkeys
+ * guard below prevents redundant work in that case.
+ *
+ * When root->limit_tuples is set, create_sort_path() uses the bounded
+ * heap-sort cost model (N*log2(2K) instead of N*log2(N)), giving an
+ * accurate startup-cost estimate that feeds into fractional path
+ * comparison correctly.
+ */
+ if (nestjoinOK && root->query_pathkeys != NIL)
+ {
+ List *useful_pathkeys = NIL;
+ ListCell *lc;
+
+ foreach(lc, root->query_pathkeys)
+ {
+ PathKey *pathkey = (PathKey *)
lfirst(lc);
+ EquivalenceClass *ec = pathkey->pk_eclass;
+
+ if (!relation_can_be_sorted_early(root, outerrel, ec,
false))
+ break;
+
+ useful_pathkeys = lappend(useful_pathkeys, pathkey);
+ }
+
+ /*
+ * Only proceed if we found useful pathkeys and the outer rel
does not
+ * already have a path satisfying them — if it does, the
foreach loop
+ * above already considered it.
+ */
+ if (useful_pathkeys != NIL &&
+ get_cheapest_path_for_pathkeys(outerrel->pathlist,
+
useful_pathkeys,
+
outerrel->lateral_relids,
+
TOTAL_COST, false) == NULL)
+ {
+ Path *outerpath =
+
get_cheapest_path_for_pathkeys(outerrel->pathlist, NIL,
+
outerrel->lateral_relids,
+
TOTAL_COST, false);
+
+ if (outerpath != NULL && !PATH_PARAM_BY_REL(outerpath,
innerrel))
+ {
+ Path *sorted_outer;
+ List *merge_pathkeys;
+
+ /*
+ * For LEFT JOIN every outer row produces at
least one output
+ * row (NULL-extended if unmatched), so the
LIMIT bound on join
+ * output safely bounds the outer side — pass
it for an
+ * accurate top-N heap-sort cost estimate. For
INNER, SEMI,
+ * and ANTI, outer rows can be discarded by the
join condition,
+ * so we conservatively model a full sort.
+ */
+ sorted_outer = (Path *)
+ create_sort_path(root, outerrel,
outerpath,
+
useful_pathkeys, -1);
+
+ merge_pathkeys = build_join_pathkeys(root,
joinrel, jointype,
+
sorted_outer->pathkeys);
+
+ /*
+ * cheapest_parameterized_paths always includes
the
+ * cheapest-total unparameterized path, so no
need to
+ * try inner_cheapest_total separately.
+ */
+ foreach(lc,
innerrel->cheapest_parameterized_paths)
+ {
+ Path *innerpath = (Path *)
lfirst(lc);
+ Path *mpath;
+
+ try_nestloop_path(root, joinrel,
+
sorted_outer, innerpath,
+
merge_pathkeys,
+
jointype,
+
PGS_NESTLOOP_PLAIN,
+
extra);
+
+ mpath = get_memoize_path(root,
innerrel, outerrel,
+
innerpath, sorted_outer, jointype,
+
extra);
+ if (mpath != NULL)
+ try_nestloop_path(root, joinrel,
+
sorted_outer, mpath,
+
merge_pathkeys,
+
jointype,
+
PGS_NESTLOOP_MEMOIZE,
+
extra);
+ }
+
+ /* Also consider materialized form of the
cheapest inner path */
+ if (matpath != NULL)
+ try_nestloop_path(root, joinrel,
+
sorted_outer, matpath,
+
merge_pathkeys,
+
jointype,
+
PGS_NESTLOOP_MATERIALIZE,
+
extra);
+ }
+ }
+ }
+
/*
* Consider partial nestloop and mergejoin plan if outerrel has any
* partial path and the joinrel is parallel-safe. However, we can't
--
2.55.0
From 029587a45f2691f36eb8fcbc76bf37474c7832f3 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 19:26:34 +0200
Subject: [PATCH v1 3/3] Adjust query plans changed
---
.../postgres_fdw/expected/postgres_fdw.out | 108 +++++------
.../regress/expected/collate.icu.utf8.out | 22 ++-
src/test/regress/expected/create_am.out | 4 +-
src/test/regress/expected/eager_aggregate.out | 120 +++++--------
.../regress/expected/incremental_sort.out | 52 +++---
src/test/regress/expected/join.out | 42 ++---
src/test/regress/expected/oid8.out | 28 ++-
src/test/regress/expected/partition_join.out | 169 ++++++++++--------
src/test/regress/expected/pg_lsn.out | 28 ++-
src/test/regress/expected/tablesample.out | 8 +-
src/test/regress/sql/create_am.sql | 4 +-
11 files changed, 283 insertions(+), 302 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index a6295674daf..ae54589c4b3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2317,21 +2317,21 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON
(t1.c8 = t2.c8) ORDER BY t1.
-------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1
- -> Sort
+ -> Incremental Sort
Output: t1.c1, t2.c1
Sort Key: t1.c1, t2.c1
- -> Merge Left Join
+ Presorted Key: t1.c1
+ -> Nested Loop Left Join
Output: t1.c1, t2.c1
- Merge Cond: (t1.c8 = t2.c8)
+ Join Filter: (t1.c8 = t2.c8)
-> Sort
Output: t1.c1, t1.c8
- Sort Key: t1.c8
+ Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c8
Remote SQL: SELECT "C 1", c8 FROM "S 1"."T 1"
- -> Sort
+ -> Materialize
Output: t2.c1, t2.c8
- Sort Key: t2.c8
-> Foreign Scan on public.ft2 t2
Output: t2.c1, t2.c8
Remote SQL: SELECT "C 1", c8 FROM "S 1"."T 1"
@@ -4536,24 +4536,24 @@ select sum(c2) * (random() <= 1)::int as sum from ft1
order by 1;
set enable_hashagg to false;
explain (verbose, costs off)
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum
from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and
t1."C 1" < 100 order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Nested Loop
Output: t1.c2, qry.sum
- Sort Key: t1.c2
- -> Nested Loop
- Output: t1.c2, qry.sum
+ -> Sort
+ Output: t1.c2, t1."C 1"
+ Sort Key: t1.c2
-> Index Scan using t1_pkey on "S 1"."T 1" t1
- Output: t1."C 1", t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7,
t1.c8
+ Output: t1.c2, t1."C 1"
Index Cond: (t1."C 1" < 100)
Filter: (t1.c2 < 3)
- -> Subquery Scan on qry
- Output: qry.sum, t2.c1
- Filter: ((t1.c2 * 2) = qry.sum)
- -> Foreign Scan
- Output: (sum((t2.c1 + t1."C 1"))), t2.c1
- Relations: Aggregate on (public.ft2 t2)
- Remote SQL: SELECT sum(("C 1" + $1::integer)), "C 1" FROM
"S 1"."T 1" GROUP BY 2
+ -> Subquery Scan on qry
+ Output: qry.sum, t2.c1
+ Filter: ((t1.c2 * 2) = qry.sum)
+ -> Foreign Scan
+ Output: (sum((t2.c1 + t1."C 1"))), t2.c1
+ Relations: Aggregate on (public.ft2 t2)
+ Remote SQL: SELECT sum(("C 1" + $1::integer)), "C 1" FROM "S
1"."T 1" GROUP BY 2
(16 rows)
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum
from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and
t1."C 1" < 100 order by 1;
@@ -12465,27 +12465,27 @@ DELETE FROM result_tbl;
-- (case where subplans are re-scanned with parameter changes)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT o.x FROM (VALUES (2505), (3505)) o(x), LATERAL (SELECT a FROM async_pt
WHERE a = o.x OR a = 1505 LIMIT 1) s ORDER BY o.x;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Nested Loop
Output: "*VALUES*".column1
- Sort Key: "*VALUES*".column1
- -> Nested Loop
+ -> Sort
Output: "*VALUES*".column1
+ Sort Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1
- -> Limit
- Output: NULL::integer
- -> Append
- -> Async Foreign Scan on public.async_p1 async_pt_1
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE
(((a = $1::integer) OR (a = 1505)))
- -> Async Foreign Scan on public.async_p2 async_pt_2
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE
(((a = $1::integer) OR (a = 1505)))
- -> Async Foreign Scan on public.async_p3 async_pt_3
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl3 WHERE
(((a = $1::integer) OR (a = 1505)))
+ -> Limit
+ Output: NULL::integer
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_1
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE (((a
= $1::integer) OR (a = 1505)))
+ -> Async Foreign Scan on public.async_p2 async_pt_2
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE (((a
= $1::integer) OR (a = 1505)))
+ -> Async Foreign Scan on public.async_p3 async_pt_3
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl3 WHERE (((a
= $1::integer) OR (a = 1505)))
(19 rows)
SELECT o.x FROM (VALUES (2505), (3505)) o(x), LATERAL (SELECT a FROM async_pt
WHERE a = o.x OR a = 1505 LIMIT 1) s ORDER BY o.x;
@@ -12497,27 +12497,27 @@ SELECT o.x FROM (VALUES (2505), (3505)) o(x), LATERAL
(SELECT a FROM async_pt WH
EXPLAIN (VERBOSE, COSTS OFF)
SELECT o.x FROM (VALUES (2505), (3505)) o(x), LATERAL (SELECT a FROM async_pt
WHERE a = o.x OR (a = 1505 AND o.x = 2505) LIMIT 1) s ORDER BY o.x;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
Output: "*VALUES*".column1
- Sort Key: "*VALUES*".column1
- -> Nested Loop
+ -> Sort
Output: "*VALUES*".column1
+ Sort Key: "*VALUES*".column1
-> Values Scan on "*VALUES*"
Output: "*VALUES*".column1
- -> Limit
- Output: NULL::integer
- -> Append
- -> Async Foreign Scan on public.async_p1 async_pt_1
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE
(((a = $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
- -> Async Foreign Scan on public.async_p2 async_pt_2
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE
(((a = $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
- -> Async Foreign Scan on public.async_p3 async_pt_3
- Output: NULL::integer
- Remote SQL: SELECT NULL FROM public.base_tbl3 WHERE
(((a = $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
+ -> Limit
+ Output: NULL::integer
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_1
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE (((a
= $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
+ -> Async Foreign Scan on public.async_p2 async_pt_2
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE (((a
= $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
+ -> Async Foreign Scan on public.async_p3 async_pt_3
+ Output: NULL::integer
+ Remote SQL: SELECT NULL FROM public.base_tbl3 WHERE (((a
= $1::integer) OR ((a = 1505) AND ($1::integer = 2505))))
(19 rows)
SELECT o.x FROM (VALUES (2505), (3505)) o(x), LATERAL (SELECT a FROM async_pt
WHERE a = o.x OR (a = 1505 AND o.x = 2505) LIMIT 1) s ORDER BY o.x;
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index cb5795f036e..190c32d580c 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -3612,26 +3612,30 @@ INSERT INTO pagg_tab6 (b, c) SELECT substr('cdCD', (i %
4) + 1 , 1), substr('cdC
ANALYZE pagg_tab6;
EXPLAIN (COSTS OFF)
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: t1.c COLLATE "C"
-> Append
- -> HashAggregate
+ -> GroupAggregate
Group Key: t1.c
-> Nested Loop
Join Filter: (t1.c = t2.c)
- -> Seq Scan on pagg_tab6_p1 t2
- Filter: (c = b)
+ -> Sort
+ Sort Key: t2.c COLLATE case_insensitive
+ -> Seq Scan on pagg_tab6_p1 t2
+ Filter: (c = b)
-> Seq Scan on pagg_tab5_p1 t1
- -> HashAggregate
+ -> GroupAggregate
Group Key: t1_1.c
-> Nested Loop
Join Filter: (t1_1.c = t2_1.c)
- -> Seq Scan on pagg_tab6_p2 t2_1
- Filter: (c = b)
+ -> Sort
+ Sort Key: t2_1.c COLLATE case_insensitive
+ -> Seq Scan on pagg_tab6_p2 t2_1
+ Filter: (c = b)
-> Seq Scan on pagg_tab5_p2 t1_1
-(17 rows)
+(21 rows)
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
c | count
diff --git a/src/test/regress/expected/create_am.out
b/src/test/regress/expected/create_am.out
index c1a95157251..05efb03272c 100644
--- a/src/test/regress/expected/create_am.out
+++ b/src/test/regress/expected/create_am.out
@@ -201,9 +201,9 @@ SELECT
pc.relkind,
pa.amname,
CASE WHEN relkind = 't' THEN
- (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE
pcm.reltoastrelid = pc.oid)
+ (SELECT 'toast for ' || pcm.oid::regclass FROM pg_class pcm WHERE
pcm.reltoastrelid = pc.oid)
ELSE
- relname::regclass::text
+ pc.oid::regclass::text
END COLLATE "C" AS relname
FROM pg_class AS pc,
pg_am AS pa
diff --git a/src/test/regress/expected/eager_aggregate.out
b/src/test/regress/expected/eager_aggregate.out
index 091ae48a92b..bd5ab531dcc 100644
--- a/src/test/regress/expected/eager_aggregate.out
+++ b/src/test/regress/expected/eager_aggregate.out
@@ -936,92 +936,52 @@ GROUP BY t3.y ORDER BY t3.y;
Finalize GroupAggregate
Output: t3.y, sum((t2.y + t3.y))
Group Key: t3.y
- -> Sort
+ -> Nested Loop
Output: t3.y, (PARTIAL sum((t2.y + t3.y)))
- Sort Key: t3.y
- -> Append
- -> Hash Join
- Output: t3.y, (PARTIAL sum((t2.y + t3.y)))
- Hash Cond: (t2.x = t1.x)
- -> Partial GroupAggregate
- Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y))
- Group Key: t2.x, t3.y, t3.x
- -> Incremental Sort
+ Join Filter: (t1.x = t2.x)
+ -> Sort
+ Output: t2.x, t3.y, t3.x, (PARTIAL sum((t2.y + t3.y)))
+ Sort Key: t3.y
+ -> Partial GroupAggregate
+ Output: t2.x, t3.y, t3.x, PARTIAL sum((t2.y + t3.y))
+ Group Key: t2.x, t3.y, t3.x
+ -> Incremental Sort
+ Output: t2.y, t2.x, t3.y, t3.x
+ Sort Key: t2.x, t3.y
+ Presorted Key: t2.x
+ -> Merge Join
Output: t2.y, t2.x, t3.y, t3.x
- Sort Key: t2.x, t3.y
- Presorted Key: t2.x
- -> Merge Join
- Output: t2.y, t2.x, t3.y, t3.x
- Merge Cond: (t2.x = t3.x)
- -> Sort
- Output: t2.y, t2.x
- Sort Key: t2.x
- -> Seq Scan on
public.eager_agg_tab1_p1 t2
- Output: t2.y, t2.x
- -> Sort
- Output: t3.y, t3.x
- Sort Key: t3.x
- -> Seq Scan on
public.eager_agg_tab1_p1 t3
- Output: t3.y, t3.x
- -> Hash
- Output: t1.x
- -> Seq Scan on public.eager_agg_tab1_p1 t1
- Output: t1.x
- -> Hash Join
- Output: t3_1.y, (PARTIAL sum((t2_1.y + t3_1.y)))
- Hash Cond: (t2_1.x = t1_1.x)
- -> Partial GroupAggregate
- Output: t2_1.x, t3_1.y, t3_1.x, PARTIAL sum((t2_1.y
+ t3_1.y))
- Group Key: t2_1.x, t3_1.y, t3_1.x
- -> Incremental Sort
- Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x
- Sort Key: t2_1.x, t3_1.y
- Presorted Key: t2_1.x
- -> Merge Join
- Output: t2_1.y, t2_1.x, t3_1.y, t3_1.x
- Merge Cond: (t2_1.x = t3_1.x)
- -> Sort
- Output: t2_1.y, t2_1.x
- Sort Key: t2_1.x
- -> Seq Scan on
public.eager_agg_tab1_p2 t2_1
+ Merge Cond: (t2.x = t3.x)
+ -> Sort
+ Output: t2.y, t2.x
+ Sort Key: t2.x
+ -> Append
+ -> Seq Scan on
public.eager_agg_tab1_p1 t2_1
Output: t2_1.y, t2_1.x
- -> Sort
- Output: t3_1.y, t3_1.x
- Sort Key: t3_1.x
- -> Seq Scan on
public.eager_agg_tab1_p2 t3_1
- Output: t3_1.y, t3_1.x
- -> Hash
- Output: t1_1.x
- -> Seq Scan on public.eager_agg_tab1_p2 t1_1
- Output: t1_1.x
- -> Hash Join
- Output: t3_2.y, (PARTIAL sum((t2_2.y + t3_2.y)))
- Hash Cond: (t2_2.x = t1_2.x)
- -> Partial GroupAggregate
- Output: t2_2.x, t3_2.y, t3_2.x, PARTIAL sum((t2_2.y
+ t3_2.y))
- Group Key: t2_2.x, t3_2.y, t3_2.x
- -> Incremental Sort
- Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x
- Sort Key: t2_2.x, t3_2.y
- Presorted Key: t2_2.x
- -> Merge Join
- Output: t2_2.y, t2_2.x, t3_2.y, t3_2.x
- Merge Cond: (t2_2.x = t3_2.x)
- -> Sort
- Output: t2_2.y, t2_2.x
- Sort Key: t2_2.x
- -> Seq Scan on
public.eager_agg_tab1_p3 t2_2
+ -> Seq Scan on
public.eager_agg_tab1_p2 t2_2
Output: t2_2.y, t2_2.x
- -> Sort
- Output: t3_2.y, t3_2.x
- Sort Key: t3_2.x
- -> Seq Scan on
public.eager_agg_tab1_p3 t3_2
+ -> Seq Scan on
public.eager_agg_tab1_p3 t2_3
+ Output: t2_3.y, t2_3.x
+ -> Sort
+ Output: t3.y, t3.x
+ Sort Key: t3.x
+ -> Append
+ -> Seq Scan on
public.eager_agg_tab1_p1 t3_1
+ Output: t3_1.y, t3_1.x
+ -> Seq Scan on
public.eager_agg_tab1_p2 t3_2
Output: t3_2.y, t3_2.x
- -> Hash
+ -> Seq Scan on
public.eager_agg_tab1_p3 t3_3
+ Output: t3_3.y, t3_3.x
+ -> Materialize
+ Output: t1.x
+ -> Append
+ -> Seq Scan on public.eager_agg_tab1_p1 t1_1
+ Output: t1_1.x
+ -> Seq Scan on public.eager_agg_tab1_p2 t1_2
Output: t1_2.x
- -> Seq Scan on public.eager_agg_tab1_p3 t1_2
- Output: t1_2.x
-(88 rows)
+ -> Seq Scan on public.eager_agg_tab1_p3 t1_3
+ Output: t1_3.x
+(48 rows)
SELECT t3.y, sum(t2.y + t3.y)
FROM eager_agg_tab1 t1
diff --git a/src/test/regress/expected/incremental_sort.out
b/src/test/regress/expected/incremental_sort.out
index 1e6e020fea8..0eaa8d75ba2 100644
--- a/src/test/regress/expected/incremental_sort.out
+++ b/src/test/regress/expected/incremental_sort.out
@@ -1610,16 +1610,16 @@ from tenk1 t, generate_series(1, 1000);
QUERY PLAN
---------------------------------------------------------------------------------
Unique
- -> Sort
- Sort Key: t.unique1, ((SubPlan expr_1))
- -> Gather
- Workers Planned: 2
- -> Nested Loop
+ -> Nested Loop
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan expr_1))
+ -> Gather
+ Workers Planned: 2
-> Parallel Index Only Scan using tenk1_unique1 on tenk1
t
- -> Function Scan on generate_series
- SubPlan expr_1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan expr_1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
+ -> Function Scan on generate_series
(11 rows)
explain (costs off) select
@@ -1629,16 +1629,16 @@ from tenk1 t, generate_series(1, 1000)
order by 1, 2;
QUERY PLAN
---------------------------------------------------------------------------
- Sort
- Sort Key: t.unique1, ((SubPlan expr_1))
- -> Gather
- Workers Planned: 2
- -> Nested Loop
+ Nested Loop
+ -> Sort
+ Sort Key: t.unique1, ((SubPlan expr_1))
+ -> Gather
+ Workers Planned: 2
-> Parallel Index Only Scan using tenk1_unique1 on tenk1 t
- -> Function Scan on generate_series
- SubPlan expr_1
- -> Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = t.unique1)
+ SubPlan expr_1
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 = t.unique1)
+ -> Function Scan on generate_series
(10 rows)
-- Parallel sort but with expression not available until the upper rel.
@@ -1708,21 +1708,19 @@ explain (costs off)
select * from
(select * from tenk1 order by four) t1 join tenk1 t2 on t1.four = t2.four
and t1.two = t2.two
order by t1.four, t1.two limit 1;
- QUERY PLAN
------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Limit
- -> Merge Join
- Merge Cond: ((tenk1.four = t2.four) AND (tenk1.two = t2.two))
- -> Incremental Sort
+ -> Nested Loop
+ Join Filter: ((tenk1.four = t2.four) AND (tenk1.two = t2.two))
+ -> Sort
Sort Key: tenk1.four, tenk1.two
- Presorted Key: tenk1.four
-> Sort
Sort Key: tenk1.four
-> Seq Scan on tenk1
- -> Sort
- Sort Key: t2.four, t2.two
+ -> Materialize
-> Seq Scan on tenk1 t2
-(12 rows)
+(10 rows)
--
-- Test incremental sort for Append/MergeAppend
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 051c3539930..52b90a8d9e9 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -10643,29 +10643,29 @@ 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
+ QUERY PLAN
+--------------------------------------------------------------
+ Nested Loop Left Join
Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
- Sort Key: t1.a
- -> Nested Loop Left Join
- Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ -> Sort
+ Output: t1.b, t1.a
+ Sort Key: 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)
+ Output: t1.b, t1.a
+ -> 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)
select t1.b, ss.phv from join_ut1 t1 left join lateral
diff --git a/src/test/regress/expected/oid8.out
b/src/test/regress/expected/oid8.out
index 2e114f1ce70..7bfa8a9d826 100644
--- a/src/test/regress/expected/oid8.out
+++ b/src/test/regress/expected/oid8.out
@@ -217,21 +217,19 @@ SELECT DISTINCT (i || '000000000000' || j)::oid8 f
generate_series(1, 5) k
WHERE i <= 10 AND j > 0 AND j <= 10
ORDER BY f;
- QUERY PLAN
------------------------------------------------------------------------------------
- Sort
- Sort Key: (((((i.i)::text || '000000000000'::text) || (j.j)::text))::oid8)
- -> HashAggregate
- Group Key: ((((i.i)::text || '000000000000'::text) ||
(j.j)::text))::oid8
- -> Nested Loop
- -> Function Scan on generate_series k
- -> Materialize
- -> Nested Loop
- -> Function Scan on generate_series j
- Filter: ((j > 0) AND (j <= 10))
- -> Function Scan on generate_series i
- Filter: (i <= 10)
-(12 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Unique
+ -> Nested Loop
+ -> Sort
+ Sort Key: (((((i.i)::text || '000000000000'::text) ||
(j.j)::text))::oid8)
+ -> Nested Loop
+ -> Function Scan on generate_series j
+ Filter: ((j > 0) AND (j <= 10))
+ -> Function Scan on generate_series i
+ Filter: (i <= 10)
+ -> Function Scan on generate_series k
+(10 rows)
SELECT DISTINCT (i || '000000000000' || j)::oid8 f
FROM generate_series(1, 10) i,
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index a7e26afa793..52903a736f4 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -781,37 +781,41 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
(SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a =
t4.a
WHERE t4.b = 0) t2 ON t1.a = t2.a
WHERE t1.c = t2.c AND t2.a IS NOT NULL ORDER BY t1.a, t1.c;
- QUERY PLAN
----------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Merge Append
Sort Key: t1.a, t1.c
- -> Append
- -> Nested Loop
+ -> Nested Loop
+ -> Sort
+ Sort Key: t3_1.a, (COALESCE(t3_1.c, t4_1.c))
-> Nested Loop
-> Seq Scan on prt1_p1 t4_1
Filter: (b = 0)
-> Index Scan using prt3_p1_a_idx on prt3_p1 t3_1
Index Cond: ((a = t4_1.a) AND (a IS NOT NULL))
- -> Index Only Scan using prt4_p1_c_a_idx on prt4_p1 t1_1
- Index Cond: ((c = ((COALESCE(t3_1.c, t4_1.c)))::text) AND
(a = t3_1.a))
- -> Nested Loop
+ -> Index Only Scan using prt4_p1_c_a_idx on prt4_p1 t1_1
+ Index Cond: ((c = ((COALESCE(t3_1.c, t4_1.c)))::text) AND (a =
t3_1.a))
+ -> Nested Loop
+ -> Sort
+ Sort Key: t3_2.a, (COALESCE(t3_2.c, t4_2.c))
-> Nested Loop
-> Seq Scan on prt1_p2 t4_2
Filter: (b = 0)
-> Index Scan using prt3_p2_a_idx on prt3_p2 t3_2
Index Cond: ((a = t4_2.a) AND (a IS NOT NULL))
- -> Index Only Scan using prt4_p2_c_a_idx on prt4_p2 t1_2
- Index Cond: ((c = ((COALESCE(t3_2.c, t4_2.c)))::text) AND
(a = t3_2.a))
- -> Nested Loop
- -> Seq Scan on prt1_p3 t4_3
- Filter: (b = 0)
+ -> Index Only Scan using prt4_p2_c_a_idx on prt4_p2 t1_2
+ Index Cond: ((c = ((COALESCE(t3_2.c, t4_2.c)))::text) AND (a =
t3_2.a))
+ -> Nested Loop
+ -> Sort
+ Sort Key: t3_3.a, (COALESCE(t3_3.c, t4_3.c))
-> Nested Loop
- Join Filter: (((COALESCE(t3_3.c, t4_3.c)))::text =
(t1_3.c)::text)
+ -> Seq Scan on prt1_p3 t4_3
+ Filter: (b = 0)
-> Index Scan using prt3_p3_a_idx on prt3_p3 t3_3
Index Cond: ((a = t4_3.a) AND (a IS NOT NULL))
- -> Index Only Scan using prt4_p3_c_a_idx on prt4_p3 t1_3
- Index Cond: ((c = ((COALESCE(t3_3.c,
t4_3.c)))::text) AND (a = t3_3.a))
-(28 rows)
+ -> Index Only Scan using prt4_p3_c_a_idx on prt4_p3 t1_3
+ Index Cond: ((c = ((COALESCE(t3_3.c, t4_3.c)))::text) AND (a =
t3_3.a))
+(32 rows)
SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
(SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a =
t4.a
@@ -2214,53 +2218,60 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b
AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND
t2.c = t3.c)) ss
ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0
ORDER BY t1.a;
- QUERY PLAN
------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Merge Append
Sort Key: t1.a
- -> Append
- -> Nested Loop Left Join
+ -> Nested Loop Left Join
+ -> Sort
+ Sort Key: t1_1.a
-> Seq Scan on prt1_l_p1 t1_1
Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text =
(t2_1.c)::text))
- -> Seq Scan on prt2_l_p1 t3_1
- -> Hash
- -> Seq Scan on prt1_l_p1 t2_1
- Filter: ((t1_1.a = a) AND ((t1_1.c)::text =
(c)::text))
- -> Nested Loop Left Join
+ -> Hash Join
+ Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text =
(t2_1.c)::text))
+ -> Seq Scan on prt2_l_p1 t3_1
+ -> Hash
+ -> Seq Scan on prt1_l_p1 t2_1
+ Filter: ((t1_1.a = a) AND ((t1_1.c)::text =
(c)::text))
+ -> Nested Loop Left Join
+ -> Sort
+ Sort Key: t1_2.a
-> Seq Scan on prt1_l_p2_p1 t1_2
Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text =
(t2_2.c)::text))
- -> Seq Scan on prt2_l_p2_p1 t3_2
- -> Hash
- -> Seq Scan on prt1_l_p2_p1 t2_2
- Filter: ((t1_2.a = a) AND ((t1_2.c)::text =
(c)::text))
- -> Nested Loop Left Join
+ -> Hash Join
+ Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text =
(t2_2.c)::text))
+ -> Seq Scan on prt2_l_p2_p1 t3_2
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p1 t2_2
+ Filter: ((t1_2.a = a) AND ((t1_2.c)::text =
(c)::text))
+ -> Nested Loop Left Join
+ -> Sort
+ Sort Key: t1_3.a
-> Seq Scan on prt1_l_p2_p2 t1_3
Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text =
(t2_3.c)::text))
- -> Seq Scan on prt2_l_p2_p2 t3_3
- -> Hash
- -> Seq Scan on prt1_l_p2_p2 t2_3
- Filter: ((t1_3.a = a) AND ((t1_3.c)::text =
(c)::text))
- -> Nested Loop Left Join
+ -> Hash Join
+ Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text =
(t2_3.c)::text))
+ -> Seq Scan on prt2_l_p2_p2 t3_3
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p2 t2_3
+ Filter: ((t1_3.a = a) AND ((t1_3.c)::text =
(c)::text))
+ -> Nested Loop Left Join
+ -> Sort
+ Sort Key: t1_4.a
-> Seq Scan on prt1_l_p3_p1 t1_4
Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_5.b = t2_5.a) AND ((t3_5.c)::text =
(t2_5.c)::text))
+ -> Hash Join
+ Hash Cond: ((t3_5.b = t2_5.a) AND ((t3_5.c)::text =
(t2_5.c)::text))
+ -> Append
+ -> Seq Scan on prt2_l_p3_p1 t3_5
+ -> Seq Scan on prt2_l_p3_p2 t3_6
+ -> Hash
-> Append
- -> Seq Scan on prt2_l_p3_p1 t3_5
- -> Seq Scan on prt2_l_p3_p2 t3_6
- -> Hash
- -> Append
- -> Seq Scan on prt1_l_p3_p1 t2_5
- Filter: ((t1_4.a = a) AND
((t1_4.c)::text = (c)::text))
- -> Seq Scan on prt1_l_p3_p2 t2_6
- Filter: ((t1_4.a = a) AND
((t1_4.c)::text = (c)::text))
-(44 rows)
+ -> Seq Scan on prt1_l_p3_p1 t2_5
+ Filter: ((t1_4.a = a) AND ((t1_4.c)::text =
(c)::text))
+ -> Seq Scan on prt1_l_p3_p2 t2_6
+ Filter: ((t1_4.a = a) AND ((t1_4.c)::text =
(c)::text))
+(51 rows)
SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b
AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND
t2.c = t3.c)) ss
@@ -5255,36 +5266,46 @@ EXPLAIN (COSTS OFF)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b =
t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND
t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210))
AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
- Sort
+ Merge Append
Sort Key: t1.a, t1.b
- -> Append
- -> Hash Join
- Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c
= t2_1.c))
+ -> Merge Join
+ Merge Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c =
t2_1.c))
+ -> Sort
+ Sort Key: t1_1.a, t1_1.b, t1_1.c
-> Seq Scan on alpha_neg_p1 t1_1
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >=
100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
- -> Hash
- -> Seq Scan on beta_neg_p1 t2_1
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND (b < 210)))
- -> Hash Join
- Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c
= t2_2.c))
+ -> Sort
+ Sort Key: t2_1.a, t2_1.b, t2_1.c
+ -> Seq Scan on beta_neg_p1 t2_1
+ Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
< 210)))
+ -> Merge Join
+ Merge Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c =
t2_2.c))
+ -> Sort
+ Sort Key: t1_2.a, t1_2.b, t1_2.c
-> Seq Scan on alpha_neg_p2 t1_2
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >=
100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
- -> Hash
- -> Seq Scan on beta_neg_p2 t2_2
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND (b < 210)))
- -> Nested Loop
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND
(t1_3.c = t2_3.c))
- -> Seq Scan on alpha_pos_p2 t1_3
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >=
100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
+ -> Sort
+ Sort Key: t2_2.a, t2_2.b, t2_2.c
+ -> Seq Scan on beta_neg_p2 t2_2
+ Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
< 210)))
+ -> Nested Loop
+ Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c =
t2_3.c))
+ -> Sort
+ Sort Key: t2_3.a, t2_3.b
-> Seq Scan on beta_pos_p2 t2_3
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
< 210)))
- -> Nested Loop
- Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.b = t2_4.b) AND
(t1_4.c = t2_4.c))
- -> Seq Scan on alpha_pos_p3 t1_4
+ -> Materialize
+ -> Seq Scan on alpha_pos_p2 t1_3
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >=
100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
+ -> Nested Loop
+ Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.b = t2_4.b) AND (t1_4.c =
t2_4.c))
+ -> Sort
+ Sort Key: t2_4.a, t2_4.b
-> Seq Scan on beta_pos_p3 t2_4
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
< 210)))
-(29 rows)
+ -> Seq Scan on alpha_pos_p3 t1_4
+ Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND
(b < 110)) OR ((b >= 200) AND (b < 210))))
+(39 rows)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b =
t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND
t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210))
AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
a | b | c | a | b | c
diff --git a/src/test/regress/expected/pg_lsn.out
b/src/test/regress/expected/pg_lsn.out
index 8ab59b2e445..ee014f7514e 100644
--- a/src/test/regress/expected/pg_lsn.out
+++ b/src/test/regress/expected/pg_lsn.out
@@ -142,21 +142,19 @@ SELECT DISTINCT (i || '/' || j)::pg_lsn f
generate_series(1, 5) k
WHERE i <= 10 AND j > 0 AND j <= 10
ORDER BY f;
- QUERY PLAN
---------------------------------------------------------------------------
- Sort
- Sort Key: (((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn)
- -> HashAggregate
- Group Key: ((((i.i)::text || '/'::text) || (j.j)::text))::pg_lsn
- -> Nested Loop
- -> Function Scan on generate_series k
- -> Materialize
- -> Nested Loop
- -> Function Scan on generate_series j
- Filter: ((j > 0) AND (j <= 10))
- -> Function Scan on generate_series i
- Filter: (i <= 10)
-(12 rows)
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Unique
+ -> Nested Loop
+ -> Sort
+ Sort Key: (((((i.i)::text || '/'::text) ||
(j.j)::text))::pg_lsn)
+ -> Nested Loop
+ -> Function Scan on generate_series j
+ Filter: ((j > 0) AND (j <= 10))
+ -> Function Scan on generate_series i
+ Filter: (i <= 10)
+ -> Function Scan on generate_series k
+(10 rows)
SELECT DISTINCT (i || '/' || j)::pg_lsn f
FROM generate_series(1, 10) i,
diff --git a/src/test/regress/expected/tablesample.out
b/src/test/regress/expected/tablesample.out
index 9ff4611640c..fe2dd6eef41 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -253,13 +253,15 @@ select pct, count(unique1) from
group by pct;
QUERY PLAN
--------------------------------------------------------
- HashAggregate
+ GroupAggregate
Group Key: "*VALUES*".column1
-> Nested Loop
- -> Values Scan on "*VALUES*"
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
-> Sample Scan on tenk1
Sampling: bernoulli ("*VALUES*".column1)
-(6 rows)
+(8 rows)
select pct, count(unique1) from
(values (0),(100)) v(pct),
diff --git a/src/test/regress/sql/create_am.sql
b/src/test/regress/sql/create_am.sql
index 754fe0c694b..0062189eb61 100644
--- a/src/test/regress/sql/create_am.sql
+++ b/src/test/regress/sql/create_am.sql
@@ -146,9 +146,9 @@ SELECT
pc.relkind,
pa.amname,
CASE WHEN relkind = 't' THEN
- (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE
pcm.reltoastrelid = pc.oid)
+ (SELECT 'toast for ' || pcm.oid::regclass FROM pg_class pcm WHERE
pcm.reltoastrelid = pc.oid)
ELSE
- relname::regclass::text
+ pc.oid::regclass::text
END COLLATE "C" AS relname
FROM pg_class AS pc,
pg_am AS pa
--
2.55.0
