PostgreSQL含OR条件查询未选索引，哈希连接替代嵌套循环求优化

嘿，遇到OR条件导致PostgreSQL不走索引、用哈希连接+全表扫描的情况我碰过好多次了，给你几个实用的优化方向，应该能帮你把耗时压到100ms以内：

PostgreSQL的查询优化器对多列OR的支持一直不太友好，很容易放弃索引选择全表扫描。你可以把原查询拆成多个独立的子查询，用UNION ALL合并结果——只要你的数据不会因为OR条件出现重复计数（比如同一条记录同时满足两个OR分支），这种方法几乎肯定能让每个子查询都用上对应的索引。

举个例子，如果原查询是这样：

SELECT Count(_pcn.id) AS total_open_note 
FROM patientchartnote _pcn
WHERE _pcn.status = 'open' OR _pcn.assigned_to = 'current_user';

可以改成：

SELECT SUM(note_count) AS total_open_note
FROM (
    SELECT COUNT(_pcn.id) AS note_count 
    FROM patientchartnote _pcn 
    WHERE _pcn.status = 'open'
    
    UNION ALL
    
    SELECT COUNT(_pcn.id) AS note_count 
    FROM patientchartnote _pcn 
    WHERE _pcn.assigned_to = 'current_user'
) AS sub_queries;

如果担心重复计数（比如某条记录同时满足两个条件），就把UNION ALL换成UNION——不过UNION需要去重，性能会稍差一点，优先用UNION ALL。

根据你的OR条件类型，创建合适的索引：

• 如果OR是针对同一列的多个值（比如status = 'open' OR status = 'draft'），可以创建部分索引，只包含符合条件的行，索引体积更小、查询更快：

  CREATE INDEX idx_pcn_open_draft ON patientchartnote(id) WHERE status IN ('open', 'draft');
  

• 如果OR涉及不同列，给每个列单独创建单列索引，配合上面的UNION ALL拆分，每个子查询就能单独用上对应的索引。复合索引对OR条件的支持不好，不建议优先尝试。

有时候优化器选哈希连接，是因为它估算的返回行数太多，觉得走索引+嵌套循环不如全表扫描高效。你可以临时调整参数引导它选择索引：

• 临时关闭哈希连接，测试是否走索引：

  SET enable_hashjoin = off;
  

  执行EXPLAIN ANALYZE看看效果，如果有效，说明优化器的统计估算可能有问题——你可以针对OR涉及的列重新更新统计信息：

  ANALYZE patientchartnote (status, assigned_to); -- 替换成你的列名
  

• 如果你用的是SSD存储，可以把random_page_cost从默认的4降到1.1左右，让优化器更倾向于选择索引扫描（这个可以全局设置，但建议先测试）：

  SET random_page_cost = 1.1;
  

确保OR条件里的列没有被函数包裹（比如UPPER(_pcn.title) = 'OPEN'），也没有隐式类型转换（比如字符串和数字直接比较）——这些情况都会导致索引失效，优化器只能走全表扫描。如果必须用函数，就创建对应的函数索引。

如果上面的方法都不行，可以用索引提示强制优化器使用指定索引：

SELECT Count(_pcn.id) AS total_open_note 
FROM patientchartnote _pcn
WHERE _pcn.status = 'open' OR _pcn.assigned_to = 'current_user'
INDEX idx_pcn_status, idx_pcn_assigned_to; -- 替换成你的索引名

不过这种方法不推荐长期用，因为优化器比我们更懂数据分布，数据变化后强制索引可能反而变慢。

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