3 ms·
> https://www.postgresql.org/docs/11/queries-with.html https://www.postgresql.org/docs/11/queries-with.html The optional RECURSIVE modifier changes WITH from a
by jteppinette 7y ago
> https://www.postgresql.org/docs/11/queries-with.html https://www.postgresql.org/docs/11/queries-with.html
The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL.
Am I incorrect in thinking that "standard" CTEs don't have an impact on performance. When I saw this in the article and how it had no performance improvements it reaffirmed my assumption.
- wolf550e 7y agoPostgreSQL 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
- AaronFriel 7y agoYes, but I think it's understandable given that nothing in the spec suggests CTEs should affect performance. Practically speaking, though, CTEs can be used to organize code, and query plan generators/optimizers may take that into account. Or, as someone else added, PostgreSQL will materialize the CTE as if it were a temporary table being used as an input. Almost all RDBMS advice is implementation specific, sadly.
- oarabbus_ 7y agoThey have an almost-negligible performance decrease in my experience. I worked with a client who was transitioning from their MySQL DB to a (mysql-flavored) AWS Aurora DB, as they required high concurrency. In order to handle the throughput their requirement was for the queries to run in under 1 second. After getting down to ~1.1-1.3s on average, I had to figure out how to squeeze the last bits of optimization I could get. One thing I did was rewrite all the CTEs to subqueries, and if I recall correctly, it shaved something like a few percent off the query time (~.01-.05s). I'd call it negligible as the speed increase certainly isn't worth the decrease in readability and maintainability of the query.