3 ms·
It isn't clear to me why so few of these optimization articles talk about temporary tables. They are my go-to solution for many situations. Temporary tables ar
by ccleve 7y ago
It isn't clear to me why so few of these optimization articles talk about temporary tables. They are my go-to solution for many situations.
Temporary tables are great because they break the problem down into smaller pieces. They make the query much more understandable. They are way easier to debug. They are way less sensitive to failing because the query optimizer decides to do things differently because table statistics have changed. They will often eliminate the need for exotic syntax.
You also have the opportunity to add secondary indexes to these tables.
When you create a temporary table, you are essentially doing the work of the optimizer yourself. That's perfectly ok if you know things about the data that the optimizer doesn't or hasn't successfully figured out on its own.
They're not for every situation; they only work well when the intermediate tables are small and fit in memory. But that is very frequently the case.
- whitehouse3 7y agoOne reason you may not see CTE's or temp tables included in these articles is because their specifics are tightly coupled to the schema of your DB. It's hard to write a generic example in Stack Overflow that would work for someone else. But like you said: if you know the DB schema then temp tables make SQL easier to read, write, and reason about.
- taffer 7y agoI'm not sure if I like the idea of using CTEs as an optimization fence. It means using implementation quirks that can be changed in future releases.
- oarabbus_ 7y agoI'm not sure what you mean by this - it is possible to write any subquery as a CTE
- deleted 7y ago[deleted]
- Someone 7y agoMight refer to MySQL. It didn’t support any CTEs until version 8.0, from April, 2018.
- ccleve 7y agoHow are temp tables or CTEs more tightly tied to a schema than the original query? They both reference the same set of tables and columns, and both depend on indexes being in place. A schema change will break both, in equal measure.
- deleted 7y ago[deleted]
- ComodoHacker 7y ago>They are way less sensitive to failing because the query optimizer decides to do things differently because table statistics have changed. Optimizer doesn't have statistics on temp tables. It has to either gather it during query execution (which costs performance) or use some heuristical estimations (which AFAIK PostgreSQL does). These estimations could be wrong, and query plan becomes unstable. That's why temp tables aren't used often. Another reason is complexity on app side (several query executions instead of one, transaction management, etc.)