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.
--
