I'm trying to diagnose a slow query, using EXPLAIN ANALYZE. I'm new to the command so I've read . The query plan uses a "CTE scan", but I don't know what that is, compared to, say, a sequential scan - and more importantly, what a CTE scan means in general for query performance.
1 Answer
A "CTE scan" is a sequential scan of the materialized results of a CTE term (a named section like "blah" in a CTE like WITH blah AS (SELECT ...).
Materialized means that PostgreSQL has calculated the results and turned them into a temporary store of rows, it isn't just using the CTE like a view.
The main implication is that selecting a small subset from a CTE term and discarding the rest can do a lot of wasted work, because the parts you discard must still be fully calculated.
For details see a recent blog post I wrote on the topic.
2
