PostgreSQL 多条件查询性能优化及表结构改进方案咨询

原查询性能差的核心原因有两个：

• 现有索引仅针对date单列，过滤完指定日期的记录后，需要全量回表逐一匹配reference_id和rent_id，回表开销极高
• 大量OR条件拼接的写法会导致PostgreSQL优化器难以生成最优执行计划，额外增加执行开销

方案1（优先推荐，无需改表结构，改造成本最低） #

操作步骤 #

1. 调整唯一约束的字段顺序：删除原有的uniq_reference_id_rent_id_date唯一约束，重建为字段顺序(date, reference_id, rent_id)的唯一约束，语法如下：

ALTER TABLE MyTable DROP CONSTRAINT uniq_reference_id_rent_id_date;
ALTER TABLE MyTable ADD CONSTRAINT uniq_date_reference_id_rent_id UNIQUE (date, reference_id, rent_id);

调整后该唯一索引的最左前缀为date，完全覆盖查询的过滤逻辑，无需额外创建其他索引，原有ix_MyTable_date单值索引也可以直接删除，节省存储空间。

2. 改写查询语句：将大量OR拼接的条件改写为元组IN匹配，语法更简洁，优化器处理效率更高：

SELECT * 
FROM MyTable
WHERE date = '目标日期'
  AND (reference_id, rent_id) IN ((ref1, rent1), (ref2, rent2), (ref3, rent3)...);

该方案执行时可以直接命中联合唯一索引，无需回表即可完成所有条件过滤，性能提升最明显。

方案2（改进同事提出的hash列方案，解决哈希碰撞风险） #

如果因为其他业务逻辑不能调整唯一约束顺序，可以选择hash列方案，优化后可以避免哈希碰撞问题，同时保证性能：

操作步骤 #

1. 新增计算生成的hash列，同时创建(date, ref_rent_hash)联合索引：

-- 新增存储生成的hash列，用concat拼接两个字段避免边界碰撞
ALTER TABLE MyTable ADD COLUMN ref_rent_hash INT GENERATED ALWAYS AS (hashtext(concat(reference_id::TEXT, '|', rent_id::TEXT))) STORED;
-- 创建联合索引
CREATE INDEX ix_mytable_date_refrenthash ON MyTable (date, ref_rent_hash);

2. 查询时同时使用hash过滤和原始字段匹配，既利用hash快速过滤，又避免哈希碰撞导致的结果错误：

SELECT * 
FROM MyTable
WHERE date = '目标日期'
  AND ref_rent_hash IN (hash_val1, hash_val2, hash_val3...)
  AND (reference_id, rent_id) IN ((ref1, rent1), (ref2, rent2), (ref3, rent3)...);

方案3（适用待匹配组合超过100个的场景） #

如果每次查询的(reference_id, rent_id)组合数量极多，建议用unnest将参数转成临时表关联查询，避免SQL语句过长，执行效率更稳定：

SELECT t.*
FROM MyTable t
JOIN unnest(
  ARRAY[ref1, ref2, ref3...]::INT[],
  ARRAY[rent1, rent2, rent3...]::INT[]
) AS params(ref_id, r_id)
  ON t.date = '目标日期'
  AND t.reference_id = params.ref_id
  AND t.rent_id = params.r_id;

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