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

On Thu, 15 Dec 2022 at 11:47, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Nathaniel Hazelton <nathaniel(at)sturdyai(dot)com> writes:
> > 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?
>
> You've got a view with an unused CTE? Why?

hmm, but the CTE isn't unused. It's just that we don't push quals
down due to the "materialized". Perhaps the view only contains the
CTE? Strange way to write a view... Perhaps there was some reason to
materialise it that's not obvious to us.

> But at any rate, yes, the presence of a CTE is an optimization
> fence: we can't flatten the subquery containing it without losing
> the query level at which the CTE should be evaluated. Perhaps there
> are special cases where that could be relaxed, but nobody's tried
> very hard.

Just to expand on that a little. Prior to v12, CTEs could never have
quals pushed down into them. That was relaxed in v12 and the original
behaviour can be obtained using "materialized". Without that, the
planner is more free to perform its typical optimisations such as
pulling up subqueries or pushing quals down into subqueries.

David