PostgreSQL select-only CTE removal is too aggressive?

On Mon, 29 Jun 2026 at 13:52, Kirill Reshke <reshkekirill@gmail.com> wrote:

Hi!

PostgreSQL planner currently replaces not-referenced CTE with dummy
relations here [0]

But it seems to be too aggressive for subqueries with side-effects:

```
create table z(i int, j int, k int);
create or replace function f() returns void as $$ insert into z
values(1,1,1) $$ language sql;
reshke=# explain with d as (select f(1)) select 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4)
(1 row)

```

I don't find where this behaviour is actually explicitly documented.

For data-modifying CTEs we have this[1]:

```
Data-modifying statements in WITH are executed exactly once, and
always to completion, independently of whether the primary query reads
all (or indeed any) of their output. Notice that this is different
from the rule for SELECT in WITH: as stated in the previous section,
execution of a SELECT is carried only as far as the primary query
demands its output.
```

Maybe we should add here a notice, that execution of an unreferenced
SELECT CTE, even one containing a data-modifying function, is not
guaranteed?

[0] https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/optimizer/plan/subselect.c?id=26255a320733de2d91a30bd6ae529dd01e7f3409#n905
[1] https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

--
Best regards,
Kirill Reshke

Also, PFA simple patch that fixes exactly my case and maybe breaks
many others. WDYT?

```
reshke@yezzey-cbdb-bench:~/pg$ git diff src/backend/optimizer/plan/subselect.c
diff --git a/src/backend/optimizer/plan/subselect.c
b/src/backend/optimizer/plan/subselect.c
index 6aa8971c95d..967fd4e7bf8 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -904,7 +904,7 @@ SS_process_ctes(PlannerInfo *root)
                /*
                 * Ignore SELECT CTEs that are not actually referenced anywhere.
                 */
-               if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
+               if (cte->cterefcount == 0 && cmdType == CMD_SELECT &&
!contain_volatile_functions(cte->ctequery))
                {
                        /* Make a dummy entry in cte_plan_ids */
                        root->cte_plan_ids =
lappend_int(root->cte_plan_ids, -1);
```

--
Best regards,
Kirill Reshke

Kirill Reshke <reshkekirill@gmail.com> writes:

I don't find where this behaviour is actually explicitly documented.

For data-modifying CTEs we have this[1]:

Data-modifying statements in WITH are executed exactly once, and
always to completion, independently of whether the primary query reads
all (or indeed any) of their output. Notice that this is different
from the rule for SELECT in WITH: as stated in the previous section,
execution of a SELECT is carried only as far as the primary query
demands its output.

I don't see how this is not a direct, obvious consequence of that
rule. The primary query demands none of the unreferenced CTE's
output, therefore it is not executed at all.

The referenced text in the "previous section" is

This works because PostgreSQL's implementation
evaluates only as many rows of a WITH query as are actually
fetched by the parent query.

which is the same thing in slightly different words.

regards, tom lane

On Mon, 29 Jun 2026, 20:17 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

Kirill Reshke <reshkekirill@gmail.com> writes:

I don't find where this behaviour is actually explicitly documented.

For data-modifying CTEs we have this[1]:

Data-modifying statements in WITH are executed exactly once, and
always to completion, independently of whether the primary query reads
all (or indeed any) of their output. Notice that this is different
from the rule for SELECT in WITH: as stated in the previous section,
execution of a SELECT is carried only as far as the primary query
demands its output.

I don't see how this is not a direct, obvious consequence of that
rule. The primary query demands none of the unreferenced CTE's
output, therefore it is not executed at all.

The referenced text in the "previous section" is

This works because PostgreSQL's implementation
evaluates only as many rows of a WITH query as are actually
fetched by the parent query.

which is the same thing in slightly different words.

regards, tom lane

Thank you for clarifications.

I have been thinking for a while about this optimization. The thing is,
there is currently no way of forcing this CTE evaluation other than
referencing it from other query parts. I think this is little clumsy. What
if we use MATERIALIZE here for this purpose? So,

WITH d as MATERIALIZED (select f()) select 1 ;

will force evaluation? Does this strike you as good idea?

Kirill Reshke <reshkekirill@gmail.com> writes:

I have been thinking for a while about this optimization. The thing is,
there is currently no way of forcing this CTE evaluation other than
referencing it from other query parts. I think this is little clumsy. What
if we use MATERIALIZE here for this purpose? So,
WITH d as MATERIALIZED (select f()) select 1 ;
will force evaluation? Does this strike you as good idea?

No. What is the point?

Also, this would be far from a one-liner change. Stopping the
planner from throwing away unreferenced CTEs wouldn't cause the
executor to run them. Currently the executor only pays attention
to running ModifyTable nodes to completion.

The short answer here is that this feature has behaved like this since
we implemented it, in 2008 (cf commit 44d5be0e5). AFAIR you are the
first to complain about it. You should seriously consider the
possibility that it's you that are mistaken about the value of doing
things differently. Even if there is some value, I think that
backwards compatibility would argue against changing the semantics.

regards, tom lane

On Mon, Jun 29, 2026 at 12:58 PM Kirill Reshke <reshkekirill@gmail.com>
wrote:

I have been thinking for a while about this optimization. The thing is,
there is currently no way of forcing this CTE evaluation other than
referencing it from other query parts. I think this is little clumsy. What
if we use MATERIALIZE here for this purpose? So,

I find myself usually, if something has side-effects, returning result
information about what effects it had. That means propagating the result
(usually a jsonb object) out of the main query so the caller (usually a
function) can grab ahold of it to return to the client. Even just a row
count and a label works.

David J.