优化含JSONB聚合与CTE的PostgreSQL查询性能

问题概述 #

当前需优化一条包含JSONB聚合和CTE的PostgreSQL查询，执行计划显示HashAggregate和Sort操作是性能瓶颈。已按platform字段做表分区（platform 'a'数据量远大于'b'），并创建了过滤索引，但仍无法消除notification_a.createdat上的Sort操作。调整work_mem至128MB后性能提升约2倍，但面对当前1000万行、未来数亿行的数据量，仍需更高效的实现方案。

表DDL #

CREATE TABLE notification
(
    id        bigint                   NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    body      jsonb                    NOT NULL,
    "type"    text                     NOT NULL,
    userid    int                      NOT NULL,
    isread    boolean                  NOT NULL DEFAULT FALSE,
    platform  text                     NOT NULL,
    "state"   text                     NOT NULL,
    createdat timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updatedat timestamp WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT notification_pkey PRIMARY KEY (id, platform),
    CONSTRAINT fk_notification_userid FOREIGN KEY (userid) REFERENCES "user" (id)
) PARTITION BY LIST (platform);

CREATE TABLE notification_a PARTITION OF notification FOR VALUES IN ('a');
CREATE TABLE notification_b PARTITION OF notification FOR VALUES IN ('b');
CREATE TABLE notification_default PARTITION OF notification DEFAULT;

当前查询语句 #

--EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE, FORMAT TEXT)
WITH grouped AS (
    SELECT CASE

               WHEN type = 'newContent' AND COUNT(*) FILTER (WHERE type = 'newContent') OVER () >= 3
                   THEN
                           JSONB_AGG(
                           JSONB_BUILD_OBJECT(
                                   'id', id,
                                   'body', body,
                                   'createdAt', createdat,
                                   'type', type
                               )
                       ) FILTER (WHERE type = 'newContent') OVER ()

               ELSE
                   JSONB_BUILD_OBJECT(
                           'id', id,
                           'body', body,
                           'createdAt', createdat,
                           'type', type
                       )
               END "notification"
    FROM notification_a
    WHERE userid = 23053
      AND NOT isread
      AND state = 'sent'
    ORDER BY createdat DESC
)
SELECT CASE

           WHEN JSONB_TYPEOF(notification) = 'array' AND notification #>> '{0,type}' = 'newContent' THEN
               JSONB_BUILD_OBJECT(
                       'body', JSONB_BUILD_OBJECT(
                       'contentCount', JSONB_ARRAY_LENGTH(notification),
                       'department', notification #>> '{0,body,department}'
                   ),
                       'createdAt', NOW(),
                       'type', 'newContentCompact',
                       'group', notification
                   )

           ELSE notification
           END,
       COUNT(*) OVER () total_count
FROM grouped
GROUP BY notification
ORDER BY (notification ->> 'createdAt')::timestamptz DESC
OFFSET 0 LIMIT 100;

已创建索引 #

CREATE INDEX IF NOT EXISTS notification_search_index ON notification (userid, isread, state, createdat DESC) WHERE (state = 'sent' and not isread);

核心需求 #

• 获取指定platform的分页通知数据
• 当type为newContent且数量≥3时，将其合并为分组结果（仅需type、body->'department'及分组数量）

示例 #

输入行：

id: 1, type: 'a'
id: 2, type: 'a'
id: 3, type: 'a'
id: 4, type: 'b'
id: 5, type: 'c'
id: 6, type: 'd'

执行SELECT ... OFFSET 0 LIMIT 3返回：

count: 3, type: 'a'
id: 4, type: 'b'
id: 5, type: 'c'

优化方案 #

1. 重构查询逻辑，消除冗余聚合与去重 #

原查询中窗口函数会为每一条newContent行生成重复的聚合数组，后续还需GROUP BY去重，这是HashAggregate的主要来源。改为先统计newContent的数量，再分支处理：

WITH new_content_stats AS (
    -- 先统计目标用户的newContent未读已发送数量
    SELECT COUNT(*) AS cnt
    FROM notification_a
    WHERE userid = 23053
      AND NOT isread
      AND state = 'sent'
      AND type = 'newContent'
),
raw_data AS (
    -- 获取原始数据，利用索引有序扫描避免Sort
    SELECT 
        id, body, createdat, type
    FROM notification_a
    WHERE userid = 23053
      AND NOT isread
      AND state = 'sent'
    ORDER BY createdat DESC
)
SELECT 
    CASE
        WHEN ncs.cnt >= 3 AND rd.type = 'newContent' THEN
            -- 仅生成一次聚合结果
            JSONB_BUILD_OBJECT(
                'body', JSONB_BUILD_OBJECT(
                    'contentCount', ncs.cnt,
                    'department', (SELECT body->>'department' FROM raw_data WHERE type='newContent' LIMIT 1)
                ),
                'createdAt', NOW(),
                'type', 'newContentCompact',
                'group', (SELECT JSONB_AGG(
                    JSONB_BUILD_OBJECT(
                        'id', id,
                        'body', body,
                        'createdAt', createdat,
                        'type', type
                    )
                ) FROM raw_data WHERE type='newContent')
            )
        ELSE
            JSONB_BUILD_OBJECT(
                'id', rd.id,
                'body', rd.body,
                'createdAt', rd.createdat,
                'type', rd.type
            )
        END AS notification,
    -- 计算合并后的总条数
    CASE
        WHEN ncs.cnt >=3 THEN (SELECT COUNT(*) FROM raw_data) - ncs.cnt + 1
        ELSE (SELECT COUNT(*) FROM raw_data)
    END AS total_count
FROM raw_data rd
CROSS JOIN new_content_stats ncs
-- 过滤重复的newContent行，仅保留第一条用于生成聚合结果
WHERE NOT (ncs.cnt >=3 AND rd.type = 'newContent' AND EXISTS (
    SELECT 1 FROM raw_data rd2 
    WHERE rd2.type='newContent' AND rd2.createdat > rd.createdat
))
ORDER BY 
    CASE
        WHEN ncs.cnt >=3 AND rd.type = 'newContent' THEN NOW()
        ELSE rd.createdat
    END DESC
OFFSET 0 LIMIT 100;

2. 优化分区索引，消除Sort操作 #

当前索引包含冗余列，针对notification_a分区创建更精简的索引，让数据库可以直接使用索引有序扫描：

CREATE INDEX IF NOT EXISTS notification_a_search_idx 
ON notification_a (userid, createdat DESC) 
WHERE (state = 'sent' AND NOT isread);

此索引去掉了isread和state列（WHERE条件已过滤），索引体积更小，且createdat DESC的顺序可以直接满足排序需求，避免Sort操作。

3. 将JSONB构建逻辑移至应用层 #

如果业务允许，建议仅从数据库获取原始数据（id, body, createdat, type）和newContent的统计数，在应用层完成合并与JSON结构生成。这样可以减少数据库的CPU开销，尤其是JSONB聚合和对象构建的性能消耗。

4. 改用键集分页替代OFFSET #

当分页偏移量较大时，OFFSET会导致数据库扫描大量不必要的行。建议使用键集分页，基于createdat和id（唯一键）实现高效分页：

-- 示例：上一页最后一条数据的createdat和id
SELECT ... 
FROM notification_a
WHERE userid = 23053
  AND NOT isread
  AND state = 'sent'
  AND (createdat < '上一页最后时间' OR (createdat = '上一页最后时间' AND id < '上一页最后id'))
ORDER BY createdat DESC, id DESC
LIMIT 100;

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