PostgreSQL SQL查询优化求助：耗时12秒需优化方案

问题背景 #

现有PostgreSQL查询执行耗时约12秒，通过EXPLAIN ANALYZE分析发现采用Nested Loop连接算法，遍历总计145854行数据。已尝试创建索引且执行计划显示索引已被使用，但性能提升不明显，需针对性优化。

原查询语句 #

EXPLAIN ANALYZE 
SELECT 
       "feedback_option"."feedback_id" AS Col2,
       "feedback_option"."feedback_created_at" AS Col3,
       "feedback_option"."feedback_stage_id" AS Col4,
       "feedback_option"."option_id" AS Col5,
       "feedback_option"."other_text" AS Col6,
       "feedback_option"."duration" AS Col7,
       "feedback_option"."back_counter" AS Col8,
       "feedback_option"."is_spam" AS Col9,
       "feedback_option"."dx" AS Col10,
       "feedback_option"."dy" AS Col11,
       "feedback_option"."dz" AS Col12,
       "feedback_option"."created_at" AS Col13,
       "feedback_option"."ts" AS Col14,
       "feedback_option"."contact_name" AS Col15,
       "feedback_option"."contact_number" AS Col16,
       NULLIF(TRIM("feedback_option"."other_text"), '') AS "annotated_other_text",
       (feedback.created_at at time zone 'Asia/Karachi') AS "created_at_tz",
       EXTRACT('year' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_year",
       EXTRACT('isoyear' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_iso_year",
       EXTRACT('quarter' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_quarter",
       EXTRACT('month' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_month",
       EXTRACT('week' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_week",
       EXTRACT('dow' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') + 1 AS "created_at_tz_week_day",
       EXTRACT('day' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_day",
       EXTRACT('hour' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_hour",
       EXTRACT('minute' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_minute",
       EXTRACT('second' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_second",
       EXTRACT('dow' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') + 1 AS "week_day",
       ((feedback.created_at at time zone 'Asia/Karachi'))::time AS "created_time"
FROM "feedback_option"
INNER JOIN "question_option"
    ON ("feedback_option"."option_id" = "question_option"."id")
INNER JOIN "question"
    ON ("question_option"."question_id" = "question"."id")
INNER JOIN "feedback"
    ON ("feedback_option"."feedback_id" = "feedback"."id")
INNER JOIN "questionnaire"
    ON ("feedback"."questionnaire_id" = "questionnaire"."id")
INNER JOIN "questionnaires_questionnairerole"
    ON ("questionnaire"."id" = "questionnaires_questionnairerole"."questionnaire_id")
WHERE (NULLIF(TRIM("feedback_option"."other_text"), '') IS NOT NULL 
  AND "question"."kind" = 'COM' 
  AND "feedback"."processed" = true 
  AND "feedback"."division_id" IN (
      SELECT U0."id" 
      FROM "division" U0 
      WHERE U0."id" IN ( 
          WITH RECURSIVE division_descendents AS ( 
              SELECT id, parent_id 
              FROM division 
              WHERE id IN (2) 
              UNION SELECT child.id, child.parent_id 
              FROM division AS child 
              INNER JOIN division_descendents AS parent ON parent.id = child.parent_id 
              WHERE child.is_active = True 
          ) 
          SELECT id 
          FROM division_descendents 
      )
  ) 
  AND "feedback"."organization_id" = 2 
  AND "questionnaires_questionnairerole"."role_id" = 173 
  AND "feedback"."organization_id" = 2 
  AND "feedback"."questionnaire_id" = 183 
  AND "feedback"."group_id" = 1 
  AND "feedback"."processed" = true
)

查询计划信息 #

执行计划显示采用Nested Loop连接算法，遍历总计145854行数据，索引已被命中但性能未达预期。

优化建议 #

1. 清理WHERE子句冗余条件 #

原WHERE子句存在重复判断，删除后可减少解析与计算开销：

• 移除重复的feedback.processed = true
• 移除重复的feedback.organization_id = 2

2. 预计算递归部门查询结果 #

原查询中递归CTE每次执行都会重新计算部门列表，可提前缓存结果：

-- 临时表存储递归结果（若部门结构稳定，可改用物化视图定期刷新）
WITH RECURSIVE division_descendents AS (
    SELECT id, parent_id 
    FROM division 
    WHERE id IN (2) 
    UNION 
    SELECT child.id, child.parent_id 
    FROM division AS child 
    INNER JOIN division_descendents AS parent ON parent.id = child.parent_id 
    WHERE child.is_active = True 
)
SELECT id INTO temp_division_ids FROM division_descendents;

后续查询直接使用feedback.division_id IN (SELECT id FROM temp_division_ids)。

3. 复用时区计算结果 #

原SELECT中多次重复计算时区转换逻辑，通过CTE预计算可避免重复运算：

WITH feedback_with_tz AS (
    SELECT 
        f.*,
        (f.created_at AT TIME ZONE 'Asia/Karachi') AS created_at_tz,
        (f.created_at AT TIME ZONE 'Asia/Karachi') AT TIME ZONE 'UTC' AS created_at_utc
    FROM feedback f
    WHERE f.processed = true 
      AND f.organization_id = 2 
      AND f.questionnaire_id = 183 
      AND f.group_id = 1 
      AND f.division_id IN (SELECT id FROM temp_division_ids)
)
SELECT 
    fo.feedback_id AS Col2,
    fo.feedback_created_at AS Col3,
    -- 其他feedback_option字段省略...
    NULLIF(TRIM(fo.other_text), '') AS annotated_other_text,
    fwt.created_at_tz,
    EXTRACT('year' FROM fwt.created_at_utc) AS created_at_tz_year,
    EXTRACT('isoyear' FROM fwt.created_at_utc) AS created_at_tz_iso_year,
    -- 其他EXTRACT字段同理引用fwt.created_at_utc
    fwt.created_at_tz::time AS created_time
FROM feedback_option fo
INNER JOIN question_option qo ON fo.option_id = qo.id
INNER JOIN question q ON qo.question_id = q.id AND q.kind = 'COM'
INNER JOIN feedback_with_tz fwt ON fo.feedback_id = fwt.id
INNER JOIN questionnaire qn ON fwt.questionnaire_id = qn.id
WHERE NULLIF(TRIM(fo.other_text), '') IS NOT NULL
  AND EXISTS (
      SELECT 1 FROM questionnaires_questionnairerole qqr
      WHERE qqr.questionnaire_id = qn.id AND qqr.role_id = 173
  )

4. 优化索引与连接策略 #

索引优化

创建覆盖查询过滤与连接需求的复合索引，减少回表：

• feedback表：CREATE INDEX idx_feedback_filter ON feedback(organization_id, questionnaire_id, group_id, processed, division_id) INCLUDE(id, created_at);
• feedback_option表：CREATE INDEX idx_fo_other_text ON feedback_option(feedback_id, option_id) WHERE NULLIF(TRIM(other_text), '') IS NOT NULL;
• question表：CREATE INDEX idx_question_com ON question(id) WHERE kind = 'COM';
• questionnaires_questionnairerole表：CREATE INDEX idx_qqr_role ON questionnaires_questionnairerole(questionnaire_id, role_id);

连接算法调整

若Nested Loop开销过高，可临时禁用Nested Loop测试Hash Join或Merge Join的性能：

SET enable_nestloop = off;
-- 执行原查询
SET enable_nestloop = on; -- 恢复默认设置

5. 简化不必要的JOIN #

原查询中questionnaires_questionnairerole表仅用于过滤，改用EXISTS子查询可避免全表连接开销，尤其是当该表数据量较大时效果更明显（如上述CTE优化示例中的写法）。

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