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

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

Whenever I give a talk on the query optimizer, I'm constantly telling
people to take logic out of functions and inline it, avoid CTEs, and
generally merge everything into one big query.  But as the OP says,
that is decidedly less than ideal from a code-beauty-and-maintenance
point of view: people WANT to be able to use syntactic sugar and still
get good performance.  Allowing for the insertion of optimization
fences is good and important but it needs to be user-controllable
behavior.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company