PostgreSQL重写查询OR子句优化性能 解决结果重复问题

问题背景 #

原查询执行缓慢核心原因：WHERE子句的OR逻辑包含两个针对products表的关联子查询，会对category表的每一行重复扫描products表两次。
查询匹配规则如下，满足所有前置条件+任意一个附加条件的记录需要返回：

• 前置条件：
  • category记录type_key=4、status='active'
  • 关联存在attribute_key=6、status='active'的products记录
• 附加条件（满足其一即可）：
  • 关联products中存在attribute_key=2、value in ('Active','active')、status='active'的记录
  • 关联products中不存在任何attribute_key=2、status='active'的记录

原始慢查询 #

-- original query
select Distinct on (p.value,c.key) c.key, c.type_key, c.status, c.created_by 
from category c
LEFT JOIN products p
on p.category_key=c.key
where c.type_key=4  
and p.status='active' and c.status='active'
and p.attribute_key=6
and ( 
   (EXISTS (SELECT * from products p1
                  WHERE p1.attribute_key=2 
                   AND p1.category_key=c.key 
                   AND ((value in ('Active', 'active')))
                   AND p1.status='active'
        ))
    OR(NOT EXISTS(SELECT * from products p1
                   WHERE p1.attribute_key=2 
                    AND p1.category_key=c.key 
                    AND p1.status='active') 
        )) 
order by p.value

已尝试改写的问题 #

之前尝试通过合并products表关联条件改写SQL，如下：

select Distinct on (p.value,c.key) c.key, c.type_key, c.status, c.created_by 
from category c
LEFT JOIN products p
on p.category_key=c.key
where c.type_key=4  
and p.attribute_key in (2,6) 
and p.status='active' and c.status='active'
and  (p.attribute_key !=2 -- 对应NOT EXISTS逻辑
   OR (value in ('Active', 'active'))) 
order by p.value

该写法返回重复结果的核心原因：行级关联会让同一个category下attribute_key=2和attribute_key=6的产品记录产生笛卡尔积，DISTINCT ON只能按指定字段去重，无法修正逻辑上的多对多匹配错误，也无法准确判断「整个category下不存在attr=2的active产品」这个聚合级条件。

优化后写法 #

通过单次扫描products表+条件聚合的方式，提前按category_key分组计算所有需要的判断标记，彻底消除关联子查询的重复扫描开销，逻辑100%等价原查询：

SELECT 
  c.key,
  c.type_key,
  c.status,
  c.created_by,
  p.attr6_value AS value
FROM category c
JOIN (
  SELECT
    category_key,
    -- 标记是否存在attr=6的active产品
    BOOL_OR(attribute_key = 6) AS has_attr6,
    -- 标记是否存在attr=2的active产品
    BOOL_OR(attribute_key = 2) AS has_attr2_active,
    -- 标记是否存在attr=2且值为Active/active的active产品
    BOOL_OR(attribute_key = 2 AND value IN ('Active', 'active')) AS has_attr2_active_val,
    -- 提取attr=6对应的value，匹配原查询返回和排序逻辑
    MAX(CASE WHEN attribute_key = 6 THEN value END) AS attr6_value
  FROM products
  WHERE status = 'active'
    AND attribute_key IN (2,6) -- 仅扫描需要的属性，减少数据处理量
  GROUP BY category_key
) p ON p.category_key = c.key
WHERE 
  c.type_key = 4
  AND c.status = 'active'
  AND p.has_attr6 = TRUE
  AND (p.has_attr2_active_val = TRUE OR p.has_attr2_active = FALSE)
-- 等价原DISTINCT ON排序逻辑，聚合后无重复行无需额外去重
ORDER BY p.attr6_value

性能增益说明 #

• 仅需扫描products表1次，完全消除原查询中关联子查询的N次重复扫描，数据量越大性能提升越明显
• 可配合索引进一步提速：
  • 给products表创建联合索引(status, attribute_key, category_key) INCLUDE (value)，可以直接走索引完成聚合计算，无需回表
  • 给category表创建联合索引(type_key, status, key) INCLUDE (type_key, created_by)，可以快速过滤符合条件的category记录

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