优化PostgreSQL物化视图查询，将执行时间降至50秒内

背景 #

我有一个PostgreSQL物化视图，已通过调整连接结构、移除过滤字段类型转换、将外层WHERE子句迁移至CTE等方式优化查询执行时间，现在需要进一步优化，将执行时间降至50秒以内。

原始物化视图代码 #

-- public.reporting_user_activity_contents source

CREATE MATERIALIZED VIEW public.reporting_user_activity_contents
TABLESPACE pg_default
AS WITH sub_q AS (
         SELECT u.id AS user_id,
            t.id AS tenant_id,
            t.global_tenant_id,
            m.id AS content_id,
            m.title AS content_title,
            um.id AS user_mission_id,
            mk.id AS content_kind_id,
            mk.kind AS content_kind,
            gp.path_id,
            gp.group_id AS assigned_through_group_id,
            COALESCE(ugpr.hidden, false) AS hidden,
                CASE
                    WHEN uv.created_at IS NOT NULL THEN uv.created_at
                    WHEN uv.created_at IS NULL THEN ugpr.viewed_at
                    ELSE NULL::timestamp without time zone
                END AS first_viewed_at,
                CASE
                    WHEN um.completed_at IS NOT NULL THEN um.completed_at
                    WHEN um.completed_at IS NULL THEN ugpr.completed_at
                    ELSE NULL::timestamp without time zone
                END AS first_completed_at,
                CASE
                    WHEN ugpr.discussed_at IS NOT NULL THEN ugpr.discussed_at
                    ELSE NULL::timestamp without time zone
                END AS first_discussed_at,
                CASE
                    WHEN gpr.discussable THEN gpr.discussable
                    ELSE false
                END AS discussible,
            ( SELECT to_jsonb(array_to_json(array_agg(row_to_json(t_1.*)))) AS to_jsonb
                   FROM ( SELECT tags.id,
                            tags.name,
                            tags.taggings_count
                           FROM tags
                             JOIN taggings ON tags.id = taggings.tag_id
                          WHERE taggings.taggable_id = m.id AND taggings.taggable_type::text = 'Mission'::text AND (taggings.context::text = ANY (ARRAY['tags'::character varying::text, 'skills'::character varying::text, 'skill_groups'::character varying::text, 'levels'::character varying::text]))) t_1) AS tags
           FROM missions m
             JOIN user_missions um ON um.mission_id = m.id AND um.assigned_through_type::text = 'Space'::text
             LEFT JOIN user_views uv ON uv.viewable_id = m.id AND uv.user_id = um.user_id AND uv.viewable_type::text = 'Mission'::text
             JOIN mission_kinds mk ON mk.id = m.mission_kind_id
             JOIN users u ON u.id = um.user_id
             JOIN tenants t ON u.tenant_id = t.id
             LEFT JOIN group_path_resources gpr ON gpr.id = uv.assigned_through_id AND uv.assigned_through_type::text = 'GroupPathResource'::text AND gpr.resourceable_type::text = 'Mission'::text AND gpr.resourceable_id = um.mission_id
             LEFT JOIN group_path_sections gps ON gps.id = gpr.group_path_section_id
             LEFT JOIN group_paths gp ON gp.id = gps.group_path_id
             LEFT JOIN user_group_path_resources ugpr ON ugpr.group_path_resource_id = gpr.id AND ugpr.resourceable_id = um.id AND ugpr.resourceable_type::text = 'UserMission'::text
        )
 SELECT sub_q.user_id,
    sub_q.tenant_id,
    sub_q.global_tenant_id,
    sub_q.content_id,
    sub_q.content_title,
    sub_q.user_mission_id,
    sub_q.content_kind_id,
    sub_q.content_kind,
    sub_q.path_id,
    sub_q.assigned_through_group_id,
    sub_q.discussible,
    sub_q.tags,
    min(sub_q.first_viewed_at) AS first_viewed_at,
    min(sub_q.first_completed_at) AS first_completed_at,
    min(sub_q.first_discussed_at) AS first_discussed_at,
    bool_and(sub_q.hidden) AS hidden
   FROM sub_q
  WHERE sub_q.tenant_id IS NOT NULL
  GROUP BY sub_q.user_id, sub_q.tenant_id, sub_q.global_tenant_id, sub_q.content_id, sub_q.content_title, sub_q.user_mission_id, sub_q.content_kind_id, sub_q.content_kind, sub_q.path_id, sub_q.assigned_through_group_id, sub_q.discussible, sub_q.tags
WITH DATA;

视图索引 #

CREATE INDEX idx_u_t_gt_c_atg ON public.reporting_user_activity_contents USING btree (content_id, user_mission_id, user_id, tenant_id, global_tenant_id, assigned_through_group_id, path_id);
CREATE INDEX ruac_atg_p ON public.reporting_user_activity_contents USING btree (assigned_through_group_id, path_id);
CREATE INDEX ruac_t_gt ON public.reporting_user_activity_contents USING btree (tenant_id, global_tenant_id);
CREATE INDEX ruac_u_atg ON public.reporting_user_activity_contents USING btree (user_id, assigned_through_group_id);

修改后的查询语句 #

explain analyze
WITH sub_q AS (
         SELECT u.id AS user_id,
            t.id AS tenant_id,
            t.global_tenant_id,
            m.id AS content_id,
            m.title AS content_title,
            um.id AS user_mission_id,
            mk.id AS content_kind_id,
            mk.kind AS content_kind,
            gp.path_id,
            gp.group_id AS assigned_through_group_id,
            COALESCE(ugpr.hidden, false) AS hidden,
            
                COALESCE(uv.created_at, ugpr.viewed_at) AS first_viewed_at,
             
                COALESCE(um.completed_at, ugpr.completed_at) AS first_completed_at,
               
                ugpr.discussed_at AS first_discussed_at,
                
                COALESCE(gpr.discussable, false) AS discussible,
            
           (
                SELECT jsonb_agg(jsonb_build_object('id', tags.id, 'name', tags.name, 'taggings_count', tags.taggings_count))
                FROM taggings
                JOIN tags ON tags.id = taggings.tag_id
                WHERE taggings.taggable_id = m.id
                AND taggings.taggable_type = 'Mission'
                AND taggings.context = ANY (ARRAY['tags', 'skills', 'skill_groups', 'levels'])
            ) AS tags       
            FROM tenants t
            JOIN users u ON u.tenant_id = t.id
            JOIN user_missions um ON um.user_id = u.id AND um.assigned_through_type = 'Space'
            JOIN missions m ON m.id = um.mission_id
            LEFT JOIN user_views uv ON uv.user_id = u.id AND uv.viewable_id = m.id AND uv.viewable_type = 'Mission'
            LEFT JOIN user_group_path_resources ugpr ON ugpr.resourceable_id = um.id AND ugpr.resourceable_type = 'UserMission'
            LEFT JOIN group_path_resources gpr ON gpr.id = uv.assigned_through_id AND uv.assigned_through_type = 'GroupPathResource' AND gpr.resourceable_type = 'Mission' AND gpr.resourceable_id = um.mission_id
            LEFT JOIN group_path_sections gps ON gps.id = gpr.group_path_section_id
            LEFT JOIN group_paths gp ON gp.id = gps.group_path_id
            LEFT JOIN mission_kinds mk ON mk.id = m.mission_kind_id
            WHERE t.id IS NOT NULL 

        )
 SELECT sub_q.user_id,
    sub_q.tenant_id,
    sub_q.global_tenant_id,
    sub_q.content_id,
    sub_q.content_title,
    sub_q.user_mission_id,
    sub_q.content_kind_id,
    sub_q.content_kind,
    sub_q.path_id,
    sub_q.assigned_through_group_id,
    sub_q.discussible,
    sub_q.tags,
    min(sub_q.first_viewed_at) AS first_viewed_at,
    min(sub_q.first_completed_at) AS first_completed_at,
    min(sub_q.first_discussed_at) AS first_discussed_at,
    bool_and(sub_q.hidden) AS hidden
   FROM sub_q
  GROUP BY sub_q.user_id, sub_q.tenant_id, sub_q.global_tenant_id, sub_q.content_id, sub_q.content_title, 
           sub_q.user_mission_id, sub_q.content_kind_id, sub_q.content_kind, sub_q.path_id, sub_q.assigned_through_group_id, sub_q.discussible, sub_q.tags;

执行计划分析 #

从执行计划来看，主要耗时点集中在：

• HashAggregate操作处理大量数据时内存不足，导致磁盘IO开销激增
• 标签聚合的关联子查询逐行执行，重复扫描taggings和tags表
• 部分JOIN操作未利用合适索引，触发全表扫描

优化方案 #

1. 重构标签聚合逻辑，避免关联子查询 #

将逐行执行的标签子查询改为预聚合JOIN方式，减少重复扫描：

WITH tag_agg AS (
    SELECT 
        taggings.taggable_id AS mission_id,
        jsonb_agg(jsonb_build_object('id', tags.id, 'name', tags.name, 'taggings_count', tags.taggings_count)) AS tags
    FROM taggings
    JOIN tags ON tags.id = taggings.tag_id
    WHERE taggings.taggable_type = 'Mission'
      AND taggings.context = ANY (ARRAY['tags', 'skills', 'skill_groups', 'levels'])
    GROUP BY taggings.taggable_id
),
sub_q AS (
    SELECT u.id AS user_id,
           t.id AS tenant_id,
           t.global_tenant_id,
           m.id AS content_id,
           m.title AS content_title,
           um.id AS user_mission_id,
           mk.id AS content_kind_id,
           mk.kind AS content_kind,
           gp.path_id,
           gp.group_id AS assigned_through_group_id,
           COALESCE(ugpr.hidden, false) AS hidden,
           COALESCE(uv.created_at, ugpr.viewed_at) AS first_viewed_at,
           COALESCE(um.completed_at, ugpr.completed_at) AS first_completed_at,
           ugpr.discussed_at AS first_discussed_at,
           COALESCE(gpr.discussable, false) AS discussible,
           tag_agg.tags
    FROM tenants t
    JOIN users u ON u.tenant_id = t.id
    JOIN user_missions um ON um.user_id = u.id AND um.assigned_through_type = 'Space'
    JOIN missions m ON m.id = um.mission_id
    LEFT JOIN tag_agg ON m.id = tag_agg.mission_id
    LEFT JOIN user_views uv ON uv.user_id = u.id AND uv.viewable_id = m.id AND uv.viewable_type = 'Mission'
    LEFT JOIN user_group_path_resources ugpr ON ugpr.resourceable_id = um.id AND ugpr.resourceable_type = 'UserMission'
    LEFT JOIN group_path_resources gpr ON gpr.id = uv.assigned_through_id AND uv.assigned_through_type = 'GroupPathResource' AND gpr.resourceable_type = 'Mission' AND gpr.resourceable_id = um.mission_id
    LEFT JOIN group_path_sections gps ON gps.id = gpr.group_path_section_id
    LEFT JOIN group_paths gp ON gp.id = gps.group_path_id
    LEFT JOIN mission_kinds mk ON mk.id = m.mission_kind_id
    WHERE t.id IS NOT NULL
)
SELECT sub_q.user_id,
       sub_q.tenant_id,
       sub_q.global_tenant_id,
       sub_q.content_id,
       sub_q.content_title,
       sub_q.user_mission_id,
       sub_q.content_kind_id,
       sub_q.content_kind,
       sub_q.path_id,
       sub_q.assigned_through_group_id,
       sub_q.discussible,
       sub_q.tags,
       min(sub_q.first_viewed_at) AS first_viewed_at,
       min(sub_q.first_completed_at) AS first_completed_at,
       min(sub_q.first_discussed_at) AS first_discussed_at,
       bool_and(sub_q.hidden) AS hidden
FROM sub_q
GROUP BY sub_q.user_id, sub_q.tenant_id, sub_q.global_tenant_id, sub_q.content_id, sub_q.content_title, 
         sub_q.user_mission_id, sub_q.content_kind_id, sub_q.content_kind, sub_q.path_id, sub_q.assigned_through_group_id, sub_q.discussible, sub_q.tags;

2. 添加针对性索引，加速JOIN与过滤 #

为核心关联表创建过滤条件+关联字段的复合索引：

-- 加速user_missions的过滤与连接
CREATE INDEX idx_user_missions_user_id_space ON user_missions(user_id) WHERE assigned_through_type = 'Space';
-- 加速users与tenants的连接
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
-- 加速missions与user_missions、mission_kinds的连接
CREATE INDEX idx_missions_id_kind ON missions(id, mission_kind_id);
-- 加速user_views的查找
CREATE INDEX idx_user_views_user_mission ON user_views(user_id, viewable_id) WHERE viewable_type = 'Mission';
-- 加速taggings的聚合查询
CREATE INDEX idx_taggings_mission_context ON taggings(taggable_id, taggable_type, context);

3. 优化聚合操作的内存配置 #

临时提高work_mem参数，让HashAggregate在内存中完成，避免磁盘排序：

-- 根据服务器内存情况调整数值，执行查询前临时设置
SET work_mem = '64MB';

4. 减少聚合前的数据量 #

• 检查连接逻辑，确保所有LEFT JOIN条件严谨，避免生成不必要的笛卡尔积
• 若业务允许，在CTE中提前过滤冗余记录（如添加时间范围过滤）

5. 更新表统计信息 #

让优化器生成更准确的执行计划：

ANALYZE user_missions, users, missions, taggings, tags, user_views, group_path_resources;

6. 物化视图增量刷新（可选） #

若物化视图不需要实时数据，改用增量刷新替代全量重建：

• 用触发器跟踪源表变化，仅同步新增/修改的数据
• 借助pg_cron定期执行增量同步，降低每次重建的开销

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