From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jay Levitt <jay.levitt@gmail.com>
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Date: Wed, 2 Nov 2011 18:13:06 +0100
Message-ID: <201111021813.06401.andres@anarazel.de> (raw)
In-Reply-To: <CA+TgmoY_a34rAWCxBaA+3RtSv9tBo0PaRyxnNH4-rtJ5XkBQdA@mail.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andres@anarazel.de, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, jay.levitt@gmail.com
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
In-Reply-To: <201111021813.06401.andres@anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox