postgresql 什么是 CTE 扫描，它对性能有何影响？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26852535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is a CTE scan, and what are its implications for performance?
提问by bblack
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.
我正在尝试使用EXPLAIN ANALYZE. 我是这个命令的新手，所以我已经阅读了http://www.postgresql.org/docs/9.3/static/using-explain.html。查询计划使用“CTE 扫描”，但与顺序扫描相比，我不知道那是什么——更重要的是，CTE 扫描通常对查询性能意味着什么。
回答by Craig Ringer
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 ...).
“CTE 扫描”是对 CTE 术语（如WITH blah AS (SELECT ...).
Materializedmeans 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.
物化意味着 PostgreSQL 已经计算了结果并将它们变成了行的临时存储，它不仅仅是像视图一样使用 CTE。
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.
主要的含义是从 CTE 项中选择一个小的子集并丢弃其余的会做很多浪费的工作，因为你丢弃的部分仍然必须完全计算。
For details see a recent blog post I wrote on the topic.
有关详细信息，请参阅我最近撰写的有关该主题的博客文章。
