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

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


>> 3. Add a new GUC which can enable and disable the optimization fence. This
>> is a very clumsy tool, but maybe good enough for some users and some people
>> here in this thread have complained about our similar GUCs.
> 
> Any GUC would be unable to distinguish one WITH clause from another.
> The hammer would then be guaranteed to be too big for precisely the
> cases where it's most needed.
> 

If I could, I'd give -1 million to a GUC-based approach, as that would 
make it entirely unusable in practice, I think.

Actually, I can give -1 million, so I'm giving it.
>>
>> 4. Add some new more generic query hinting facility. This is a lot
>> of work and something which would be very hard to get consensus for.
> 
> Just the design of the thing would be the work of months at a minimum,
> assuming we got to some consensus at all.  Maybe it's worth doing.
> 

While I came to conclusion that query hints may be quite useful in some 
situations, I'm pretty sure this is not a battle you'd like to fight.


regards

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