Re: decompose big queries
От | Steven Hirsch |
---|---|
Тема | Re: decompose big queries |
Дата | |
Msg-id | alpine.DEB.2.20.1804061003410.4018@z87 обсуждение исходный текст |
Ответ на | Re: decompose big queries (pinker <pinker@onet.eu>) |
Список | pgsql-general |
On Fri, 6 Apr 2018, pinker wrote: > Edson Carlos Ericksson Richter wrote >> I don't know if there are best practices (each scenario requires its own >> solution), but for plain complex SELECT queries, I do use "WITH" >> queries... They work really well. > Be cautious with CTE's. They weren't meant to be an alternative to > subqueries and will probably change the way your query is executed, > because they are optimisation fences: > https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/ +1 Recently I had a poorly performing view speed up by a factor of 6x when converted from CTEs to to nested subqueries. In my case, the lack of predicate push-down was a real killer. Pg would labor away to produce an enormous intermediate result that was then selected down to a rather modest final result set. Showed up clearly in the access plan, however, and wasn't much of a problem to fix. --
В списке pgsql-general по дате отправления: