Menu
I'm trying to diagnose a slow query, using EXPLAIN ANALYZE. I'm new to the command so I've read http://www.postgresql.org/docs/9.3/static/using-explain.html . 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.
728
Computer tomography enterography (CTE) is a type of computed tomography medical test that helps diagnose certain gastrointestinal diseases in the small intestine, sometimes referred to as the small bowel. CTE is a noninvasive imaging exam.
PostgreSQL conforms to the ANSI SQL-99 standard and implementing CTEs in PostgreSQL is similar to SQL Server. CTE is also known as WITH query. This type of query helps you to simplify long queries, it is similar to defining temporary tables that exist only for the running of the query.
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.
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
