Isn't a CTE in Postgres (unlike in MS SQL, AFAIR) also an optimization fence?
Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)
Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)
Sadly, in most cases, this mean you have to decide between ugly and performant, or nice and slow code.
I hope this gets fixed soon. Nobody should have to write queries like this:
select blah blah blah
from x, (select blah blah blah
from y, (select blah blah blah
from z, (select blah blah
from w
where a=b
and c=d)
where z.id = w.id
and p = 2
and q = 4)
where z.id = y.different_id
and r = 3
and t = 'BLAH'
and u not in (select u from w)) l
where l.id = x.id
(For bonus points, keep the original CTE form, but commented out, to help with troubleshooting further down the road.)
Edit: It matters! See comment above :-)