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.


--