Re: PostgreSQL select-only CTE removal is too aggressive?

On Mon, 29 Jun 2026, 20:17 Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kirill Reshke <reshkekirill(at)gmail(dot)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?

>