4 ms·
Are 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
by ttfkam 4y ago
Are 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.