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

On 04/05/17 05:33, Alvaro Herrera wrote:> David Fetter wrote:>>> When we add a "temporary" GUC, we're taking on a
giganticburden.>> Either we support it forever somehow, or we put it on a deprecation>> schedule immediately and expect
tobe answering questions about it for>> years after it's been removed.>>>> -1 for the GUC.>> Absolutely.>> So ISTM we
havethree choices:>> 1) we switch unmarked CTEs as inlineable by default in pg11. What seems> likely to happen for a
userthat upgrades to pg11 is that 5 out of 10> CTE-using queries are going to become faster than with pg10, and they>
aregoing to be happy; 4 out of five are going to see no difference, but> they didn't have to do anything about it; and
theremaining query is> going to become slower, either indistinguishably so (in which case they> don't care and they
remainhappy because of the other improvements) or> notably so, in which case they can easily figure where to add the>
MATERIALIZEDoption and regain the original performance.
 

+1 (Mario, proxied by me)

I've been asked to pass on comments by my colleague Mario of True Group
regarding the pain of the current behaviour of CTE's being optimisation
fences (as he doesn't normally follow this email group, I'll pass on any
feedback):
    "Greetings,
    Jumping onto the bandwagon here. At True Group in Auckland, we use    PostgreSQL as the platform for large-scale
softwaredevelopment, often    with extensive and complex schemas, views and queries.
 
    We frequently encounter poor query performance due to CTEs being an    optimisation barrier. We are forced to use
workaroundssuch as a    set-returning function (parameterised view) in place of a view, and    manually placing the
neededquals (WHERE clauses) into each CTE. This    creates headaches in large systems, where writing expressive SQL is
 essential, rather than employing workarounds that make code harder to    understand and reuse.
 
    Our general assumption is that we should write SQL that describes what    we want to achieve. The planner's job is
todetermine how to do that    efficiently.
 
    There is an argument that pushing quals into CTEs could reduce    performance for some queries, especially if the
qualis expensive. My    answer is twofold:
 
    a) most queries will either experience no change or benefit from qual    push-down, as usually the expensive part
isthe query subtree, not the    top-level qual.
 
    b) if a small proportion of queries are negatively affected, this is    better addressed by improving the planner's
costestimation. At worst,    an explicit OPTIMIZATION BARRIER hint could be added. But I know there    is much
philosophicalobjection in the PostgreSQL community to planner    hints. The irony is that a main argument for retaining
currentCTE    behaviour is that people rely on CTEs as implicit barrier hints!
 
    As an aside, we also encounter several other instances where qual    push-down fails, including where rewording a
queryin a way that is    semantically identical can change whether push-down takes place. But    probably the greatest
bugbearis inability to push-down a qual into 
 
more    than one subtree, meaning if a query has two or more subtrees, each of    which is expensive, but only one row
isneeded from each, determined by    one qual at the top level, one subtree will get the qual pushed into it    and run
fast,while the others will do a full (expensive) table 
 
scan and    be filtered only afterwards. There are also gains to be had in 
improving    qual push-down in sub-selects with aggregates, and others. But I think    these issues are only due to
lackof resource to implement, rather than    philosophical/political objections.
 
    We would like to see a Postgres version in the future that does much    better planning in the areas I've
mentioned,including but not limited    to the present issue of CTEs. Our organisation may be able to 
 
contribute    materially to this, if the political will is there, and others affected    pitch in to achieve common
goals.Not being expert in Postgres    internals, I am not certain how difficult each of the problems is.
 
    I hope we have contributed usefully to the conversation, and invite    feedback.

    Sincerely,    Mario"


Cheers,
Gavin