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

On 12/14/22, 10:46 PM, "David Rowley" <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com>> wrote:

On Thu, 15 Dec 2022 at 11:47, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Nathaniel Hazelton <nathaniel(at)sturdyai(dot)com <mailto: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.

NH: Oops. I posted a slightly incorrect version of what I was trying to show. Remove the 'materialized', and it acts the same way. Given that it selects 1/0, it's clearly not executed, materialized or not. BUT it does affect the query plan, materialized OR NOT, which is the part I couldn't understand. We do not have an unused CTE in our view. I was just attempting to boil what I saw as a bug down to its minimal example.

> 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.

NH; We do use a non-materialized CTE in the view, because we want to use a CTE for what it's for - organizing our code. Given that it's not materialized, we thought there should be no ill effect. Remove the materialized from the original post, and you will see it DOES affect the query plan.

NH: I will post a more complete example with views today.

Nathaniel