I could not reproduce this exact before-patch plan on current master.
With the NOT MATERIALIZED example, current unpatched master already
produces a direct Seq Scan on cte_pullup_t rather than a CTE Scan on
cte.
Hash Right Join
Hash Cond: (cte_pullup_t.id = s.tid)
-> Seq Scan on cte_pullup_t
-> Hash
-> Seq Scan on cte_pullup_s s
Filter: (id < 5)
The patch still changes the higher-level plan shape in my test: the
inlineable-CTE case becomes the same general shape as the equivalent
no-CTE query.
Hi Clemenza,
Thank you for testing and for the detailed feedback.
Here is a reproducible example using the standard regression tables
tenk1 and tenk2 (both have 10000 rows; tenk1.unique1 is a primary key):
explain (costs off)
select * from tenk2 s left join (
with cte as not materialized (select unique1, two from tenk1)
select * from (select unique1, two from cte) sub
) t on t.unique1 = s.unique1
where s.unique1 < 10;
Before the patch (unpatched master):
Hash Right Join
Hash Cond: (tenk1.unique1 = s.unique1)
-> Seq Scan on tenk1
-> Hash
-> Bitmap Heap Scan on tenk2 s
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk2_unique1
Index Cond: (unique1 < 10)
After the patch:
Nested Loop Left Join
-> Seq Scan on tenk2 s
Filter: (unique1 < 10)
-> Index Scan using tenk1_pkey on tenk1
Index Cond: (unique1 = s.unique1)
Without the patch, the subquery is planned separately: SS_process_ctes
inlines the CTE, but is_simple_subquery still rejects the subquery
because cteList is non-empty - the planner cannot see that the join
condition t.unique1 = s.unique1 could use the primary key index on
tenk1,
so it falls back to a hash join with a full seq scan.
With the patch, the subquery is pulled up into the parent query.
The planner can now see through the former subquery boundary and chooses
a
nested loop with index scan (only 10 index lookups instead of scanning
10000 rows).
I also noticed a small inconsistency in the comment above
SS_all_ctes_inlineable(). It says that every CTE is either
"unreferenced (SELECT) or passes the inlineability checks", but the
implementation explicitly returns false for:
```
if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
return false;
```
This behavior matches the explanation in your email, so I think the
comment may just need adjustment.
Regards,
Clemenza Zhang
I have fixed the comment above SS_all_ctes_inlineable().
The function header comment now briefly notes that unreferenced CTEs
cause
it to return false, and the inline comment at the check site explains
the reason in detail: unreferenced SELECT CTEs (cterefcount == 0) are
neither inlined nor materialized by SS_process_ctes -- they are simply
skipped with a dummy entry in cte_plan_ids.
Updated patch attached.
P.S. I most likely continue discussion from another email:
[email protected]
Regards,
Andrey Kazarinov
From 3b658d9a62be0d326e1f4a031df6d38731eac23d Mon Sep 17 00:00:00 2001
From: Andrey Kazarinov <[email protected]>
Date: Tue, 8 Sep 2026 16:13:19 +0300
Subject: [PATCH] Allow subquery pull-up past inlineable CTEs
Extract CTE inlineability checks into is_cte_inlineable() as the single
source of truth, and use it to permit pull-up when all CTEs are inlineable.
CTE inlining happens on the copy inside pull_up_simple_subquery so the
original RTE is preserved on fallback
---
src/backend/optimizer/plan/subselect.c | 143 ++++++++++++++++------
src/backend/optimizer/prep/prepjointree.c | 17 ++-
src/include/optimizer/subselect.h | 2 +
src/test/regress/expected/with.out | 87 +++++++++++--
src/test/regress/sql/with.sql | 40 ++++++
5 files changed, 244 insertions(+), 45 deletions(-)
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 2cf5c15a309..73eb91ffefe 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -91,6 +91,7 @@ static bool contain_outer_selfref(Node *node);
static bool contain_outer_selfref_walker(Node *node, Index *depth);
static void inline_cte(PlannerInfo *root, CommonTableExpr *cte);
static bool inline_cte_walker(Node *node, inline_cte_walker_context *context);
+static bool is_cte_inlineable(CommonTableExpr *cte);
static bool sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink);
static bool simplify_EXISTS_query(PlannerInfo *root, Query *query);
static Query *convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect,
@@ -918,43 +919,8 @@ SS_process_ctes(PlannerInfo *root)
/*
* Consider inlining the CTE (creating RTE_SUBQUERY RTE(s)) instead of
* implementing it as a separately-planned CTE.
- *
- * We cannot inline if any of these conditions hold:
- *
- * 1. The user said not to (the CTEMaterializeAlways option).
- *
- * 2. The CTE is recursive.
- *
- * 3. The CTE has side-effects; this includes either not being a plain
- * SELECT, or containing volatile functions. Inlining might change
- * the side-effects, which would be bad.
- *
- * 4. The CTE is multiply-referenced and contains a self-reference to
- * a recursive CTE outside itself. Inlining would result in multiple
- * recursive self-references, which we don't support.
- *
- * Otherwise, we have an option whether to inline or not. That should
- * always be a win if there's just a single reference, but if the CTE
- * is multiply-referenced then it's unclear: inlining adds duplicate
- * computations, but the ability to absorb restrictions from the outer
- * query level could outweigh that. We do not have nearly enough
- * information at this point to tell whether that's true, so we let
- * the user express a preference. Our default behavior is to inline
- * only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
- * will be inlined even if multiply referenced.
- *
- * Note: we check for volatile functions last, because that's more
- * expensive than the other tests needed.
*/
- if ((cte->ctematerialized == CTEMaterializeNever ||
- (cte->ctematerialized == CTEMaterializeDefault &&
- cte->cterefcount == 1)) &&
- !cte->cterecursive &&
- cmdType == CMD_SELECT &&
- !contain_dml(cte->ctequery) &&
- (cte->cterefcount <= 1 ||
- !contain_outer_selfref(cte->ctequery)) &&
- !contain_volatile_functions(cte->ctequery))
+ if (is_cte_inlineable(cte))
{
inline_cte(root, cte);
/* Make a dummy entry in cte_plan_ids */
@@ -1223,6 +1189,111 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
return expression_tree_walker(node, inline_cte_walker, context);
}
+/*
+ * We cannot inline if any of these conditions hold:
+ *
+ * 1. The user said not to (the CTEMaterializeAlways option).
+ *
+ * 2. The CTE is recursive.
+ *
+ * 3. The CTE has side-effects; this includes either not being a plain
+ * SELECT, or containing volatile functions. Inlining might change
+ * the side-effects, which would be bad.
+ *
+ * 4. The CTE is multiply-referenced and contains a self-reference to
+ * a recursive CTE outside itself. Inlining would result in multiple
+ * recursive self-references, which we don't support.
+ *
+ * Otherwise, we have an option whether to inline or not. That should
+ * always be a win if there's just a single reference, but if the CTE
+ * is multiply-referenced then it's unclear: inlining adds duplicate
+ * computations, but the ability to absorb restrictions from the outer
+ * query level could outweigh that. We do not have nearly enough
+ * information at this point to tell whether that's true, so we let
+ * the user express a preference. Our default behavior is to inline
+ * only singly-referenced CTEs, but a CTE marked CTEMaterializeNever
+ * will be inlined even if multiply referenced.
+ *
+ * Note: we check for volatile functions last, because that's more
+ * expensive than the other tests needed.
+ */
+static bool
+is_cte_inlineable(CommonTableExpr *cte)
+{
+ CmdType cmdType = ((Query *) cte->ctequery)->commandType;
+
+ return (cte->ctematerialized == CTEMaterializeNever ||
+ (cte->ctematerialized == CTEMaterializeDefault &&
+ cte->cterefcount == 1)) &&
+ !cte->cterecursive &&
+ cmdType == CMD_SELECT &&
+ !contain_dml(cte->ctequery) &&
+ (cte->cterefcount <= 1 ||
+ !contain_outer_selfref(cte->ctequery)) &&
+ !contain_volatile_functions((Node *) cte->ctequery);
+}
+
+/*
+ * SS_all_ctes_inlineable: are all CTEs in the query inlineable?
+ *
+ * Returns true if every CTE in cteList passes the inlineability checks
+ * of is_cte_inlineable(). Returns false if any CTE is not inlineable,
+ * or if there are unreferenced CTEs. Used by is_simple_subquery to determine
+ * whether a subquery with CTEs can still be pulled up.
+ */
+bool
+SS_all_ctes_inlineable(Query *subquery)
+{
+ ListCell *lc;
+
+ foreach(lc, subquery->cteList)
+ {
+ CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+ CmdType cmdType = ((Query *) cte->ctequery)->commandType;
+
+ /*
+ * Unreferenced SELECT CTEs are neither inlined nor materialized
+ * by SS_process_ctes -- they are simply skipped with a dummy entry
+ * in cte_plan_ids.
+ */
+ if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
+ return false;
+
+ if (!is_cte_inlineable(cte))
+ return false;
+ }
+
+ return true;
+}
+
+/*
+ * SS_inline_ctes: inline all inlineable CTEs in the given query.
+ *
+ * Inlineable CTEs are replaced with RTE_SUBQUERY references via
+ * inline_cte_walker, and removed from cteList.
+ *
+ * Inlineability conditions are determined by SS_all_ctes_inlineable().
+ */
+void
+SS_inline_ctes(PlannerInfo *root)
+{
+ ListCell *lc;
+
+ foreach(lc, root->parse->cteList)
+ {
+ CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+
+ Assert(((Query *) cte->ctequery)->commandType == CMD_SELECT &&
+ is_cte_inlineable(cte));
+
+ inline_cte(root, cte);
+ }
+
+ /* CTEs have inlined, so we can clean this list */
+ root->parse->cteList = NIL;
+ return;
+}
+
/*
* Attempt to transform 'testexpr' over the VALUES subquery into
* a ScalarArrayOpExpr. We currently support the transformation only when
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index dfe320beccd..1b5b8aa9cf3 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1481,6 +1481,17 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
subroot->non_recursive_path = NULL;
/* We don't currently need a top JoinDomain for the subroot */
+ /*
+ * If the subquery has inlineable CTEs, inline them now so that the
+ * Assert below is satisfied. is_simple_subquery should have already
+ * verified that all CTEs are inlineable, so SS_inline_ctes is expected
+ * to succeed.
+ */
+ if (subquery->cteList)
+ {
+ SS_inline_ctes(subroot);
+ }
+
/* No CTEs to worry about */
Assert(subquery->cteList == NIL);
@@ -1976,7 +1987,9 @@ is_simple_subquery(PlannerInfo *root, Query *subquery, RangeTblEntry *rte,
/*
* Can't pull up a subquery involving grouping, aggregation, SRFs,
- * sorting, limiting, or WITH. (XXX WITH could possibly be allowed later)
+ * sorting, limiting, or non-inlineable CTEs. CTEs that are inlineable
+ * will be inlined by pull_up_simple_subquery before the
+ * Assert(cteList==NIL).
*
* We also don't pull up a subquery that has explicit FOR UPDATE/SHARE
* clauses, because pullup would cause the locking to occur semantically
@@ -1995,7 +2008,7 @@ is_simple_subquery(PlannerInfo *root, Query *subquery, RangeTblEntry *rte,
subquery->limitOffset ||
subquery->limitCount ||
subquery->hasForUpdate ||
- subquery->cteList)
+ (subquery->cteList && !SS_all_ctes_inlineable(subquery)))
return false;
/*
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index 4ecccf46bd3..b7c91865260 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -17,6 +17,8 @@
#include "nodes/plannodes.h"
extern void SS_process_ctes(PlannerInfo *root);
+extern bool SS_all_ctes_inlineable(Query *subquery);
+extern void SS_inline_ctes(PlannerInfo *root);
extern ScalarArrayOpExpr *convert_VALUES_to_ANY(PlannerInfo *root,
Node *testexpr,
Query *values);
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index addb24896be..1c81ca713a1 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2921,13 +2921,11 @@ SELECT q1 FROM
SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
FROM int8_tbl i8
) ss;
- QUERY PLAN
---------------------------------------
- Subquery Scan on ss
- Output: ss.q1
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, NULL::bigint
-(4 rows)
+ QUERY PLAN
+--------------------------------
+ Seq Scan on public.int8_tbl i8
+ Output: i8.q1
+(2 rows)
SELECT q1 FROM
(
@@ -3854,3 +3852,78 @@ WHERE t1.two = 0 AND t2.two = 0 AND t1.thousand = t2.thousand;
Filter: (two = 0)
(11 rows)
+-- Test CTE inlining during subquery pull-up
+CREATE TABLE cte_pullup_t (id INT PRIMARY KEY, val TEXT);
+INSERT INTO cte_pullup_t SELECT g, 'val' || g FROM generate_series(1,100) g;
+CREATE TABLE cte_pullup_s (id INT, tid INT);
+INSERT INTO cte_pullup_s SELECT g, (g % 10) + 1 FROM generate_series(1,50) g;
+ANALYZE cte_pullup_t, cte_pullup_s;
+-- NOT MATERIALIZED CTE in subquery: should be inlined and pulled up
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS NOT materialized (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+ QUERY PLAN
+----------------------------------------------------------
+ Merge Right Join
+ Merge Cond: (cte_pullup_t.id = s.tid)
+ -> Index Scan using cte_pullup_t_pkey on cte_pullup_t
+ -> Sort
+ Sort Key: s.tid
+ -> Seq Scan on cte_pullup_s s
+ Filter: (id < 5)
+(7 rows)
+
+-- Default (singly-referenced) CTE in subquery: same, should be inlined and pulled up
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+ QUERY PLAN
+----------------------------------------------------------
+ Merge Right Join
+ Merge Cond: (cte_pullup_t.id = s.tid)
+ -> Index Scan using cte_pullup_t_pkey on cte_pullup_t
+ -> Sort
+ Sort Key: s.tid
+ -> Seq Scan on cte_pullup_s s
+ Filter: (id < 5)
+(7 rows)
+
+-- MATERIALIZED CTE in subquery: should NOT be inlined
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS materialized (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+ QUERY PLAN
+----------------------------------------
+ Hash Right Join
+ Hash Cond: (cte.id = s.tid)
+ -> CTE Scan on cte
+ CTE cte
+ -> Seq Scan on cte_pullup_t
+ -> Hash
+ -> Seq Scan on cte_pullup_s s
+ Filter: (id < 5)
+(8 rows)
+
+-- CTE WITH volatile function: should NOT be inlined
+EXPLAIN (COSTS OFF)
+WITH cte AS NOT materialized (SELECT id, random() AS r FROM cte_pullup_t)
+SELECT * FROM (SELECT id FROM cte) sub WHERE id = 1;
+ QUERY PLAN
+----------------------------------
+ CTE Scan on cte
+ Filter: (id = 1)
+ CTE cte
+ -> Seq Scan on cte_pullup_t
+(4 rows)
+
+DROP TABLE cte_pullup_s;
+DROP TABLE cte_pullup_t;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 0cb26312e21..bfcb56a77b4 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1800,3 +1800,43 @@ EXPLAIN (COSTS OFF)
WITH cte AS (SELECT DISTINCT two, thousand FROM tenk1)
SELECT * FROM cte t1, cte t2
WHERE t1.two = 0 AND t2.two = 0 AND t1.thousand = t2.thousand;
+
+-- Test CTE inlining during subquery pull-up
+CREATE TABLE cte_pullup_t (id INT PRIMARY KEY, val TEXT);
+INSERT INTO cte_pullup_t SELECT g, 'val' || g FROM generate_series(1,100) g;
+CREATE TABLE cte_pullup_s (id INT, tid INT);
+INSERT INTO cte_pullup_s SELECT g, (g % 10) + 1 FROM generate_series(1,50) g;
+
+ANALYZE cte_pullup_t, cte_pullup_s;
+
+-- NOT MATERIALIZED CTE in subquery: should be inlined and pulled up
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS NOT materialized (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+
+-- Default (singly-referenced) CTE in subquery: same, should be inlined and pulled up
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+
+-- MATERIALIZED CTE in subquery: should NOT be inlined
+EXPLAIN (COSTS OFF)
+SELECT * FROM cte_pullup_s s LEFT JOIN (
+ WITH cte AS materialized (SELECT id, val FROM cte_pullup_t)
+ SELECT * FROM (SELECT id, val FROM cte) sub
+) t on t.id = s.tid
+WHERE s.id < 5;
+
+-- CTE WITH volatile function: should NOT be inlined
+EXPLAIN (COSTS OFF)
+WITH cte AS NOT materialized (SELECT id, random() AS r FROM cte_pullup_t)
+SELECT * FROM (SELECT id FROM cte) sub WHERE id = 1;
+
+DROP TABLE cte_pullup_s;
+DROP TABLE cte_pullup_t;
--
2.43.0
