5 ms·
Another feature that should probably be on that list is Common Table Expressions: https://momjian.us/main/writings/pgsql/cte.pdf https://momjian.us/main/writing
by brasetvik 8y ago
Another feature that should probably be on that list is Common Table Expressions: https://momjian.us/main/writings/pgsql/cte.pdf https://momjian.us/main/writings/pgsql/cte.pdf
(I still frequently run into people who work a lot with SQL that don't use CTEs, though less than before :)
- dhd415 8y agoI think the article intended to highlight features specific to PostgreSQL. CTEs are undeniably great, especially recursive CTEs, but they're also part of the SQL:1999 standard and available in lots of databases.
- jboggan 8y agoYes but the fact that they are an optimization fence is a major drawback.
- paddy_m 8y agoNot always. I use them to force hash joins. This way you can filter table A on an index, and filter table B on an index, then join them with a hash join. In a straight join postgres would try to use the same index for all of the joins. At least that was my experience. Maybe I'm not understanding the docs properly or using the correct terminology. I know that the end result was 10x faster than any index I could come up with.
- cpburns2009 8y agoJust be aware that CTEs add optimizer barriers in PostgreSQL. So a query like: SELECT * FROM ( ... ) AS results may have a completely different query plan than a query like WITH results AS ( ... ) SELECT * FROM results This may or may not be desirable, and is important to be aware of.
- munk-a 8y agoIt is a balance always... but code maintainability isn't something to be dismissed. If utilizing CTEs greatly improves your readability it's probably worth utilizing CTEs until there is a performance need to switch off (and it's pretty trivial to de-CTE a query)