3 ms·
PostgreSQL materializes all CTEs so it has a big impact on performance. It's a hack that works around lack of optimizer hints in PostgreSQL to force the executi
by wolf550e 7y ago
PostgreSQL materializes all CTEs so it has a big impact on performance. It's a hack that works around lack of optimizer hints in PostgreSQL to force the execution plan you want. Only in PostgreSQL 12 did they change this: https://www.postgresql.org/docs/devel/release-12.html#id-1.11.6.5.5.3.4 https://www.postgresql.org/docs/devel/release-12.html#id-1.1...
- jteppinette 7y ago> https://dba.stackexchange.com/a/13117 https://dba.stackexchange.com/a/13117 Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view.
- wolf550e 7y agoInformation about SQL in general should not be confused with information about specific RDBMS. That SE answer is about MS SQL Server. What I wrote was specific to PostgreSQL which does a specific weird thing using CTEs where they are materialized. In SQL Server, CTEs are not materialized and are just used to organize the query, to make it more readable. If I wrote about PostgreSQL heap organized tables, would you object and say I'm wrong because MS SQL Server tables are index-organized, clustered on the primary key?
- hobs 7y agoGood answer, but fwiw SQL Server has heaps too :p