From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
Date: Wed, 02 Nov 2011 12:39:36 -0700
Message-ID: <4EB19C78.8030903@emolecules.com> (raw)
In-Reply-To: <CAGTBQpahSrrddk1T9UPG6rkDWW6mzB_Hn2gJ=fyp1gbq1Mrt_w@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>
<CAGTBQpahSrrddk1T9UPG6rkDWW6mzB_Hn2gJ=fyp1gbq1Mrt_w@mail.gmail.com>
On 11/2/11 10:22 AM, Claudio Freire wrote:
> On Wed, Nov 2, 2011 at 12:13 PM, Robert Haas<robertmhaas@gmail.com> wrote:
>> 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).
> Well, in my case, I got performance thanks to CTEs *being*
> optimization fences, letting me fiddle with query execution.
>
> And I mean, going from half-hour queries to 1-minute queries.
Same here. It was a case where I asked this group and was told that putting an "offset 0" fence in was probably the only way to solve it (once again reminding us that Postgres actually does have hints ... they're just called other things).
> It is certainly desirable to maintain the possibility to use fences when needed.
Indeed. Optimizer problems are usually fixed in due course, but these "fences" are invaluable when you have a dead web site that has to be fixed right now.
Craig
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: craig_james@emolecules.com
Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?
In-Reply-To: <4EB19C78.8030903@emolecules.com>
* 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