[jsmith45]

You already got a good response for postgres.

If you where speaking of MSSQL, it does treats non-recursive[1] CTE's as equivalent to to a view, and effectively inline it. So if things were faster with a temp table than a CTE it would mean that the query optimizer came up with a bad query plan. The most likely reason being that your query was complicated enough that you confused the cardinality estimator, so it ends up choosing really bad join types.

[1] Recursive queries in MSSQL are more complicated. They spool, but not necessarily all rows get spooled right away. So an infinite result recursive CTE does not necessarily fail. In any case predicate pushdown still occurs.