PostgreSQL中按时间阈值合并相邻行的性能优化问题

核心思路：用窗口函数替代嵌套循环分组 #

你的问题本质是分组连续满足条件的记录，这类场景用窗口函数（尤其是LAG()和累加标记分组）比嵌套子查询效率高得多，能彻底避免嵌套循环带来的性能损耗。

步骤1：标记可合并的记录组 #

用LAG()窗口函数获取同容器前一条记录的date_to，计算当前记录与前一条的时间间隔，通过累加标记划分不同的合并组：

WITH grouped_records AS (
    SELECT
        container,
        date_from,
        date_to,
        -- 当前记录与前一条不满足合并条件时，生成新组标记
        SUM(CASE 
            WHEN LAG(date_to) OVER (PARTITION BY container ORDER BY date_from) + INTERVAL '5 minutes' >= date_from
            THEN 0
            ELSE 1
        END) OVER (PARTITION BY container ORDER BY date_from) AS group_id
    FROM positions
)

这里的INTERVAL '5 minutes'是时间阈值，可根据实际需求调整。group_id会自动将所有连续可合并的记录归为同一组。

步骤2：按组聚合得到合并结果 #

基于分组直接聚合，取每组最早的date_from和最晚的date_to：

SELECT
    container,
    MIN(date_from) AS merged_date_from,
    MAX(date_to) AS merged_date_to
FROM grouped_records
GROUP BY container, group_id
ORDER BY container, merged_date_from;

这个方案全程用窗口函数+单次聚合，避免了嵌套循环带来的重复扫描或笛卡尔积，性能远优于原嵌套子查询。

索引优化：让窗口函数效率最大化 #

窗口函数依赖PARTITION BY container ORDER BY date_from的排序逻辑，必须给这个组合创建索引：

CREATE INDEX idx_positions_container_date_from ON positions (container, date_from);

该索引能让PostgreSQL直接按顺序读取同容器的记录，无需额外排序，大幅降低窗口函数的计算开销。

分区方案（仅针对超大规模数据） #

如果positions表数据量达到千万甚至亿级，可考虑以下分区策略：

• 按container分区：将每个容器的记录单独放在一个分区，窗口函数的PARTITION BY container只需扫描单个分区，无需跨分区处理。
• 按时间分区：若查询常聚焦于特定时间段，按date_from的年月分区，减少扫描的数据量。

注意：分区并非必需，仅当单表数据量极大（如超1000万行）时才需要考虑，否则索引优化已足够解决性能问题。

原查询性能差的原因 #

你提到的with_first_to_squash子查询应该采用了自关联嵌套（比如每条记录都关联自身找前一条），这种方式的时间复杂度为O(n²)，当数据量增大时性能会急剧下降；而窗口函数的时间复杂度仅为O(n log n)（有索引时可降至O(n)），性能差距非常明显。

内容的提问来源于stack exchange，提问作者Adriano di Lauro