Hi hackers,
This patch allows subquery pull-up to proceed when the subquery contains
CTEs that are all inlineable. Previously, is_simple_subquery() rejected
any subquery with a non-empty cteList, which meant that even trivially
inlineable CTEs blocked pull-up.
Problem
-------
A subquery with an inlineable CTE currently forces a Subquery Scan node
that cannot be eliminated:
explain (costs off)
select s.id, t.val
from cte_pullup_s s left join (
select * from (
with cte as not materialized (select id, val from cte_pullup_t)
select id, val from cte
) sub
) t on t.id = s.tid
where s.id < 5;
Before the patch:
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)
After:
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)
The CTE is inlineable, the subquery is simple - but
pull_up_simple_subquery
has Assert(subquery->cteList == NIL), and is_simple_subquery() rejects
any
subquery with CTEs outright. The original comment even says "(XXX WITH
could possibly be allowed later)".
Approach
--------
1. Extract the CTE inlineability conditions from SS_process_ctes() into
is_cte_inlineable() - the single place that decides whether a CTE
can be inlined. Two new helpers use it:
- SS_all_ctes_inlineable(Query *subquery): returns true if every CTE
in cteList passes is_cte_inlineable(). Unreferenced SELECT CTEs
(cterefcount == 0) cause it to return false, since those are
neither inlined nor materialized and would be lost. Used in
is_simple_subquery().
- SS_inline_ctes(PlannerInfo *root): inline all inlineable CTEs
(RTE_CTE -> RTE_SUBQUERY via inline_cte_walker), then set
root->parse->cteList = NIL. Called in pull_up_simple_subquery().
2. Relax is_simple_subquery(): replace `subquery->cteList` with
`(subquery->cteList && !SS_all_ctes_inlineable(subquery))`.
3. In pull_up_simple_subquery(), after copyObject(rte->subquery) and
subroot initialization, call SS_inline_ctes(subroot) when
subquery->cteList is non-empty. This clears cteList before the
Assert. Since pull_up_simple_subquery already works on a copy of
rte->subquery, inlining on the copy leaves the original RTE untouched
if pull-up is later abandoned at the recheck.
New tests in with.sql cover:
- NOT MATERIALIZED CTE in subquery: inlined, subquery pulled up
(Merge Right Join with Index Scan instead of CTE Scan + Hash Join)
- Default (singly-referenced) CTE in subquery: same behavior
- MATERIALIZED CTE in subquery: pull-up correctly blocked
- CTE with volatile function (random()): pull-up correctly blocked
An existing test case that had a Subquery Scan over a simple int8_tbl
subquery with an inlineable CTE now produces a direct Seq Scan, which
is the expected improvement.
SS_all_ctes_inlineable() calls contain_volatile_functions() and
contain_outer_selfref() via is_cte_inlineable(), which walk the CTE
query tree. Since such subqueries were previously rejected immediately
(without any CTE-related work), this adds overhead only in cases that
previously could not be pulled up at all - so any extra cost is
amortized by the pull-up improvement.
Thoughts on the approach?
Regards,
Andrey KazarinovFrom b0bb15a58bf537c3ac3ba29fdc1f833042885957 Mon Sep 17 00:00:00 2001
From: Andrey Kazarinov <[email protected]>
Date: Wed, 13 May 2026 14:27:30 +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 | 142 ++++++++++++++++------
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, 243 insertions(+), 45 deletions(-)
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 2cf5c15a309..9f34da92018 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,110 @@ 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 is either unreferenced (SELECT)
+ * or passes the inlineability checks of is_cte_inlineable(). This is 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;
+
+ /*
+ * Handle the case of unreferenced SELECT CTEs,
+ * which are neither inlined nor materialized.
+ */
+ 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 18f05caac3b..26222474c57 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);
@@ -1980,7 +1991,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
@@ -1999,7 +2012,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.53.0
