3 ms·
Except those won’t have any indexes on them, so any joins will be horrifically slow?
by zaroth 4y ago
Except those won’t have any indexes on them, so any joins will be horrifically slow?
- ttfkam 4y agoAre you putting indexes on your temporary tables? Are you including the time it takes to create them? I can't speak for others, but most of my CTEs tend to be small enough where an index wouldn't help much. Of course toggling MATERIALIZED is an easy way to see whether the equivalent of a temp table would work best or if the planner can successfully include the body of CTE in optimization. A lot harder syntactically to swap out a subquery for a temp table, which is what the MATERIALIZED keyword essentially does under the hood (at least in Postgres).
- TylerE 4y agoHell, on small tables Postgres often won’t do an index scan even when one exists because it’s faster to do a linear scan and not an index read + fetchs - or at least the planner thinks so.