Re: decompose big queries

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.

--