On 30 Apr. 2017 07:56, "Ilya Shkuratov" <motr.i...@ya.ru> wrote:

Hello, dear hackers!

There is task in todo list about optional CTE optimization fence

I am not interested at this point in disabling mechanism
implementation, but I would like to discuss the optimization
mechanism, that should work when the fence is disabled.

It's looking at what other DBMSes do.

Notably MS SQL Server. AFAIK its CTEs are a lot like query-scoped views.
They are simply updatable where possible, so you can write

WITH x AS (...)

I do not know how MS SQL handles inlining and pullup/pushdown vs
materialization, handles multiple evaluation costs, etc.

This is the model I would want to aim for.

It seems, that we can replace CTE with subquery, so the optimizer
can do all available optimizations. This idea is quite
straightforward, but I could not find a discussion of it.
(Maybe it is so, because everyone knows that the idea is bad and it is
not worth to discuss. But I hope it is not, so I start this thread. =))

It's not bad for SELECT.

But there are complexities.

- CTE terms may contain data-mutating functions people are relying on not
multiply executing;

- we document that in postgres CTEs act as optimisation fences even with
the standard syntax. So users rely on this as a query hint. Personally I
want to relnotes this and tell people to use our OFFSET 0 hint instead, or
add a NOINLINE option to our CTEs, then make pg allow inlining by default.
This is a BC break, but not a big one if we restrict inlining of volatile.
And since we don't have query hints (*cough*) by project policy, we can't
really object to removing one can we?

- as you noted, it is hard to decide when it's worth inlining vs
materializing for CTE terms referenced more than once. We should possibly
start small and only inline single reference terms in the first release.
We'd continue to force materializing of multiple reference terms.

That'd at least help people who use CTEs to write clearer queries not
suffer for it. And it'd give us experience to help with conservatively
introducing multiple reference inlining.

Reply via email to