告别低效查询腾讯云 PostgreSQL Push Pred 助力性能飞跃谓词下推是数据库优化器老生常谈的优化特性为什么需要谓词下推其实核心就一个——让数据过滤“越早越好”减少后续计算的压力。每个数据库都有自己谓词下推的算法这里面最特殊的我觉得应该算是 PostgreSQL 的谓词下推因为我们经常在其他数据库的代码里看到谓词下推的动作但是 PostgreSQL 的谓词下推是没有具体下推动作的他是基于 join type/join 条件类型/等价传递闭包等能力先进行谓词拆解再把谓词组装到“正确”的位置实现下推。下面我们从功能的角度来看一下 PostgreSQL 这个三十多年的老数据库的谓词下推的发展历史最后也看一下在腾讯云增加的 push pred 能力的实现方法。常量谓词下推1999年当时有位叫 Bernard Frankpitt 的开发者发现了一个奇怪的问题同样是查询一张带索引的表用常量过滤条件Filter能走索引可把常量换成“常量函数”优化器就“瞎了”直接走全表扫描。举个例子表t1有个int4类型的字段a1建了索引t1_idx。执行SELECT*FROMt1WHEREt1.a15;优化器能精准用上索引;但执行SELECT*FROMt1WHEREt1.a1sqr(2);优化器却选择了全表扫描其中 sqr 是求平方的函数sqr(2)本质就是4。原因很简单当时 PostgreSQL 的优化器只能识别“索引列 op 常量”的格式却没意识到“常量作为参数的函数”本质也是常量。这就导致明明可以走索引的查询白白浪费了性能。为了解决这个问题Bernard 提交了一个补丁在优化器里加了个叫 eval_const_expr_mutator 的递归函数专门识别并计算“只有函数、运算符、常量组成的子表达式”把它们提前转换成常量。这个补丁最终落地也为后来的谓词下推打下了第一个基础——让优化器能“看透”更多隐藏的常量约束。块内谓词下推解决了常量函数的问题后优化器开始朝着“更智能的约束分发”进化这一阶段的核心是「restriction predicate限制谓词」的块内传播——也就是在同一个查询块Query Block里把单表约束、常量约束尽量往前推推到最底层的基础表上尽早过滤数据。2003年1月Tom Lane 提交的 de97072e3c8允许 merge 和 hash join 支持任意表达式只要不含 volatile 易变函数而不是只能基于“VarVar”的等式。这意味着优化器能识别更复杂的约束比如“a.x b.y and b.y 42”能自动推导出“a.x42”并把这个约束下推到表 a 上。紧接着还是 Tom Lane在2003年1月24日提交了 f5e83662d06修改了优化器的隐含等式推导逻辑。当一组等式里包含常量或外层查询的参数时会主动抑制冗余的“varvar”约束只保留“var常量”的约束。这样做既能减少执行时的计算量也能让约束更精准地推到基础表。这些修改再累加上1999年的常量函数优化慢慢形成了 PostgreSQL 的块内限制谓词常量谓词Filter下推能力——它是由常量折叠、约束归类、等价传递闭包等多个小优化共同拼凑起来的能力。视图/子查询里的谓词“推不下去”事情往往是余波未平一波又起。老的问题刚刚解决新的痛点又出现了当查询里有视图本质是子查询或者 UNION/INTERSECT 子查询时外层的 WHERE 条件没法下推到内层子查询里导致内层子查询要返回所有数据再由外层过滤性能极差。比如有人写了这样的查询SELECT*FROM(SELECTaFROMt1UNIONSELECTaFROMt2)WHEREa10;按道理应该把“a10”这个条件分别下推到 t1 和 t2 的查询里减少 UNION 的数据量但当时 PostgreSQL 做不到。2002年8月PostgreSQL 社区展开了讨论核心是“把外层谓词下推到 UNION/INTERSECT 子查询里到底合法吗”。Curt Sampson 提出视图作为“查询宏”优化器应该能像优化原生查询一样把外层条件下推而 Tom Lane 则谨慎地分析了不同场景的合法性——毕竟 SQL 的三值逻辑true/false/null很容易踩坑。最终Tom Lane 给出了结论UNION、INTERSECT 场景下谓词下推是合法的虽然会改变行为但仍符合 SQL 标准但 EXCEPT 场景不行因为下推可能导致结果不符合预期。基于这个结论他提交了 0201dac1c31 这个关键 commit实现了“将外层约束下推到 UNION 和 INTERSECT 子查询”这是 PostgreSQL 第一次实现跨查询块的谓词下推也是 pushpred 的合法性的重要铺垫。Parameterized Path 参数化路径数据库的优化永无止境解决了常量谓词的下推之后人们又把目光挪向了 join predicate。2002年10月Hans-Jürgen Schönig 发现一个查询里重复写了一次 join 条件既在 JOIN ON 里写了又在 WHERE 里写了反而比只写一次更快。原因就是重复的 join 条件被优化器当作 restriction 谓词下推到了内层扫描减少了数据量。Tom Lane 针对这个问题后续通过 04c8785c7b2 这个 commit重构了嵌套循环内层索引扫描的规划逻辑——让 join 条件能更顺畅地分发到内层路径避免了“重复写条件才能提速”的尴尬。再加上之前 de97072e3c8 支持复杂表达式的 joinPostgreSQL 在块内的 join 谓词优化已经很成熟了但它的边界很明确只能在同一个查询块里生效。这种实现的本质是让内层子路径由外层参数驱动执行——简单说就是外层每返回一行数据就把对应的参数传给内层内层用这个参数过滤数据再返回结果。看上去是一个非常常规的优化但是他让 Path 之间产生了依赖关系外层是驱动层内层是被驱动层只有 Nested loop Join 算子能够实现这种驱动和被驱动的关系所以在 Join 类型的选择上会受到一些限制。同时在 join ordering 的搜索、Join 合法性判断上需要考虑更多的特殊情况。LATERAL 成为基础设施2012年8月Tom Lane 提交了 5ebaaa49445 这个里程碑式的 commit实现了 SQL 标准的 LATERAL 子查询。LATERAL 的作用很简单——允许 FROM 子句里的子查询引用外层查询的列让跨块相关性从“隐含技巧”变成了“标准语法”。有了 LATERAL优化器就有了合法的语义入口可以名正言顺地处理“外层变量驱动内层子查询”的场景而 Parameterized Path 提供了执行层的支持块内 join 谓词分发提供了优化思路——到这里pushpred 的所有铺垫都已到位。举个例子假设存在两张表用户表 usersid int, name varchar和订单表 ordersid int, user_id int, amount numeric查询“每个用户的订单总金额”未支持 LATERAL 时需用子查询关联无法直接在子查询中引用外层 users.id支持 LATERAL 后可直接写SELECTu.name,o.total_amountFROMusers uLEFTJOINLATERAL(SELECTSUM(amount)AStotal_amountFROMordersWHEREuser_idu.id)oONtrue;这里子查询中的 user_id u.id就是直接引用外层 users 表的 id 字段实现了跨块相关性的合法引用也为后续 pushpred 下推 join 谓词提供了语义基础。push pred 登场腾讯云 PostgreSQL 实现了 Push Down Join Clause to Subquery 优化新增 GUC tencentdb_enable_push_pred它解决了最后一个核心问题把外层查询块的 join 谓词迁移到内层子查询块里让内层子查询在规划路径时就能用上 join 约束从而选择更优的执行计划。在 Push pred 登场之前DBA 如果想优化一个 SQL 的性能是可以通过改写 SQL 的方法用 Lateral 显式的来提升性能的而 pushpred就是通过优化器自动的实现这个谓词的下推。咱们用一个例子理解有一个查询外层是表 a内层是一个 LATERAL 子查询依赖a的列join 条件是 a.id 子查询.b.id。在 pushpred 出现之前join 条件只能挂在 join 节点上内层子查询规划时不知道 a.id 的值只能返回所有数据再由 join 节点过滤有了 pushpred 之后join 条件“a.id b.id”会被下推到内层子查询内层可以用这个条件走索引扫描大幅减少返回数据量。总结数据库性能的优化永无止境无论是常量谓词、还是 Join 谓词、亦或是各种动态 Filter本质上都是尽早的过滤数据我们上面举得各种例子看上去简单明了而优化器的难点在于要保证这种优化在复杂查询如果你经常见到那种几百行一句的 SQL里仍然是正确的每个优化都需要做细致的论证才行。push pred 能力不是一个“全新发明”而是站在前面所有优化的肩膀上进一步的优化是 PostgreSQL 这个“老”数据库几十年沉淀积累出来的结果。了解功能特性及使用方法请参见https://cloud.tencent.com/document/product/409/134538