PostgreSQL中类时间序列计数器的高效查询方案问询

表结构与业务规则 #

表为仅追加模式，建表及索引语句如下：

CREATE TABLE IF NOT EXISTS data (
    id UUID DEFAULT gen_random_uuid () PRIMARY KEY,

    test_id UUID NOT NULL,
    user_id UUID NOT NULL,
    completed BOOL NOT NULL DEFAULT False,

    inserted_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() AT TIME ZONE 'UTC'),
);

CREATE INDEX some_idx ON data (user_id, test_id, inserted_at DESC);
CREATE INDEX some_idx2 ON data (test_id, inserted_at DESC);

业务规则：单个user_id针对同一test_id可有多条记录，但仅存在一条completed为True的记录（该记录同时是该用户的最后一条记录）。

查询需求 #

针对指定test_id，查询过去一周内每日的累计指标：

• total：inserted_at早于当日的唯一用户总数
• completed：inserted_at早于当日的已完成（completed为True）唯一用户总数

示例输出：

现有方案及性能问题 #

现有查询通过LATERAL JOIN遍历每日日期并单独统计，但每次迭代都会重复扫描表，数据量增长后性能会显著下降：

SELECT date, entered, completed
FROM (
     SELECT d::date AS date
     FROM generate_series('2023-01-12', now(),INTERVAL '1 day') AS d
) AS dates
cross join lateral (
    SELECT COUNT(DISTINCT user_id) AS entered,
           COUNT(1) FILTER (WHERE completed) AS completed -- 无需distinct，每个用户仅一条完成记录
    FROM data
    WHERE
        test_id = 'someId' AND
        inserted_at < dates.date
) AS vals

对应的查询计划：

+---------------------------------------------------------------------------------------------------------------------------->
| QUERY PLAN                                                                                                                 >
|---------------------------------------------------------------------------------------------------------------------------->
| Nested Loop  (cost=185.18..185218.25 rows=1000 width=28) (actual time=0.928..7.687 rows=8 loops=1)                         >
|   ->  Function Scan on generate_series d  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.009..0.012 rows=8 loops=1)   >
|   ->  Aggregate  (cost=185.17..185.18 rows=1 width=16) (actual time=0.957..0.957 rows=1 loops=8)                           >
|         ->  Bitmap Heap Scan on data  (cost=12.01..183.36 rows=363 width=38) (actual time=0.074..0.197 rows=779 loops>
|               Recheck Cond: ((test_id = 'someId'::uuid) AND (inserted_at < (d.d)::date))  >
|               Heap Blocks: exact=629                                                                                       >
|               ->  Bitmap Index Scan on some_idx2  (cost=0.00..11.92 rows=363 width=0) (actual time=>
|                     Index Cond: ((test_id = 'someId'::uuid) AND (inserted_at < (d.d)::date>
| Planning Time: 0.261 ms                                                                                                    >
| Execution Time: 7.733 ms                                                                                                   >
+---------------------------------------------------------------------------------------------------------------------------->

优化方案 #

1. 预计算用户关键时间点（推荐） #

先一次性聚合出每个用户的首次插入时间和完成时间，再与日期序列关联计算累计值，避免多次扫描表：

WITH user_timestamps AS (
    SELECT
        user_id,
        MIN(inserted_at) AS first_inserted,
        MAX(CASE WHEN completed THEN inserted_at END) AS completed_at
    FROM data
    WHERE test_id = 'someId'
    GROUP BY user_id
),
date_series AS (
    SELECT d::date AS date
    FROM generate_series(now() - INTERVAL '7 days', now(), INTERVAL '1 day') AS d
)
SELECT
    ds.date,
    -- 累计首次插入早于当日的用户数
    SUM(CASE WHEN ut.first_inserted < ds.date THEN 1 ELSE 0 END) OVER (ORDER BY ds.date) AS total,
    -- 累计完成时间早于当日的用户数
    SUM(CASE WHEN ut.completed_at IS NOT NULL AND ut.completed_at < ds.date THEN 1 ELSE 0 END) OVER (ORDER BY ds.date) AS completed
FROM date_series ds
CROSS JOIN user_timestamps ut
GROUP BY ds.date
ORDER BY ds.date DESC;

优势：仅扫描一次表，聚合逻辑集中，后续仅需对用户维度的小数据集做关联计算，数据量越大，性能提升越明显。

2. 新增覆盖索引提升聚合效率 #

为支持上述聚合查询，新增覆盖索引，避免数据库回表读取堆数据：

CREATE INDEX idx_test_user_ts ON data (test_id, user_id) INCLUDE (inserted_at, completed);

这个索引包含了聚合所需的所有字段，数据库可直接从索引完成MIN(inserted_at)和MAX(CASE...)的计算，无需访问表的实际数据块。

3. 基于每日增量的累计方案（超大数据量场景） #

如果表数据量极大，可先统计每日新增用户和新增完成用户，再通过窗口函数做累计求和：

WITH daily_stats AS (
    SELECT
        DATE_TRUNC('day', inserted_at)::date AS date,
        -- 当日新增的首次访问用户
        COUNT(DISTINCT user_id) FILTER (WHERE inserted_at = MIN(inserted_at) OVER (PARTITION BY user_id)) AS new_total,
        -- 当日新增的完成用户
        COUNT(DISTINCT user_id) FILTER (WHERE completed) AS new_completed
    FROM data
    WHERE test_id = 'someId' AND inserted_at >= now() - INTERVAL '8 days' -- 多取一天确保覆盖一周累计起点
    GROUP BY DATE_TRUNC('day', inserted_at)::date
),
date_series AS (
    SELECT d::date AS date
    FROM generate_series(now() - INTERVAL '7 days', now(), INTERVAL '1 day') AS d
)
SELECT
    ds.date,
    SUM(ds_new.new_total) OVER (ORDER BY ds.date) AS total,
    SUM(ds_new.new_completed) OVER (ORDER BY ds.date) AS completed
FROM date_series ds
LEFT JOIN daily_stats ds_new ON ds.date = ds_new.date
ORDER BY ds.date DESC;

优势：仅扫描最近8天的数据，过滤范围更小，适合数据量超大规模的场景。

验证优化效果 #

优化后的查询计划会显示仅执行一次表/索引扫描，无多次迭代扫描的操作。可通过EXPLAIN ANALYZE查看执行计划，确认索引是否被有效利用，以及整体执行时间的变化。

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