PostgreSQL存在多个JOIN LATERAL时的慢查询优化咨询

你当前查询慢的核心原因有两个：

• 8个独立的JOIN LATERAL相当于对每一条符合条件的subscriptions行，重复扫描quiz_answers、answers、tag_scores表8次，符合条件的subscriptions有2000行的话，相当于累计扫描这三张表16000次，开销被指数放大。
• 部分LATERAL子查询里的WHERE条件使用了qa.quiz_id = q.quiz_id OR ts.tag_id = xx的逻辑，该逻辑会直接失效关联条件的索引，导致子查询需要扫描全量的tag_scores数据，而不是仅扫描和当前quiz_id关联的行，这是耗时飙升的核心诱因。

优化方案1：合并LATERAL子查询（性能提升最明显，结果完全一致） #

将8次独立的聚合计算合并为1次LATERAL查询，用条件聚合一次性计算出所有需要的sum_score值，仅需扫描一次关联表即可拿到全部判断条件：

SELECT count(*)
FROM subscriptions q
JOIN LATERAL (
      SELECT
        SUM(CASE WHEN ts.tag_id = 21 THEN ts.score ELSE 0 END) AS sum_21,
        SUM(CASE WHEN qa.quiz_id = q.quiz_id OR ts.tag_id = 32 THEN ts.score ELSE 0 END) AS sum_32,
        SUM(CASE WHEN qa.quiz_id = q.quiz_id OR ts.tag_id = 35 THEN ts.score ELSE 0 END) AS sum_35,
        SUM(CASE WHEN qa.quiz_id = q.quiz_id OR ts.tag_id = 33 THEN ts.score ELSE 0 END) AS sum_33,
        SUM(CASE WHEN qa.quiz_id = q.quiz_id OR ts.tag_id = 29 THEN ts.score ELSE 0 END) AS sum_29,
        SUM(CASE WHEN qa.quiz_id = q.quiz_id OR ts.tag_id = 30 THEN ts.score ELSE 0 END) AS sum_30,
        SUM(CASE WHEN ts.tag_id = 46 THEN ts.score ELSE 0 END) AS sum_46,
        SUM(CASE WHEN ts.tag_id = 24 THEN ts.score ELSE 0 END) AS sum_24
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      -- 缩小扫描范围，不影响结果
      WHERE ts.tag_id IN (21,32,35,33,29,30,46,24)
    ) AS agg ON 
      agg.sum_21 <= 1
      AND agg.sum_32 <= 1
      AND agg.sum_35 <= 1
      AND agg.sum_33 <= 1
      AND agg.sum_29 <= 1
      AND agg.sum_30 <= 1
      AND agg.sum_46 >= 3
      AND agg.sum_24 >= 2
WHERE
  q.state = 'subscribed' AND q.app_id = 4;

提示：如果原始逻辑里的qa.quiz_id = q.quiz_id OR ts.tag_id = xx是笔误，实际需求是qa.quiz_id = q.quiz_id AND ts.tag_id = xx，将CASE里的OR改为AND即可，性能会进一步提升数个量级。

优化方案2：添加覆盖索引 #

配合上面的合并查询，添加以下联合索引即可实现索引覆盖，无需回表查询：

• quiz_answers表添加联合索引 (quiz_id, answer_id)
• tag_scores表添加联合索引 (answer_id, tag_id, score)

优化后查询耗时可以从130s降到毫秒级，和单LATERAL查询的性能基本一致。

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