知识卡片
CTE(WITH子句)是优化围栏,且默认会被物化
通用表表达式(Common Table Expression,CTE,即WITH子句)允许把一段查询命名后像表一样使用,可以理解为只在当前SQL语句执行期间存在的临时表。它还支持编写递归查询——比如一张存储员工-经理关系的表,经理本身的ID也记录在同一张表的manager_id列里,要找出所有直接或间接的汇报链条(Scott报告给Smith、Smith报告给Adam,意味着Scott间接向Adam汇报),正是CTE递归查询的典型应用场景,这类查询天然适合提取层级数据、遍历图结构。但CTE有一个容易被忽视、却对性能影响很大的特性:PostgreSQL总是用WITH完成对一个查询的物化处理——也就是说,CTE内部的查询会被当作一个独立单元先完整执行、结果先物化出来,而不是被规划器揉进主查询里统一优化。这意味着WITH子句是一道”优化围栏”:写在CTE内部的过滤条件不会被自动下推(推到)到主查询里,反过来主查询里对CTE结果的过滤条件也不会被下推进CTE内部——这与很多人直觉里”CTE只是给子查询起了个名字,规划器该怎么优化还是怎么优化”的预期恰好相反。可迁移启发:把一段本该被下推过滤条件消灭掉大量数据的子查询写成CTE,性能可能反而比写成普通子查询或JOIN更差,因为这道”围栏”会强制CTE内部先把全部结果物化出来,再拿到外层做过滤——诊断”用了CTE反而变慢”的查询时,第一反应应该是检查过滤条件是否被这道围栏挡在了不该在的位置。
这张卡片看不懂?
这张卡片的反馈已满 10 条,暂时无法继续提交。
已有反馈 · 0 / 10 条
还没有人提交反馈。
原书出处
参考来源
- 位置:《数据架构师的PostgreSQL修炼:高效设计、开发与维护数据库应用》第7章《SQL调优》之"7.4.8 减少函数执行"(源文件:_epub-src/OEBPS/text00088.html)
- 结论依据:原文说明"PostgreSQL总是使用WITH完成对一个查询的物化处理。WITH字句是优化的围栏。我们在CTE中使用的过滤器不会被推到主查询,反之亦然", 直接支撑本卡关于CTE作为优化围栏及其物化特性的结论。
- 原始内容:这是一个非常有用的功能,以至于使用PostgreSQL的每个人都应该知道。