Re: CTE inlining

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 am not sure I like decorators since this means adding an ad hoc query hint
>> directly into the SQL syntax which is something which I requires serious
>> consideration.
>
> And mangling the semantics of existing syntax doesn't?
>
> That's what we do right now so we can pretend we don't have query
> hints while still having query hints.

I am in favor of removing the optimization fence from CTEs, and strongly
prefer no fence being the default behavior since SQL is a declarative
language and I think it is reasonable to assume that CTEs can be
inlined. But the question is how to best remove the fence while taking
into account that quite many use them as optimization fences today.

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.

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.

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.

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.

Andreas