Hi,
The attached patch set adds an optimisation that intensifies the usage
of the sorted NestLoop trick to avoid sorts higher in the query tree.
This employs the idea that it is not so rare a case when an ORDER BY
query (or GROUP BY or DISTINCT operator) can benefit from a pre-sorted
path even if the outer subtree doesn't have an index on the sort
expression. It is especially beneficial when a LIMIT clause and an OUTER
JOIN are present - combined with tuple-bound propagation through the
outer side of such a join, LIMIT can reach the Sort below the join and
turn it into a bounded top-N heapsort.
Let me explain the problem with a typical schema of a sales database. It
has a 'products' table and description tables for each category (see the
SQL script in the attachment).
A front page provides the top-N products based on popularity or other
criteria. The query looks like multiple OUTER JOINs of the main
'products' table with tables for each category. At the top, non-null
fields are combined into a JSON descriptor -- something like the following:
SELECT p.id, p.name, p.category, p.popularity,
json_strip_nulls(json_build_object(
'warranty', e.warranty_months,
'voltage', e.voltage,
'size', c.size,
'color', c.color,
'expiry', f.expiry_days,
'organic', f.organic
)) AS properties
FROM products p
LEFT JOIN electronics_props e ON e.product_id = p.id
LEFT JOIN clothing_props c ON c.product_id = p.id
LEFT JOIN food_props f ON f.product_id = p.id
ORDER BY p.popularity DESC
LIMIT 10;
AFAICS, this pattern is common in sales systems, CRM and ERP workloads -
anywhere you query "top-N items with details" over a normalised schema
with LEFT JOINs to optional property tables.
There is usually no need to join all the tables and sort afterwards;
pre-sorting the outer relation might be more beneficial. This subject
has already been discussed [1,2], but here we try to solve the case in
general. There was an argument that the user might just add an index.
But sometimes it 1) might not be reasonable (like in the example) and 2)
is impossible if the data source is not a plain table but a
FunctionScan, SubqueryScan, etc. The most important source I consider
here is an Append over foreign tables.
There is a sketchy patch set in the attachment that outlines the
solution. It consists of the following parts:
1. Executor part, the ExecSetTupleBound extension: allows recursion into
the outer side of a join to bound the underlying subtree. It is crucial
because we lack a proper hook in this subsystem, which makes the
extensible implementation of such a feature very complex.
2. Planner part, modification of the joinpath.c::match_unsorted_outer.
In the case of NestLoop, where its outer side is mentioned in the
query_pathkeys prefix, and no sort on this prefix yet exists, Postgres
tries to build such a sorted path and proposes a JOIN with sorted output.
3. Adjustment of regression tests.
Open issues
1. Scope: the optimisation targets a specific pattern (ORDER BY + LIMIT
over joins, and NestLoop). One may argue that it is too narrow to
justify core code. That's true, but I see that such a 'Sort pushdown'
might enable the optimiser to create more effective MergeJoins,
aggregates, etc. You may check the regression tests diff for insights.
2. Cost estimation: the Sort node is costed with an estimated tuple
bound from LIMIT in case of OUTER JOIN, which can, in some cases, make
the pre-sorted path look cheaper than it actually is. I don't see an
actual problem here, since it's about in-memory sorting, which shouldn't
be too expensive anyway.
Feedback and review welcome.
References
[1] "Possible optimisation: push down SORT and LIMIT nodes", 2018
https://www.postgresql.org/message-id/E9FA92C2921F31408041863B74EE4C2001AEF33492@CCPMAILDAG03.cantab.local
[2] "Wasteful nested loop join when there is `limit` in the query", 2025
https://www.postgresql.org/message-id/CAAdwFAwm6HwXM_cuPWZBxrxX4E7pBdVg=kcvdsp6q9ume3h...@mail.gmail.com
--
regards, Andrei Lepikhov,
pgEdge
From 361a1878dfaded538e6148ddabcef7e976ce42bf Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 15:35:16 +0200
Subject: [PATCH v0 1/5] Extend the ExecSetTupleBound to LEFT JOIN outer side
---
src/backend/executor/execProcnode.c | 23 +++++++++++++++++++++++
1 file changed, 23 insertions(+)
diff --git a/src/backend/executor/execProcnode.c
b/src/backend/executor/execProcnode.c
index d35976925ae..c3152f3e3ca 100644
--- a/src/backend/executor/execProcnode.c
+++ b/src/backend/executor/execProcnode.c
@@ -978,6 +978,29 @@ ExecSetTupleBound(int64 tuples_needed, PlanState
*child_node)
ExecSetTupleBound(tuples_needed, outerPlanState(child_node));
}
+ else if (IsA(child_node, NestLoopState))
+ {
+ /*
+ * For a NestLoop, the bound can be propagated to the side that
+ * drives the output row count:
+ *
+ * - LEFT JOIN: every outer row produces at least one output row
+ * (with NULLs if no match), so bound the outer side.
+ * - RIGHT JOIN: every inner row produces at least one output
row,
+ * so bound the inner side.
+ *
+ * We do not propagate for INNER, ANTI, or FULL joins, since the
+ * number of output rows can be less than the driving side's
count
+ * (INNER/ANTI may discard rows, FULL may expand both sides).
+ */
+ NestLoopState *nlstate = (NestLoopState *) child_node;
+ JoinType jointype = nlstate->js.jointype;
+
+ if (jointype == JOIN_LEFT)
+ ExecSetTupleBound(tuples_needed,
outerPlanState(child_node));
+ else if (jointype == JOIN_RIGHT)
+ ExecSetTupleBound(tuples_needed,
innerPlanState(child_node));
+ }
/*
* In principle we could descend through any plan node type that is
--
2.53.0
From 32f23607b65b2e0292113a9291c1ca17e6818bc8 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 18:38:49 +0200
Subject: [PATCH v0 2/5] 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 | 110 ++++++++++++++++++++++++++
1 file changed, 110 insertions(+)
diff --git a/src/backend/optimizer/path/joinpath.c
b/src/backend/optimizer/path/joinpath.c
index 713283a73aa..7d9c2b9c7cb 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -2004,6 +2004,116 @@ 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,
+
(jointype == JOIN_LEFT) ?
+
root->limit_tuples : -1.0);
+
+ 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.53.0
From 4d43f8e2d5d22c4e72784065e8a356d17eb85dd2 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Thu, 2 Apr 2026 19:26:34 +0200
Subject: [PATCH v0 3/5] Adjust query plans changed
---
.../postgres_fdw/expected/postgres_fdw.out | 40 +++---
.../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 | 131 ++++++++++--------
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, 228 insertions(+), 251 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index ac34a1acacb..a6f2bb13e24 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2242,21 +2242,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"
@@ -3836,24 +3836,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;
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index d170e7da066..405bd36fed8 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2623,26 +2623,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 5ac966186f7..58fbace2769 100644
--- a/src/test/regress/expected/eager_aggregate.out
+++ b/src/test/regress/expected/eager_aggregate.out
@@ -808,92 +808,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 84872c6f04e..753ef99ae6b 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -9400,29 +9400,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 38643d41fd7..ccae52e1470 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2102,53 +2102,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
@@ -5143,36 +5150,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.53.0
-- Real-life example: Top-10 most popular products with type-specific properties
-- This demonstrates the pre-sorted scan optimization in PG18.
DROP TABLE IF EXISTS electronics_props, clothing_props, food_props, products
CASCADE;
-- Main products table (large)
CREATE TABLE products (
id int PRIMARY KEY,
name text NOT NULL,
category text NOT NULL, -- 'electronics', 'clothing', 'food', ...
popularity int NOT NULL, -- sales count or rating score
price numeric(10,2)
);
-- Type-specific property tables
CREATE TABLE electronics_props (
product_id int PRIMARY KEY REFERENCES products(id),
warranty_months int,
voltage text,
wifi boolean
);
CREATE TABLE clothing_props (
product_id int PRIMARY KEY REFERENCES products(id),
size text,
color text,
material text
);
CREATE TABLE food_props (
product_id int PRIMARY KEY REFERENCES products(id),
expiry_days int,
organic boolean,
allergens text
);
-- Populate products: 100k rows, ~33k per category
INSERT INTO products
SELECT
g,
'Product_' || g,
CASE (g % 3) WHEN 0 THEN 'electronics'
WHEN 1 THEN 'clothing'
ELSE 'food' END,
(random() * 10000)::int, -- popularity 0..10000
(random() * 500 + 1)::numeric(10,2)
FROM generate_series(1, 100000) g;
-- Populate property tables for matching categories
INSERT INTO electronics_props
SELECT id, (random()*36)::int, '220V', (random() > 0.5)
FROM products WHERE category = 'electronics';
INSERT INTO clothing_props
SELECT id,
(ARRAY['S','M','L','XL'])[1 + (random()*3)::int],
(ARRAY['red','blue','black','white'])[1 + (random()*3)::int],
(ARRAY['cotton','polyester','wool'])[1 + (random()*2)::int]
FROM products WHERE category = 'clothing';
INSERT INTO food_props
SELECT id, (random()*365)::int, (random() > 0.5),
(ARRAY['nuts','gluten','dairy',NULL])[1 + (random()*3)::int]
FROM products WHERE category = 'food';
VACUUM ANALYZE;
-- The query: top-10 most popular products with all their properties as JSON
EXPLAIN (ANALYZE, COSTS OFF)
SELECT
p.id,
p.name,
p.category,
p.popularity,
p.price,
json_strip_nulls(json_build_object(
'warranty_months', e.warranty_months,
'voltage', e.voltage,
'wifi', e.wifi,
'size', c.size,
'color', c.color,
'material', c.material,
'expiry_days', f.expiry_days,
'organic', f.organic,
'allergens', f.allergens
)) AS properties
FROM products p
LEFT JOIN electronics_props e ON e.product_id = p.id
LEFT JOIN clothing_props c ON c.product_id = p.id
LEFT JOIN food_props f ON f.product_id = p.id
ORDER BY p.popularity DESC
LIMIT 10;
/*
Limit (actual time=43.924..44.176 rows=10.00 loops=1)
Buffers: shared hit=940
-> Nested Loop Left Join (actual time=43.922..44.172 rows=10.00 loops=1)
Buffers: shared hit=940
-> Nested Loop Left Join (actual time=43.865..43.980 rows=10.00
loops=1)
Buffers: shared hit=918
-> Nested Loop Left Join (actual time=43.851..43.903 rows=10.00
loops=1)
Buffers: shared hit=892
-> Sort (actual time=43.825..43.826 rows=10.00 loops=1)
Sort Key: p.popularity DESC
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=870
-> Seq Scan on products p (actual time=0.016..9.847
rows=100000.00 loops=1)
Buffers: shared hit=870
-> Index Scan using electronics_props_pkey on
electronics_props e (actual time=0.006..0.006 rows=0.20 loops=10)
Index Cond: (product_id = p.id)
Index Searches: 10
Buffers: shared hit=22
-> Index Scan using clothing_props_pkey on clothing_props c
(actual time=0.007..0.007 rows=0.60 loops=10)
Index Cond: (product_id = p.id)
Index Searches: 10
Buffers: shared hit=26
-> Index Scan using food_props_pkey on food_props f (actual
time=0.006..0.006 rows=0.20 loops=10)
Index Cond: (product_id = p.id)
Index Searches: 10
Buffers: shared hit=22
Planning:
Buffers: shared hit=36
Planning Time: 2.150 ms
Execution Time: 44.305 ms
(30 rows)
Limit (actual time=813.491..813.495 rows=10.00 loops=1)
Buffers: shared hit=1442
-> Sort (actual time=813.489..813.491 rows=10.00 loops=1)
Sort Key: p.popularity DESC
Sort Method: top-N heapsort Memory: 27kB
Buffers: shared hit=1442
-> Hash Left Join (actual time=73.483..772.134 rows=100000.00 loops=1)
Hash Cond: (p.id = f.product_id)
Buffers: shared hit=1442
-> Hash Left Join (actual time=54.732..128.615 rows=100000.00
loops=1)
Hash Cond: (p.id = c.product_id)
Buffers: shared hit=1261
-> Hash Left Join (actual time=32.330..79.951
rows=100000.00 loops=1)
Hash Cond: (p.id = e.product_id)
Buffers: shared hit=1051
-> Seq Scan on products p (actual time=0.036..6.171
rows=100000.00 loops=1)
Buffers: shared hit=870
-> Hash (actual time=32.189..32.189 rows=33333.00
loops=1)
Buckets: 65536 Batches: 1 Memory Usage:
2075kB
Buffers: shared hit=181
-> Seq Scan on electronics_props e (actual
time=0.028..12.981 rows=33333.00 loops=1)
Buffers: shared hit=181
-> Hash (actual time=22.354..22.354 rows=33334.00 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2247kB
Buffers: shared hit=210
-> Seq Scan on clothing_props c (actual
time=0.018..8.800 rows=33334.00 loops=1)
Buffers: shared hit=210
-> Hash (actual time=18.680..18.681 rows=33333.00 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2054kB
Buffers: shared hit=181
-> Seq Scan on food_props f (actual time=0.008..7.573
rows=33333.00 loops=1)
Buffers: shared hit=181
Planning:
Buffers: shared hit=73
Planning Time: 3.966 ms
Execution Time: 813.815 ms
(36 rows)
*/
