PostgreSQL大表自连接查询连续日期记录性能优化求助

看起来你现在卡在了大表自连接计算连续日期相同str_id计数的性能瓶颈上，我来给你梳理几个实战中验证过的优化方向，帮你把这个查询的成本降下来：

一、先排查基础问题：统计信息与内存配置 #

首先你的执行计划里排序成本高得离谱，而且建了索引却没被选中，先从最基础的地方排查：

• 更新表统计信息：旧的统计信息会让优化器做出错误的选择，先执行：

  ANALYZE big_table;
  

• 临时增大work_mem：当前计划里两个Sort操作的成本占了大头，增大work_mem能让PostgreSQL用更高效的内存排序，避免磁盘排序的开销。可以临时设置（根据服务器内存情况调整，比如64MB或128MB）：

  SET work_mem = '64MB';
  

  执行完查询后再改回原来的值，避免影响其他查询。

二、重写查询逻辑：用窗口函数替代自连接 #

你的核心需求是统计连续两天都出现的str_id的数量，自连接会产生巨量中间行（你的计划里预估有41亿行），这是性能差的核心原因。换成窗口函数只需要扫描一次表，效率会高很多：

用LAG函数的写法 #

SELECT COUNT(DISTINCT str_id)
FROM (
    SELECT str_id,
           date_id,
           -- 取同一个str_id的上一条记录的date_id
           LAG(date_id) OVER (PARTITION BY str_id ORDER BY date_id) AS prev_date
    FROM big_table
) t
-- 判断当前日期是否是上一条日期的后一天
WHERE date_id = prev_date + INTERVAL '1 day';

用LEAD函数的写法（效果一致，看个人习惯） #

SELECT COUNT(DISTINCT str_id)
FROM (
    SELECT str_id,
           date_id,
           -- 取同一个str_id的下一条记录的date_id
           LEAD(date_id) OVER (PARTITION BY str_id ORDER BY date_id) AS next_date
    FROM big_table
) t
-- 判断下一条日期是否是当前日期的后一天
WHERE next_date = date_id + INTERVAL '1 day';

这种写法不需要自连接，中间结果集的大小远小于自连接的41亿行，性能提升会非常明显。

三、针对性优化索引，让窗口函数/查询跑更快 #

对于上面的窗口函数写法，最优的索引是按str_id分区、date_id排序的联合索引，因为窗口函数需要按str_id分组，按date_id排序，这个索引可以让PostgreSQL直接走有序的索引扫描，完全避免排序操作：

CREATE INDEX idx_str_date ON big_table(str_id, date_id);

这个索引比你之前建的两个索引更贴合当前的查询需求，执行计划里应该会直接走Index Scan using idx_str_date on big_table，而不是全表扫描+排序。

如果一定要保留原来的自连接写法，那可以尝试：

• 临时关闭全表扫描开关（仅测试用，不要长期开）：SET enable_seqscan = off;，看看优化器会不会选择你建的索引
• 或者把索引改成覆盖索引（不过你的查询只需要date_id和str_id，现有索引已经是覆盖索引了，大概率还是统计信息或内存的问题）

四、长期优化：表分区 #

如果你的big_table数据量长期维持在1.7亿行以上，建议按date_id做范围分区（比如按月份/季度分区）。这样查询的时候，只需要扫描相邻日期的分区，而不是全表扫描，自连接的范围也会缩小到相邻分区，IO和计算量都会大幅降低。

五、验证优化效果的小技巧 #

用EXPLAIN ANALYZE代替EXPLAIN执行查询，这样能看到实际执行时间和实际返回行数，对比优化器的预估行数，如果预估和实际差很多，说明统计信息还是有问题，需要进一步调整统计信息参数（比如增大default_statistics_target）。

备注：内容来源于stack exchange，提问作者Vento