3 ms·
CTEs are very nice, although their performance characteristics push me over to lateral joins and subqueries by default.
by sk5t 9y ago
CTEs are very nice, although their performance characteristics push me over to lateral joins and subqueries by default.
- Klathmon 9y agolateral joins were already in the list so I skipped them, but I agree. I tend to reach for CTEs first as they are more readable in my opinion, but when there are perf issues, lateral joins are the way to get out of them. They almost feel like cheating!
- garyclarke27 9y agoI agree Lateral Joins are amazing, Ive started using them instead of CTEs a lot recently. Set returning functions are automatically lateral joined and make the logic much easier to follow than sub queries. Performance is great because Postgres happily inlines most SQL functions - unlike my experience with SQL Server (several years ago may have changed) where UDFs where munch slower than sub queries.