Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote:
> On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Jay Levitt <jay(dot)levitt(at)gmail(dot)com> writes:
> >> So you can see where I'm going. I know if I break everything into
> >> elegant, composable functions, it'll continue to perform poorly. If I
> >> write one big hairy, it'll perform great but it will be difficult to
> >> maintain, and it will be inelegant and a kitten will die. My tools
> >> are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and
> >> views (and other tools?) What optimizations do each of those prevent?
> >
> > plpgsql functions are black boxes to the optimizer. If you can express
> > your functions as single SQL commands, using SQL-language functions is
> > usually a better bet than plpgsql.
> >
> > CTEs are also treated as optimization fences; this is not so much an
> > optimizer limitation as to keep the semantics sane when the CTE contains
> > a writable query.
>
> I wonder if we need to rethink, though. We've gotten a number of
> reports of problems that were caused by single-use CTEs not being
> equivalent - in terms of performance - to a non-CTE formulation of the
> same idea. It seems necessary for CTEs to behave this way when the
> subquery modifies data, and there are certainly situations where it
> could be desirable otherwise, but I'm starting to think that we
> shouldn't do it that way by default. Perhaps we could let people say
> something like WITH x AS FENCE (...) when they want the fencing
> behavior, and otherwise assume they don't (but give it to them anyway
> if there's a data-modifying operation in there).
+1. I avoid writing CTEs in many cases where they would be very useful just
for that reasons.
I don't even think some future inlining necessarily has to be restricted to
one-use cases only...

+1 for making fencing behaviour as well. Currently there is no real explicit
method to specify this which is necessarily future proof (WITH, OFFSET 0)...

Andres