如何优化统计SOAP服务操作调用次数的慢COUNT/GROUP BY查询

我正在开发一个包含多操作的SOAP服务，请求与响应均存储在message表中，每条请求/响应对应表中一条记录，且同一对请求/响应的correlation_id相同。

我编写了如下SQL用于统计最近15分钟内各操作的调用次数：

SELECT operation,
       count(distinct (correlation_id)) as last_15_minutes
FROM message
WHERE creation_timestamp > (SELECT NOW() - INTERVAL '15 MINUTES')
GROUP BY operation ;

查询结果正确：

operation last_15_minutes      
--------- -------------------- 
5001      17                   
5005      15                   
5013      2                    
5021      7602                 
5201      4    

但当最近15分钟请求量较大时，查询耗时可超过30秒，需要优化。

补充信息 #

表结构 #

CREATE TABLE message
(
  id bigint GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 START 10000000 MINVALUE 1 MAXVALUE 9223372036854775807) PRIMARY KEY,
  correlation_id character varying(50) not null,
  operation character varying(4),
  variant character varying(4),
  status bigint,
  message character varying,
  creation_timestamp timestamp without time zone not null,
  version bigint not null
);

当前索引 #

CREATE INDEX message_creation_timestamp_idx ON message USING btree (creation_timestamp)
CREATE INDEX message_creation_timestamp_operation_variant_idx ON message USING btree (creation_timestamp, operation, variant)

执行计划（EXPLAIN (ANALYZE, VERBOSE, BUFFERS) 翻译结果） #

HashAggregate  (预估成本: 112345.67..112346.22, 行数: 55, 宽度: 20) (实际耗时: 32145.32..32145.35毫秒, 返回行数: 5, 循环次数: 1)
  输出字段: operation, count(DISTINCT correlation_id)
  分组键: message.operation
  缓冲区使用: 共享缓存命中12345次，读取磁盘67890次
  ->  基于message_creation_timestamp_idx索引的索引扫描，扫描对象为public.message表  (预估成本: 0.43..109876.54, 行数: 493826, 宽度:24) (实际耗时:0.05..28765.12毫秒, 返回行数:495123, 循环次数:1)
        输出字段: id, correlation_id, operation, variant, status, message, creation_timestamp, version
        索引条件: (message.creation_timestamp > (当前时间 - '00:15:00'::时间间隔))
        缓冲区使用: 共享缓存命中12345次，读取磁盘67890次
规划时间: 0.123毫秒
执行时间: 32145.41毫秒

优化思路 #

1. 创建覆盖索引，避免回表 #

当前索引未包含查询必需的correlation_id字段，导致索引扫描后需要回表读取主表数据，IO开销极大。创建包含所有查询字段的覆盖索引：

CREATE INDEX idx_message_creation_op_corrid ON message USING btree (creation_timestamp, operation, correlation_id);

数据库可直接从索引中获取operation和correlation_id，无需访问主表，大幅降低IO耗时。

2. 替换COUNT(DISTINCT)为预去重查询 #

COUNT(DISTINCT)在大数据量场景下计算效率低下，可先通过子查询完成correlation_id按operation的去重，再统计数量：

SELECT operation, COUNT(*) AS last_15_minutes
FROM (
    SELECT DISTINCT operation, correlation_id
    FROM message
    WHERE creation_timestamp > NOW() - INTERVAL '15 MINUTES'
) AS sub
GROUP BY operation;

这种方式减少了HashAggregate阶段的计算量，提升统计效率。

3. 时间分区表优化 #

若message表总数据量极大，可按creation_timestamp创建时间分区（如按小时/天分区），查询最近15分钟数据时仅需扫描对应分区，无需遍历全表索引。

4. 调整数据库参数与统计信息 #

• 执行计划显示大量磁盘读取，说明缓存不足，可根据服务器内存调大shared_buffers参数（建议设置为物理内存的25%-50%）。
• 执行ANALYZE message;更新表统计信息，让优化器生成更精准的执行计划。

5. 仅统计请求记录（可选） #

若status字段可区分请求/响应（如status=1代表请求），可只统计请求记录，直接减少一半数据扫描量：

SELECT operation, COUNT(DISTINCT correlation_id) AS last_15_minutes
FROM message
WHERE creation_timestamp > NOW() - INTERVAL '15 MINUTES'
  AND status = 1; -- 替换为实际请求对应的status值

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