4 ms·
Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.
by 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 agoOf 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.)