Problem Statement
When would you prefer a temporary table over a CTE for performance?
Explanation
Use a temp table when you need to reuse an expensive intermediate result multiple times, add indexes to it, or break a huge plan into steps to help the optimizer. It can reduce re-computation and gives the engine cardinality facts after indexes are built.
A CTE is great for readability in a single statement, but many engines inline it. If the logic is heavy and reused, a temp table with the right index can be faster and more predictable.
Code Solution
SolutionRead Only
CREATE TEMP TABLE t AS SELECT * FROM big WHERE flag=1; CREATE INDEX ON t(user_id); SELECT ... FROM t JOIN users u ON u.id=t.user_id;
Practice Sets
This question appears in the following practice sets:
