4 ms·
I don't know about postgres but joining CTEs in Sql Server is a recipe for blowing up your tempdb or at the very least waiting for ages for a trivial query to f
by poolpool 13y ago
I don't know about postgres but joining CTEs in Sql Server is a recipe for blowing up your tempdb or at the very least waiting for ages for a trivial query to finish.
- endianswap 13y agoYou must be doing something wrong then. MSSQL resolves the entire statement (CTEs plus final query) into a single query that it optimizes as a whole. Can you share an example of two queries that are equivalent where one is written using CTEs and it doesn't construct the same plan?
- bbatchelder 13y agoI have not seen this behavior, and I use the fuck out of CTEs in SQL Server.
- pilif 13y agoPostgres has no problems joining against any sized CTEs - at least that's what I'm seeing here with a considerably sized dataset (600 GB raw table size). But: The optimizer will first fully resolve the CTE expression and only then join. If the join condition eliminates many of the rows selected in the CTE, this might indeed yield a much worse execution time than doing it inline. This behaviour is documented on http://www.postgresql.org/docs/9.3/static/queries-with.html http://www.postgresql.org/docs/9.3/static/queries-with.html (at the end of 7.8.1)
- taspeotis 13y agoSounds like you should look at the execution plan of the "trivial query" and see what's up with your database schema. SQL Server's plan generation is not always perfect, but it's good 98 times out of 100.