Re: SOLVED - RE: Poor performance using CTE

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> ... Perhaps even including a
> small blurb about what an optimization barrier even means (my
> understanding is that it merely forces materialization of that part of
> the query).

FWIW, it has nothing to do with materialization; it means that we don't
push conditions down into that subquery, nor pull subexpressions up out
of it, nor rearrange join order across the subquery boundary. In short
the subquery is planned separately from the outer query. But it could
then be run by the executor in the usual tuple-at-a-time fashion,
without materializing the whole subquery result.

It is true that CTEScan nodes materialize the subquery output (ie copy
it into a tuplestore), but that's to support multiple CTEScans reading
the same CTE. One of the optimizations we *should* put in place
sometime is skipping the tuplestore if there's only one CTEScan on the
CTE.

regards, tom lane