标签
PostgreSQL , 分区 , 继承 , constraint_exclusion
背景
目前PG社区版本内置分区表的性能受制于一些BIND、LOCK所有子表等问题，当分区过多时，性能会受到极大的影响。
《分区表锁粒度差异 - pg_pathman VS native partition table》
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
不过还好pg_pathman这个插件弥补了以上问题。同时EDB发行的商用PG版本，在分区上面也有一些改进。
社区中，有一些committer关注到了这样的问题，同时提出了一些解法，已经将partition的选择代码进行了改造，采用的方法可能与EDB商用发型版本类似。
第一个相关patch如下
Faster partition pruning
Add a new module backend/partitioning/partprune.c, implementing a more
sophisticated algorithm for partition pruning. The new module uses each
partition's "boundinfo" for pruning instead of constraint exclusion,
based on an idea proposed by Robert Haas of a "pruning program": a list
of steps generated from the query quals which are run iteratively to
obtain a list of partitions that must be scanned in order to satisfy
those quals.
At present, this targets planner-time partition pruning, but there exist
further patches to apply partition pruning at execution time as well.
This commit also moves some definitions from include/catalog/partition.h
to a new file include/partitioning/partbounds.h, in an attempt to
rationalize partitioning related code.
Authors: Amit Langote, David Rowley, Dilip Kumar
Reviewers: Robert Haas, Kyotaro Horiguchi, Ashutosh Bapat, Jesper Pedersen.
Discussion: https://postgr.es/m/098b9c71-1915-1a2a-8d52-1a7a50ce79e8@lab.ntt.co.jp
这个patch还有一些连带的patch，未来社区版本的分区表应该不会再有bind, lock所有分区的问题了。
https://commitfest.postgresql.org/17/1330/
参考
《分区表锁粒度差异 - pg_pathman VS native partition table》
《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
