Re: BUG #17721: A completely unused CTE negatively affect Query Plan

The CTE in that location was emulating a view in which we have a CTE - which responds the same way. Does this mean CTEs can never be used in views that could be flattened without affecting performance? I'm able to find any mention of this. The only mention of CTEs being optimization fences goes back when all CTEs were materialized.

Is there some way to know when it is safe or not to use a CTE in a view / subquery? It would be great to have some documentation on that.

On 12/14/22, 11:25 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:

PG Bug reporting form <noreply(at)postgresql(dot)org <mailto:noreply(at)postgresql(dot)org>> writes:
> I've boiled down an issue we have in production to a simple query that can
> demonstrate it. I've run this on 13,14 and 15 locally in a docker container
> with the same results. If a CTE that is completely unexecuted exists in a
> subquery (or view in our production case) it affects the query plan VERY
> negatively. The first explain shows a sequential scan, where the second
> explain shows an index scan, just by the existence of the (obviously)
> unexecuted CTE.

The presence of the CTE prevents flattening of the subquery.
If you don't like it, don't attach the CTE right there.

regards, tom lane