From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting,
	flattening, etc?
Date: Wed, 2 Nov 2011 18:13:06 +0100
User-Agent: KMail/1.13.7 (Linux/3.0.0-2-amd64; KDE/4.6.5; x86_64; ; )
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Jay Levitt <jay.levitt@gmail.com>
References: 
 <CANSg5mqt+7j1SU2Gu2tZ_c_vdzCJPkymxEqaQ-OjRXfz=Pfimg@mail.gmail.com>
	<29918.1320244719@sss.pgh.pa.us>
	<CA+TgmoY_a34rAWCxBaA+3RtSv9tBo0PaRyxnNH4-rtJ5XkBQdA@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoY_a34rAWCxBaA+3RtSv9tBo0PaRyxnNH4-rtJ5XkBQdA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201111021813.06401.andres@anarazel.de>

On Wednesday 02 Nov 2011 16:13:09 Robert Haas wrote:
> On Wed, Nov 2, 2011 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Jay Levitt <jay.levitt@gmail.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