Re: CTE inlining

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(at)proxel(dot)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