[PATCH] Allow subquery pull-up past inlineable CTEs

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