除索引优化外，如何提升PostgreSQL中1亿行理赔表的查询效率？

该问题更适配DBA StackExchange平台，可协助转移。

• 数据集：db1_dummy共1亿条保险理赔记录，部署于PostgreSQL v13（Windows 64位本地环境）
• 涉及字段：主键id、投保人标识member_composite_id、理赔日期service_date

1. 将service_date转换为以2009-01-01为起点的天数整数，命名为service_date_2
2. 新增service_date_1字段：同一投保人的第一条记录值为0，其余记录取该投保人上一条记录的service_date_2值
3. 若service_date_1与service_date_2差值为0，将service_date_1减0.1，避免间隔为0

你当前使用的自连接+分组聚合方案时间复杂度为O(n²)，每一条记录都需要关联匹配同一投保人的所有历史记录，1亿条数据量级下运算量会指数级增长，因此执行效率极低，即使加了单列索引也无法解决根本逻辑的性能问题。

1. 替换为窗口函数实现（核心优化） #

使用PostgreSQL内置的LAG()窗口函数，仅需一次表扫描+分组排序即可完成所有计算，时间复杂度降至O(n log n)，性能提升可达数百倍。优化后SQL如下：

CREATE TABLE db1_dummy_2 AS
SELECT 
    id,
    member_composite_id,
    -- 处理service_date_1规则
    CASE
        WHEN prev_date IS NULL THEN 0
        WHEN prev_date = service_date_2 THEN service_date_2 - 0.1
        ELSE prev_date
    END AS service_date_1,
    service_date_2
FROM (
    -- 内层预计算天数和上一条记录的日期值
    SELECT
        id,
        member_composite_id,
        service_date - '2009-01-01'::DATE AS service_date_2,
        -- 按投保人分组，按id升序取上一条的service_date_2
        LAG(service_date - '2009-01-01'::DATE) OVER (
            PARTITION BY member_composite_id 
            ORDER BY id ASC
        ) AS prev_date
    FROM db1_dummy
) t
ORDER BY id;

如果同一投保人的记录需要按service_date排序而非id排序，只需要把窗口函数里的ORDER BY id ASC替换为ORDER BY service_date ASC, id ASC即可。

2. 数据库配置临时优化 #

执行查询前可调整以下PostgreSQL会话级参数，避免内存不足触发磁盘排序，进一步提升速度：

-- 调大排序/窗口函数可用内存，根据机器总内存调整，16G内存可设为4GB
SET work_mem = '4GB';
-- 调大建表操作可用内存
SET maintenance_work_mem = '8GB';
-- 临时关闭自动vacuum，减少资源占用
SET autovacuum_enabled = off;

查询执行完成后再恢复原有配置即可。

3. 可选额外优化 #

如果后续还要频繁基于投保人维度做计算，可以提前创建覆盖索引，避免全表扫描：

CREATE INDEX idx_member_date_id ON db1_dummy (member_composite_id, id, service_date);

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