Re: [HACKERS] CTE inlining - Mailing list pgsql-hackers

On 5/2/17 11:23 PM, Merlin Moncure wrote:
> \On Tue, May 2, 2017 at 12:05 PM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com> wrote:
>> On 5/2/17 6:34 PM, David Fetter wrote:
>>>
>>> On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote:
>>>>
>>>> On 05/02/2017 04:38 AM, Craig Ringer wrote:
>>>>>
>>>>> On 1 May 2017 at 22:26, Andreas Karlsson <andreas@proxel.se> wrote:
>>
>>>>
>>>>
>>>> ...
>>>>
>>>> I see some alternatives, none of them perfect.
>>>>
>>>> 1. Just remove the optimization fence and let people add OFFSET 0 to
>>>> their
>>>> queries if they want an optimization fence. This lets us keep pretending
>>>> that we do not have query hints (and therefore do not have to formalize
>>>> any
>>>> syntax for them) while still allowing people to add optimization fences.
>>>
>>>
>>> +1
>>>
>>> I get that people with gigantic PostgreSQL installations with
>>> stringent performance requirements sometimes need to do odd things to
>>> squeeze out the last few percentage points of performance.  As the
>>> people (well, at least the people close to the ground) at these
>>> organizations are fully aware, performance optimizations are extremely
>>> volatile with respect to new versions of software, whether it's
>>> PostgreSQL, Oracle, the Linux kernel, or what have you.  They expect
>>> this, and they have processes in place to handle it.  If they don't,
>>> it's pilot error.
>>>
>>> We should not be penalizing all our other users to maintain the
>>> fiction that people can treat performance optimizations as a "fire and
>>> forget" matter.
>>>
>>
>> Agreed.
>>
>>>> 2. Add a decorator for WITH (e.g. "WITH MATERIALIZED x (...") to add an
>>>> explicit optimization fence. This will for the first time add official
>>>> support for a query hint in the syntax which is a quite big precedent.
>>>
>>>
>>> Yep.  It's one we should think very carefully before we introduce.
>>>
>>
>> I think it's a mistake to see this as an introduction of query hits.
>>
>> Firstly, it's a question whether it qualifies as a hint. I wouldn't call it
>> a hint, but let's assume there is a definition of query hints that includes
>> WITH MATERIALIZED.
>>
>> More importantly, however, this is not introducing anything new. It's just a
>> different name for the current "WITH" semantics, and you can achieve the
>> same behavior by "OFFSET 0". And people are already using these as hints, so
>> I fail to see how this introduces anything new.
>>
>> In fact, if you see the optimization fence as an implicit query hint, this
>> actually *removes* a hint (although most users are unaware of that behavior
>> and use it unintentionally).
> 
> +1 down the line.  More to the point, for several years now we've (or
> at least I, but I'm not the only one) have been advocating for the
> usage of CTE to avoid the undocumented and bizarre OFFSET 0 trick.
> Jerking this out from users without giving a simple mechanic to get
> the same behavior minus a major query rewrite is blatantly user
> hostile.  I can't believe we're even contemplating it.   Also a GUC is
> not a solution for pretty obvious reasons I think.
> 

I'm not sure what you mean by "jerking this out from users". Isn't most 
of this thread about how to allow CTE inlining without hurting users 
unnecessarily?

I think we agree that:

* Just removing the optimization fence and telling users to use OFFSET 0 
instead is a no-go, just like removing the fencing and not providing any 
sensible replacement.

* GUC is not the solution.

Which leaves us with either WITH INLINE or WITH MATERIALIZE, or 
something along those lines.

If we go with WITH INLINE then we're likely not solving anything, 
because most people will simply use WITH just like now, and will be 
subject to the fencing without realizing it.

Or we will choose WITH MATERIALIZE, and then the users aware of the 
fencing (and using the CTEs for that purpose) will have to modify the 
queries. But does adding MATERIALIZE quality as major query rewrite?

Perhaps combining this with a GUC would be a solution. I mean, a GUC 
specifying the default behavior, and then INLINE / MATERIALIZE for 
individual CTEs in a query?

If you have an application intentionally using CTEs as a fence, just do
    ALTER DATABASE x SET enable_guc_fencing = on

and you don't have to rewrite the queries.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services