Postgres 12多列分区修剪对多值条件处理不智能问题咨询

问题原因 #

你遇到的是PostgreSQL 12版本多列RANGE分区的固有修剪逻辑限制：

• 多列RANGE分区的修剪严格遵循前缀匹配规则：只有排在前面的分区键全部指定单个精确等值时，后置分区键的过滤条件才会生效用于修剪。
• 针对第一个问题：当country指定单个等值'GB'，但sector用了IN多值条件，PostgreSQL 12不会对后置分区键的多值做拆分匹配，只会判定所有country为'GB'的分区都可能命中，因此会扫描全部3个GB相关分区。
• 针对第二个问题：当country用了IN多值条件时，PostgreSQL 12的修剪逻辑不会自动把查询拆分为country='GB' and sector='BETA' OR country='FR' and sector='BETA'分别做修剪，只会匹配country在'GB'到'FR'范围内的所有分区，忽略sector的过滤条件，因此会扫描GB、FR下的全部6个分区，而非预期的2个分区。

可选解决方案 #

按优先级从高到低排序：

1. 升级PostgreSQL版本
   PostgreSQL 14及以上版本对多列RANGE分区的修剪逻辑做了大量优化，你提到的两类多值查询场景都可以实现精确修剪：country = 'GB' and sector in ('BETA', 'GAMMA')只会扫描2个GB对应sector的分区，country in ('GB', 'FR') and sector = 'BETA'只会扫描2个对应国家的BETA分区，完全符合预期，无需修改业务代码和表结构，是成本最低的方案。

2. 修改分区策略为二级LIST分区
   如果暂时无法升级版本，可以把当前的多列RANGE分区改为分层LIST分区：一级分区按country做LIST分区，每个国家的一级分区再按sector做LIST二级分区。PostgreSQL 12对分层LIST分区的多值修剪支持远好于多列RANGE分区，你提到的跨国家查单行业场景可以正确修剪到每个国家下对应的sector分区。

3. 改写查询为UNION ALL拼接
   如果既不能升级也不想改分区结构，可以把多值查询拆分为多个等值条件的UNION ALL，比如查询20个国家的BETA行业数据可以写为：

SELECT * FROM part.partitioned_table WHERE country = 'GB' and sector = 'BETA'
UNION ALL
SELECT * FROM part.partitioned_table WHERE country = 'FR' and sector = 'BETA'
-- 其余国家依次拼接

PostgreSQL 12会对每个UNION ALL子句单独做分区修剪，每个子句仅扫描对应单个分区，总共只会扫描20个目标分区，符合性能要求。

4. 冗余创建以sector为首个分区键的表
   只有当你同时高频使用两类查询：按国家查多行业、按行业查多国家，且性能要求极高的情况下，才考虑新增冗余表。该方案需要额外维护两份数据的一致性，维护成本很高，非必要不选择。

内容的提问来源于stack exchange，提问作者zeroid