Thanks for the options. can you expand on why CTEs are a postgres optimization fence?
as for 4: I am aware of the named subselect but once you add a few of those to the main select statement you start to get into unreadable territory.
back to CTEs, it seems like it should be an easy substitution problem for the postgres query planner. I am not a DB developer so I am probably not aware of all the issues. The CTE seems like the thing I was trying to describe though. Do you think that the optimizations would be more possible with begin and end scopes? It seems like they are part of the same select statement so i doubt scopes would help it.
In practice optimization fence is not normally a problem, and can even be an advantage. The general rule is - just use CTEs. And the specific rule is - learn to profile and optimize using EXPLAIN ANALYZE
as for 4: I am aware of the named subselect but once you add a few of those to the main select statement you start to get into unreadable territory.
back to CTEs, it seems like it should be an easy substitution problem for the postgres query planner. I am not a DB developer so I am probably not aware of all the issues. The CTE seems like the thing I was trying to describe though. Do you think that the optimizations would be more possible with begin and end scopes? It seems like they are part of the same select statement so i doubt scopes would help it.