如何优化PostgreSQL中统计特定条件下最新行的查询？

表结构 #

(
    id         serial
        primary key,
    status_id  integer,
    created_at timestamp with time zone default now() not null,
    client_id  integer
);

需求 #

按created_at降序，统计每个client_id对应的、在最后一条status_id≠3的记录之后的所有行数。

示例数据 #

id, status_id, created_at, client_id
(1, 3, '2023-04-23 19:34:08.454000 +00:00', 2),
(2, 3, '2023-04-23 19:34:07.493000 +00:00', 2),
(3, 3, '2023-04-23 19:34:06.459000 +00:00', 2),
(4, 2, '2023-04-23 19:34:05.838000 +00:00', 2),
(5, 3, '2023-04-23 19:34:04.782000 +00:00', 2),
(6, 3, '2023-04-23 19:34:03.768000 +00:00', 2),
(7, 2, '2023-04-23 19:34:02.768000 +00:00', 2),
(8, 3, '2023-03-23 19:34:02.768000 +00:00', 1),
(9, 2, '2023-02-23 19:34:02.768000 +00:00', 1),
(10, 3, '2022-04-23 19:34:02.768000 +00:00', 1);

预期结果 #

client_id, count
    2,3
    1,1

当前查询（功能正常但速度慢） #

select count(*), i.client_id
from public.test i
where i.created_at > (select x.created_at
                      from public.test x
                      where x.status_id != 3
                        and x.client_id = i.client_id
                      order by x.created_at desc
                      limit 1)
group by i.client_id;

已创建的无效索引 #

create index test_status_id_client_id_created_at_index
on public.test (status_id, client_id, created_at);

查询执行计划 #

GroupAggregate  (cost=12.80..12.85 rows=3 width=12) (actual time=0.149..0.152 rows=2 loops=1)
  Group Key: i.client_id
  ->  Sort  (cost=12.80..12.81 rows=3 width=4) (actual time=0.144..0.145 rows=4 loops=1)
        Sort Key: i.client_id
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on test i  (cost=0.00..12.77 rows=3 width=4) (actual time=0.054..0.091 rows=4 loops=1)
              Filter: (created_at > (SubPlan 1))
              Rows Removed by Filter: 6
              SubPlan 1
                ->  Limit  (cost=1.16..1.16 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)
                      ->  Sort  (cost=1.16..1.16 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10)
                            Sort Key: x.created_at DESC
                            Sort Method: quicksort  Memory: 25kB
                            ->  Seq Scan on test x  (cost=0.00..1.15 rows=1 width=8) (actual time=0.001..0.002 rows=2 loops=10)
                                  Filter: ((status_id <> 3) AND (client_id = i.client_id))
                                  Rows Removed by Filter: 8
Planning Time: 0.826 ms
Execution Time: 0.314 ms

环境 #

PostgreSQL 14

问题 #

是否有优化该查询的方法，还是需要开发应用定时导出预处理数据？

优化方案 #

1. 调整索引结构 #

当前索引(status_id, client_id, created_at)不符合子查询的过滤逻辑，子查询是先按client_id分组，再筛选status_id≠3的最新记录。应创建以client_id为首列的复合索引，并按created_at降序排列，让PostgreSQL直接定位目标记录，避免排序：

CREATE INDEX test_client_id_status_id_created_at_idx
ON public.test (client_id, status_id, created_at DESC);

2. 重写查询，消除关联子查询 #

原查询的关联子查询会对主表每一行执行一次子查询（执行计划显示loops=10），数据量大时效率极低。可以用聚合查询先获取每个client的最后有效记录时间，再关联统计：

WITH client_last_valid AS (
    SELECT 
        client_id,
        MAX(CASE WHEN status_id != 3 THEN created_at END) AS last_valid_created_at
    FROM public.test
    GROUP BY client_id
)
SELECT 
    t.client_id,
    COUNT(*) AS count
FROM public.test t
JOIN client_last_valid clv ON t.client_id = clv.client_id
WHERE t.created_at > clv.last_valid_created_at
GROUP BY t.client_id;

如果需要更精准的逻辑（比如处理status_id≠3的记录不存在的情况），可以用窗口函数定位最新有效记录：

WITH ranked_records AS (
    SELECT 
        client_id,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY client_id 
            ORDER BY CASE WHEN status_id !=3 THEN created_at ELSE NULL END DESC
        ) AS rn
    FROM public.test
),
last_valid AS (
    SELECT client_id, created_at AS last_valid_created_at
    FROM ranked_records
    WHERE rn = 1
)
SELECT 
    t.client_id,
    COUNT(*) AS count
FROM public.test t
JOIN last_valid lv ON t.client_id = lv.client_id
WHERE t.created_at > lv.last_valid_created_at
GROUP BY t.client_id;

3. 预处理数据（高频查询场景） #

如果上述优化仍无法满足性能需求（比如表数据量极大且查询频率高），可以考虑定时预处理：

• 创建统计结果表client_post_valid_count，包含client_id和count字段
• 用PostgreSQL的pg_cron扩展或外部定时任务，定期执行优化后的查询更新统计表
• 业务直接查询统计表，避免全表扫描

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