4 ms·

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, readab

• timonv 11y ago
  As far as I know generally it doesn't matter. But please proof me wrong, sounds important! Edit: It matters! See comment above :-)
• rosser 11y ago
  Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.
  • glogla 11y ago
    > 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 11y ago
      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.)