如何优化PostgreSQL中构建JSON数组的慢查询

我有一条用于获取并构建JSON数组的PostgreSQL查询，目前查询加载耗时过长——在PGAdmin中仅查询2条记录就需要约18秒。恳请各位提供优化该查询的帮助与指导，非常感谢。

查询语句 #

SELECT s.id, s.first_name, s.last_name, JSON_AGG( JSON_BUILD_OBJECT(
    'scoreKey', rs.score_key,
    'label', rs.label,
    'denominator', rs.denominator,
    'numerator', rs.numerator
) ) AS scores
FROM students s
INNER JOIN raw_scores rs ON s.id = rs.student_id
WHERE s.school_id = 1
GROUP BY s.id, s.first_name, s.middle_name, s.last_name
ORDER BY s.first_name, s.last_name ASC

查询结果 #

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id         | first_name | last_name | scores
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1          | Jane       | Doe       | [{"scoreKey": "classScore", "label": "Week 1", "denominator": 20.00, "numerator": 18.00}, {"scoreKey": "classScore", "label": "Week 2", "denominator": 20.00, "numerator": 19.00}, {"scoreKey": "classScore", "label": "Week 3", "denominator": 20.00, "numerator": 20.00}]
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2          | John       | Doe       | [{"scoreKey": "classScore", "label": "Week 1", "denominator": 20.00, "numerator": 18.00}, {"scoreKey": "classScore", "label": "Week 2", "denominator": 20.00, "numerator": 19.00}, {"scoreKey": "classScore", "label": "Week 3", "denominator": 20.00, "numerator": 20.00}]

表结构 #

raw_scores表 #

CREATE TABLE IF NOT EXISTS raw_scores
(
    id bigint NOT NULL DEFAULT nextval('raw_scores_id_seq'::regclass),
    student_id bigint,
    school_id bigint,
    numerator numeric(4,2),
    denominator numeric(4,2),
    score_key character varying(100) COLLATE pg_catalog."default",
    label character varying(100) COLLATE pg_catalog."default"
    -- 其他字段
);

students表 #

CREATE TABLE IF NOT EXISTS students
(
    id integer NOT NULL DEFAULT nextval('students_id_seq'::regclass),
    first_name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    middle_name character varying(100) COLLATE pg_catalog."default",
    last_name character varying(100) COLLATE pg_catalog."default" NOT NULL
    -- 其他字段
);

执行计划 #

优化建议 #

1. 添加针对性索引

   • 针对students表：创建联合索引覆盖过滤、关联、分组和排序所需字段，避免回表操作：

     CREATE INDEX idx_students_school_id ON students(school_id, id, first_name, last_name);
     

   • 针对raw_scores表：创建基于关联字段的覆盖索引，让数据库直接从索引获取聚合所需数据：

     CREATE INDEX idx_raw_scores_student_id ON raw_scores(student_id, score_key, label, denominator, numerator);
     

2. 简化分组逻辑
   由于students.id是主键，分组时只需按s.id分组即可，PostgreSQL会自动识别主键关联的其他字段，无需手动加入GROUP BY：

   SELECT s.id, s.first_name, s.last_name, JSON_AGG( JSON_BUILD_OBJECT(
       'scoreKey', rs.score_key,
       'label', rs.label,
       'denominator', rs.denominator,
       'numerator', rs.numerator
   ) ) AS scores
   FROM students s
   INNER JOIN raw_scores rs ON s.id = rs.student_id
   WHERE s.school_id = 1
   GROUP BY s.id
   ORDER BY s.first_name, s.last_name ASC
   

3. 提前过滤数据
   利用raw_scores表的school_id字段在JOIN时同步过滤，减少关联的数据量：

   SELECT s.id, s.first_name, s.last_name, JSON_AGG( JSON_BUILD_OBJECT(
       'scoreKey', rs.score_key,
       'label', rs.label,
       'denominator', rs.denominator,
       'numerator', rs.numerator
   ) ) AS scores
   FROM students s
   INNER JOIN raw_scores rs ON s.id = rs.student_id AND rs.school_id = 1
   WHERE s.school_id = 1
   GROUP BY s.id
   ORDER BY s.first_name, s.last_name ASC
   

4. 优化JSON构建效率
   若业务允许，使用jsonb_agg替代json_agg（JSONB聚合性能更优）；或用row_to_json简化对象构建：

   SELECT s.id, s.first_name, s.last_name, jsonb_agg(
       json_build_object('scoreKey', rs.score_key, 'label', rs.label, 'denominator', rs.denominator, 'numerator', rs.numerator)
   ) AS scores
   FROM students s
   INNER JOIN raw_scores rs ON s.id = rs.student_id AND rs.school_id = 1
   WHERE s.school_id = 1
   GROUP BY s.id
   ORDER BY s.first_name, s.last_name ASC
   

5. 更新表统计信息
   执行以下命令让查询优化器获取最新表数据分布，生成更优执行计划：

   ANALYZE students;
   ANALYZE raw_scores;
   

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