聊一聊分区表的几个参数 前言 今天在翻看分区表文章的时候，重新审视了一下分区表的那几个参数，又多了一些思考 。 约束排除 首先是 constraint_exclusion ，顾名思义—约束排除，官方解释如下：Controls the query planner’s use of table constraints to optimize queries. The allowed values of are (examine constraints for all tables), (never examine constraints), and (examine constraints only for inheritance child tables and subqueries). is the default setting. It is often used with traditional inheritance trees to improve performance.Currently, constraint exclusion is enabled by default only for cases that are often used to implement table partitioning via inheritance trees. Turning it on for all tables imposes extra planning overhead that is quite noticeable on simple queries, and most often will yield no benefit for simple queries. If you have no tables that are partitioned using traditional inheritance, you might prefer to turn it off entirely. (Note that the equivalent feature for partitioned tables is controlled by a separate parameter, enable_partition_pruning.)控制查询优化器使用表约束以优化查询。 constraint_exclusion 的允许值是 on（检查所有表的约束）、off（从不检查约束）和 partition（仅检查继承子表和 UNION ALL 子查询的约束）。partition 是默认设置。它通常与传统的继承树一起使用以提高性能。目前，约束排除通常仅在用于通过继承树实现表分区的情况下默认启用。为所有表打开它会带来额外的计划开销，这在简单查询中非常明显，而且通常不会对简单查询产生任何好处。如果没有使用传统继承式分区表，可能更愿意完全关闭它。 （请注意，分区表的等效功能由单独的参数 enable_partition_pruning 控制。） 比如某个表上存在 id > 100 的约束，那么当执行查询 select id from test < 90 时，优化器对比约束条件，知道压根没有小于 90 的记录，于是直接跳过对于该表的扫描，返回 0 行记录（对应 FALSE）。因为当优化器使用约束排除时，需要花费更多的时间去对比约束条件和过滤条件，所以默认是 partition，仅针对继承式分区表，优化器就会对这些子表进行约束排除分析。 看个浅显的例子： 表上有个检查约束，限制 id 必须大于 100，但是查询需要检索 99 的数据，我们站在上帝视角，肯定知道没有满足的数据。但是优化器并不知晓，且由于没有合适的索引，于是只能选择最慢的顺序扫描，总共耗时 6 秒。 那让我们将 constraint_exclusion 参数设置为 on，再看下效果： One-Time Filter 的意思是：A qualification used by a Result operation. If it is false, an empty result set can be returned without further work. 如果为 False，则可以立马返回一个空结果集而无需进一步的工作。 所以单从执行时间上来看，高下立判！开启之后，仅耗时 0.024 毫秒，效率相差了几个数量级，但是 planning time 也肉眼可见上涨了好几倍，并且这是一个十分简单常见的点查，planning time 就上涨了这么多倍，对于更加复杂的查询，这个时间无疑会进一步增长，因此全局设为 on 十分不妥，影响面太大。正确姿势应该是根据特定场景，按需会话级设置该参数，可能会起到意想不到的优化效果！ 那让我们再看一个奇怪的生产案例： 表是一个继承式分区，分区裁剪依靠的也是 constraint_exclusion。使用继承式分区的正常逻辑是定义父表创建继承表，同时指定约束父表上创建触发器
但是这个案例中的子表十分奇怪，居然没有定义约束，完全靠触发器来限制数据插入到正确的子表中 这样即使指定了分区键，但是优化器也有心无力，都没有约束比较，比了个寂寞，于是每一个 SQL 都无法裁剪，需要扫描所有的分区，导致 CPU 飙升。还有就是老生常谈的函数三态了，不同于 enable_partition_pruning，约束排除要求必须是常量：
约束排除仅在查询的 WHERE 子句包含常量时才起作用。在查询中替换来自参数（在预备语句中）或存储过程的值之前，优化器解析查询。 因此即使是 current_date 这类的 stable 函数，也无法做到裁剪，这是和声明式分区的明显区别，比如如下两个分区表，分别是继承式和声明式，可以明显看到二者的差异： 当然解决办法也简单，新建一个 immutable 的 UDF，封装一下即可，但是是否会有幺蛾子就不得而知了。 剩下的两个参数就是 enable_partitionwise_join 和 enable_partitionwise_aggregate 了，wise—明智的，那么何为明智？让我们以 join 为例：Enables or disables the query planner’s use of partitionwise join, which allows a join between partitioned tables to be performed by joining the matching partitions. Partitionwise join currently applies only when the join conditions include all the partition keys, which must be of the same data type and have exactly matching sets of child partitions. Because partitionwise join planning can use significantly more CPU time and memory during planning, the default is .启用或禁用查询优化器器对分区智能连接的使用，它允许通过连接匹配的分区来执行分区表之间的连接。分区智能连接目前只适用于连接条件包括所有分区键的情况，这些分区键必须是相同的数据类型，并且有完全匹配的子分区集。因为分区智能连接在生成执行计划的过程中可能会使用更多的 CPU 时间和内存，所以默认是 off。 可以看到，限制一堆：必须是相同分区键，并且同样的类型和分区数量，才能做到智能连接，因此默认是 off，以减少不必要的规划时间。开启之后，一个大的连接会被分解成一堆小连接，从而提高了效率，不然需要全部扫描完之后再进行连接。Partitionwise join is restricted to the case where both sides have exactly same set of partitions
见一个文档中的样例：
可以看到提升了 50% 左右的性能，执行计划也显式先两两 join 之后，最后再进行汇总。但是前面也说了，只有特定有限的查询才能受益，所以默认是 off，因此最佳实践也是按需会话级调整。 小结 总而言之，constraint_exclusion 使优化器有了推理能力（其中之一），在某些场景下使用该参数可能会起到意想不到的效果！Give it a try！
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/34362.html
