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 disabling. 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 (...) UPDATE x SET ... 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.
