如何让PostgreSQL分区中@>运算符识别int4range分区键？

问题根源 #

你当前基于upper(id)作为range分区键：归档分区存储upper(id)在[1,2147483646)区间的数据，活跃分区作为默认分区存储其余数据。但查询使用id @> 2147483647时，PostgreSQL无法自动将该条件转换为对分区键upper(id)的筛选逻辑，导致分区修剪失效，不得不扫描归档分区，拖慢查询性能。

解决方案 #

方案1：重写查询条件，显式关联分区键 #

直接在查询中补充对upper(id)的判断，让PostgreSQL识别到仅需扫描活跃分区：

SELECT * FROM test 
WHERE id @> 2147483647 
  AND (upper(id) > 2147483647 OR upper(id) IS NULL);

逻辑说明：
id @> 2147483647意味着该int4range包含int4最大值2147483647，只有当upper(id)大于该值（闭区间range场景）或为NULL（无穷开区间，如[6,)）时才满足条件，这正好匹配活跃分区的范围，PostgreSQL会自动修剪归档分区。

方案2：调整分区策略，改用int4range本身作为分区键 #

如果业务允许，将分区键从upper(id)改为id本身，直接按range类型的范围分区：

-- 重建主表
CREATE TABLE test (id int4range NOT NULL, value varchar(20)) 
PARTITION BY RANGE(id);

-- 归档分区：存储所有小于[2147483646,)的range
CREATE TABLE test_part_archive PARTITION OF test
FOR VALUES FROM ('[1,1)') TO ('[2147483646,)');

-- 活跃分区：存储其余所有range
CREATE TABLE test_part_active PARTITION OF test
DEFAULT;

逻辑说明：
当执行id @> 2147483647查询时，PostgreSQL可直接判断：只有id >= '[2147483646,)')的range才会包含2147483647，因此仅扫描活跃分区，无需额外条件。

方案3：升级PostgreSQL版本（可选） #

PostgreSQL 15及以上版本对range类型的分区修剪逻辑做了优化，能更好地处理@>运算符与range分区键的关联。若生产环境允许升级，升级后可能无需修改查询或分区策略即可自动实现分区修剪。

生产环境注意事项 #

• 针对20-5000万条数据的大表，调整分区策略时建议使用pg_repack等工具或分批次迁移数据，避免长时间锁表影响业务。
• 始终通过EXPLAIN ANALYZE验证分区修剪效果，确认执行计划中仅扫描目标分区。

内容的提问来源于stack exchange，提问作者Derek - Data Nexus