[Hakeashar]

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 :)

[rosser]

Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.

[glogla]

> Sometimes, that's even the behavior you want.

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

CTEs allow you to build "lisp-like" pipeline where you transform your data as you go and are able to give the intermediate results useful names.

[radiowave]

Of course, you can first write the query with CTEs, then convert to the nested form. It's just substitution.

(For bonus points, keep the original CTE form, but commented out, to help with troubleshooting further down the road.)

[timonv]

As far as I know generally it doesn't matter. But please proof me wrong, sounds important!

Edit: It matters! See comment above :-)