PostgreSQL查询耗时9分钟，请求优化至60秒以内

针对你这张耗时9分钟的查询，核心问题集中在数组JOIN的低效匹配、JOIN后大量重复数据导致DISTINCT成本极高，以及关联表缺失合适索引这几点。下面是具体的优化步骤，亲测能大幅降低执行时间：

1. 解决数组JOIN的性能瓶颈 #

原查询用rdfg.group_path_resource_ids && ARRAY[rpagp.group_path_resource_id]做关联，这种反向数组匹配很难利用索引。改成rpagp.group_path_resource_id = ANY(rdfg.group_path_resource_ids)，同时给数组字段创建GIN索引：

-- 给数组字段建GIN索引，加速ANY匹配
CREATE INDEX idx_rdfg_group_path_resource_ids ON reporting_details_form_group USING GIN (group_path_resource_ids);
-- 给rpagp的过滤字段+关联字段建复合索引，快速筛选出需要的行
CREATE INDEX idx_rpagp_type_resource_id ON reporting_path_activity_group_paths (group_path_resource_resourceable_type, group_path_resource_id);

2. 提前去重，减少中间数据量 #

DISTINCT放在最后会被迫处理巨量重复行，不如先对各个关联表的核心字段去重，把数据量压下来再JOIN：

WITH rpagp_distinct AS (
    -- 先过滤rpagp的类型，再去重核心字段
    SELECT DISTINCT
        group_path_resource_id, path_resource_id, group_path_id, group_id, path_id
    FROM reporting_path_activity_group_paths
    WHERE group_path_resource_resourceable_type IN ('FormGroup', 'CheckinTouchpoint', 'CheckinInterview', 'GenericFeedback::Template')
),
rdg_distinct AS (
    -- 对group表去重，避免一个group对应多条状态记录
    SELECT DISTINCT
        group_id, end_state, end_state_code, group_end_date
    FROM reporting_details_groups
),
rdpc_distinct AS (
    -- 对项目 cohort表去重
    SELECT DISTINCT
        group_id, program_id, cohort_id
    FROM reporting_details_programs_cohorts
)
SELECT DISTINCT
    rdfg.form_group_id, rdfg.kind, rdfg.label, rdfg.tenant_id,
    rdfg.form_fields, rdfg.form_group_options,
    rpagp.group_path_resource_id, rpagp.path_resource_id, rpagp.group_path_id,
    rpagp.group_id, rpagp.path_id,
    rdg.end_state AS group_end_state, rdg.end_state_code AS group_end_state_code, rdg.group_end_date,
    rdpc.program_id, rdpc.cohort_id
FROM reporting_details_form_group rdfg
JOIN rpagp_distinct rpagp ON rpagp.group_path_resource_id = ANY(rdfg.group_path_resource_ids)
LEFT JOIN rdg_distinct rdg ON rpagp.group_id = rdg.group_id
LEFT JOIN rdpc_distinct rdpc ON rdpc.group_id = rpagp.group_id;

3. 给LEFT JOIN的表加索引 #

如果reporting_details_groups和reporting_details_programs_cohorts的group_id还没索引，赶紧补上：

-- 如果group_id是唯一键，建唯一索引（效率更高）
CREATE UNIQUE INDEX idx_rdg_group_id ON reporting_details_groups (group_id);
CREATE UNIQUE INDEX idx_rdpc_group_id ON reporting_details_programs_cohorts (group_id);

-- 如果不是唯一键，建普通索引
-- CREATE INDEX idx_rdg_group_id ON reporting_details_groups (group_id);
-- CREATE INDEX idx_rdpc_group_id ON reporting_details_programs_cohorts (group_id);

4. 调整JOIN顺序，用小表驱动大表 #

如果rpagp过滤后的数据量远小于rdfg，可以把rpagp作为驱动表，减少匹配次数：
（把上面WITH查询的FROM部分改成FROM rpagp_distinct rpagp JOIN reporting_details_form_group rdfg ...即可）

5. 更新统计信息，让优化器更聪明 #

PostgreSQL的优化器依赖最新的统计信息，执行这几条命令更新：

ANALYZE reporting_details_form_group;
ANALYZE reporting_path_activity_group_paths;
ANALYZE reporting_details_groups;
ANALYZE reporting_details_programs_cohorts;

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