PostgreSQL存储过程性能优化：千万级数据下游标改进问询

阿华AIGC实验室

2026-5-14

针对大数量下游标存储过程的性能优化方案

首先得明确：游标逐行处理是你当前性能瓶颈的核心原因，PostgreSQL作为关系型数据库，对集合式操作的优化能力远胜于逐行循环，尤其是面对百万到亿级数据时，这个差距会被无限放大。下面是具体的优化思路和可落地的方案：

1. 彻底替换游标：用窗口函数+集合操作实现业务逻辑

你的核心业务是合并systemuid+filename分组下，时间连续（后一条的mindatetime与前一条的maxdatetime差1毫秒）的记录，并用合并后的记录替换原记录，同时归档原记录到审计表。这个逻辑完全可以用窗口函数+CTE（公共表达式）实现，不需要任何游标。

示例代码：

WITH grouped_records AS (
    SELECT 
        systemuid,
        filename,
        mindatetime,
        maxdatetime,
        -- 标记连续记录组：当前记录与上一条不连续时，组ID+1
        SUM(CASE 
            WHEN EXTRACT(EPOCH FROM mindatetime) * 1000 
                 - EXTRACT(EPOCH FROM LAG(maxdatetime) OVER (PARTITION BY systemuid, filename ORDER BY mindatetime)) * 1000 = 1 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY systemuid, filename ORDER BY mindatetime) AS group_id
    FROM BOOKMARK.MONITORING_TESTING
),
merged_records AS (
    -- 聚合每个连续组的最早mindatetime和最晚maxdatetime
    SELECT 
        systemuid,
        filename,
        MIN(mindatetime) AS merged_mindatetime,
        MAX(maxdatetime) AS merged_maxdatetime,
        uuid_generate_v4() AS fileid
    FROM grouped_records
    GROUP BY systemuid, filename, group_id
),
audit_records AS (
    -- 关联原记录与合并后的fileid，用于审计归档
    SELECT 
        gr.systemuid,
        gr.filename,
        gr.mindatetime,
        gr.maxdatetime,
        mr.fileid AS updated_fileid
    FROM grouped_records gr
    JOIN merged_records mr 
        ON gr.systemuid = mr.systemuid 
        AND gr.filename = mr.filename 
        AND gr.group_id = mr.group_id
)
-- 1. 插入合并后的记录到临时表
INSERT INTO BOOKMARK.MONITORING_TEMP(systemuid, filename, mindatetime, maxdatetime, fileid)
SELECT systemuid, filename, merged_mindatetime, merged_maxdatetime, fileid 
FROM merged_records;

-- 2. 归档原记录到审计表
INSERT INTO BOOKMARK_AUDIT.MONITORING_AUDIT(systemuid, filename, mindatetime, maxdatetime, updated_fileid)
SELECT systemuid, filename, mindatetime, maxdatetime, updated_fileid 
FROM audit_records;

-- 3. 替换原表数据（如果需要保留合并后的记录在原表）
BEGIN;
DELETE FROM BOOKMARK.MONITORING_TESTING
USING grouped_records gr
WHERE BOOKMARK.MONITORING_TESTING.systemuid = gr.systemuid 
  AND BOOKMARK.MONITORING_TESTING.filename = gr.filename 
  AND BOOKMARK.MONITORING_TESTING.mindatetime = gr.mindatetime 
  AND BOOKMARK.MONITORING_TESTING.maxdatetime = gr.maxdatetime;

INSERT INTO BOOKMARK.MONITORING_TESTING(systemuid, filename, mindatetime, maxdatetime)
SELECT systemuid, filename, merged_mindatetime, merged_maxdatetime 
FROM merged_records;
COMMIT;

为什么这比游标快？

全流程是集合式操作，PostgreSQL优化器可以利用索引、并行查询等特性高效处理，避免逐行循环的IO和CPU开销。
避免了游标多次打开/关闭、逐行FETCH的额外开销，大数量下这个差距非常明显。

2. 索引优化：打造覆盖索引减少回表

你现有的systemuid+filename索引不够，因为查询需要排序mindatetime和访问maxdatetime字段，建议创建复合覆盖索引：

CREATE INDEX idx_monitoring_suid_fn_mdt_mxd 
ON BOOKMARK.MONITORING_TESTING (systemuid, filename, mindatetime, maxdatetime);

这个索引可以让窗口函数的LAG操作和排序完全在索引中完成，不需要回表读取数据，能大幅提升查询速度。

3. 若必须保留游标：优化游标本身（不推荐）

如果因为某些业务限制必须用游标，可以做以下优化：

声明只读游标：在游标声明中加上FOR READ ONLY，避免PostgreSQL为游标加不必要的锁：

cursor_data CURSOR FOR 
    SELECT * FROM BOOKMARK.MONITORING_TESTING 
    WHERE filename = v_filename AND systemuid=v_systemuid 
    ORDER BY mindatetime, maxdatetime
    FOR READ ONLY;

批量FETCH：不要逐行FETCH，而是批量读取多条记录（比如1000条），减少IO交互次数：
```
FETCH 1000 FROM cursor_data INTO rec_new_array; -- 需要定义数组类型变量
```
避免嵌套游标：外层游标获取distinct systemuid+filename可以改成先查询到临时表，一次性获取所有分组，再循环处理，减少重复查询的开销。

4. 其他适配亿级数据的优化

分区表：将BOOKMARK.MONITORING_TESTING按systemuid或mindatetime日期分区，这样查询和处理时只扫描相关分区，避免全表扫描。
调整PostgreSQL配置：增大work_mem（用于排序和聚合的内存）、shared_buffers（数据库缓存），减少磁盘IO。
使用UNLOGGED临时表：如果临时表不需要持久化，创建时加上WITH (UNLOGGED)，减少事务日志写入开销：
```
CREATE UNLOGGED TABLE BOOKMARK.MONITORING_TEMP(...);
```
拆分大事务：如果处理亿级数据，不要用单个大事务，每处理一定量（比如10万条）提交一次，避免事务日志膨胀。