[PATCH] Allow subquery pull-up past inlineable CTEs
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253700psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 09, 2026 at 02:33 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253700_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253700_3 && git checkout t253700_3Patchset v3 (message #3) is on t253700_3
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 Kazarinov
Hi Andrey,
I tested this patch on current master. The patch applies and builds
successfully, and the main optimization case works as expected in my
testing.
For an inlineable CTE inside the subquery, the patched plan changes to
the same general shape as the equivalent query without the CTE,
allowing the planner to consider the index/merge-join path.
I also tested a few negative cases:
- MATERIALIZED CTE: remains a CTE Scan, as expected.
- CTE containing a volatile function (random()): remains a CTE Scan.
- A default CTE referenced twice: remains materialized and is read
through two CTE Scan nodes.
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)
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.
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 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:
kazarandrey@yandex.ru
Regards,
Andrey Kazarinov
Hi Andrey,
Thanks for the clarification. That explains the difference I saw: the
CTE itself had already been inlined by SS_process_ctes, while the
remaining cteList still prevented the surrounding subquery from being
pulled up.
I tested v2 on current master.
For the inlineable cases, both a NOT MATERIALIZED CTE and a default
singly-referenced CTE were successfully pulled up. In both cases the
planner produced a nested-loop plan using the primary-key index on the
inner table through the outer join condition.
I also retested the negative cases:
* A MATERIALIZED CTE still prevents pull-up and remains a CTE Scan.
* A CTE containing a volatile function still remains a CTE Scan.
* A default multiply-referenced CTE remains materialized and is read
through two CTE Scan nodes.
* I also tested an unreferenced SELECT CTE specifically for the
cterefcount == 0 case. The surrounding subquery remains a Subquery
Scan, so it is not incorrectly accepted through the new all-inlineable
path.
For the representative inlineable case, I also compared the query
result with the equivalent query without the CTE, and the results were
identical.
The updated SS_all_ctes_inlineable() comment now matches the implementation.
I did not find any new issues in these tests.
I couldn't find this patch in the CommitFest app. If you plan to
submit it for the next CommitFest, I'd be happy to add myself as a
reviewer there as well.
Regards,
Clemenza
On Tue, Sep 8, 2026 at 10:19 PM Andrey Kazarinov
<a.kazarinov@postgrespro.ru> wrote:
Show quoted text
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 ZhangI 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:
kazarandrey@yandex.ruRegards,
Andrey Kazarinov
<div><div>> Re: Clemenza Zhang</div><div>> I couldn't find this patch in the CommitFest app. If you plan to<br />> submit it for the next CommitFest, I'd be happy to add myself as a<br />> reviewer there as well.</div><div> </div><div>I made a CommitFest patch <a href="https://commitfest.postgresql.org/patch/7283/" rel="noopener noreferrer" target="_blank">https://commitfest.postgresql.org/patch/7283/</a></div><div>Join, please, as a reviewer there. This is my first patch on CommitFest,</div><div>so <span style="white-space:pre-wrap">I may have filled</span> some fields not properly right.</div></div><div> </div><div><div>Regards,<br />Andrey Kazarinov</div></div>
Sorry, I didn't see that letter in HTML format by default. Copy it.
Re: Clemenza Zhang
I couldn't find this patch in the CommitFest app. If you plan to
submit it for the next CommitFest, I'd be happy to add myself as a
reviewer there as well.
I made a CommitFest patch https://commitfest.postgresql.org/patch/7283/
Join, please, as a reviewer there. This is my first patch on CommitFest,
so I may have filled some fields not properly right.
Regards,
Andrey Kazarinov