PostgreSQL中JSON聚合查询的性能优化方案咨询

问题背景 #

现有表结构：

CREATE TABLE IF NOT EXISTS users_searches
(
    uid                varchar      NOT NULL,
    search_query       json         NOT NULL,
    search_ts          timestamptz  NOT NULL
);

表中数据示例：

需求是将数据聚合到新表，包含uid、searches_4h（4小时内搜索记录）、searches_8h（8小时内）、searches_12h（12小时内）。原查询耗时约10分钟，以下是优化方案：

优化方案 #

1. 创建覆盖索引，消除回表查询 #

原查询需要过滤search_ts、分组uid，还需获取search_query，创建复合覆盖索引可让数据库直接从索引获取所有需要的数据，无需回表扫描原表，大幅降低IO开销：

CREATE INDEX idx_users_searches_ts_uid_query ON users_searches (search_ts DESC, uid, search_query);

索引按search_ts降序排列，完美匹配查询中search_ts > 时间阈值的过滤条件，同时包含uid和search_query，完全覆盖查询所需字段。

2. 减少重复聚合计算，一次聚合多次复用 #

原查询调用3次JSON_AGG，相当于对符合条件的数据做3次聚合扫描。可以先一次性聚合12小时内的所有数据（带时间标记），再通过条件筛选生成4h、8h的结果，仅做一次聚合：

WITH user_12h_searches AS (
    SELECT
        uid,
        JSON_AGG(
            JSON_BUILD_OBJECT(
                'query', search_query,
                'ts', search_ts
            )
        ) AS all_searches
    FROM users_searches
    WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR
    GROUP BY uid
)
SELECT
    uid,
    JSON_AGG(s.query) FILTER (WHERE s.ts > CURRENT_TIMESTAMP - INTERVAL '4' HOUR) AS searches_4h,
    JSON_AGG(s.query) FILTER (WHERE s.ts > CURRENT_TIMESTAMP - INTERVAL '8' HOUR) AS searches_8h,
    JSON_AGG(s.query) AS searches_12h
FROM user_12h_searches,
     JSON_TO_RECORDSET(all_searches) AS s(query json, ts timestamptz)
GROUP BY uid;

如果使用PostgreSQL 12+，也可以用JSONB_PATH_QUERY_ARRAY简化筛选逻辑：

WITH user_12h_searches AS (
    SELECT
        uid,
        JSONB_AGG(JSONB_BUILD_OBJECT('query', search_query, 'ts', search_ts)) AS all_searches
    FROM users_searches
    WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR
    GROUP BY uid
)
SELECT
    uid,
    JSONB_PATH_QUERY_ARRAY(all_searches, '$[*] ? (@.ts > $threshold).query')
        WITH PARAMS ('threshold' := CURRENT_TIMESTAMP - INTERVAL '4' HOUR) AS searches_4h,
    JSONB_PATH_QUERY_ARRAY(all_searches, '$[*] ? (@.ts > $threshold).query')
        WITH PARAMS ('threshold' := CURRENT_TIMESTAMP - INTERVAL '8' HOUR) AS searches_8h,
    JSONB_AGG(JSONB_EXTRACT_PATH_TEXT(all_searches, 'query')) AS searches_12h
FROM user_12h_searches
GROUP BY uid;

3. 改用物化视图（非实时场景） #

如果该聚合查询不需要实时计算，而是定期更新（比如每小时刷新一次），可以创建物化视图，定期刷新后直接查询物化视图，速度会比实时聚合快几个数量级：

-- 创建物化视图
CREATE MATERIALIZED VIEW user_search_aggregates AS
SELECT
    uid,
    JSON_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '4' HOUR) AS searches_4h,
    JSON_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '8' HOUR) AS searches_8h,
    JSON_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR) AS searches_12h
FROM users_searches
WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR
GROUP BY uid;

-- 创建物化视图索引（可选，加速查询）
CREATE INDEX idx_mv_user_searches_uid ON user_search_aggregates (uid);

-- 定期刷新物化视图（比如每小时执行一次）
REFRESH MATERIALIZED VIEW user_search_aggregates;

4. 替换JSON_AGG为JSONB_AGG #

如果业务允许，将JSON_AGG替换为JSONB_AGG，JSONB是PostgreSQL的二进制JSON格式，在聚合、筛选时的性能比原生JSON更好：

SELECT
    uid,
    JSONB_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '4' HOUR)  AS searches_4h,
    JSONB_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '8' HOUR)  AS searches_8h,
    JSONB_AGG(search_query) FILTER (WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR) AS searches_12h
FROM users_searches
WHERE search_ts > CURRENT_TIMESTAMP - INTERVAL '12' HOUR
GROUP BY uid;

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