PostgreSQL单时间列时段表查询各时段起止时间的SQL优化咨询

最优实现方案（性能最优，仅需单次表扫描） #

直接使用PostgreSQL内置的LEAD窗口函数实现，不需要多次关联查询，代码简洁执行效率更高：

SELECT
  period_time AS begin,
  COALESCE(
    LEAD(period_time) OVER (ORDER BY period_time ASC),
    MIN(period_time) OVER ()
  ) AS end,
  name
FROM periods
ORDER BY period_time;

方案说明 #

1. 性能优势：原有实现用了两次LATERAL JOIN，每一行数据都要触发两次子查询，N个时段就需要执行2N+1次表扫描；本方案仅需1次全表扫描即可完成所有计算，性能提升是数量级的，尤其适合时段数量多的场景，也非常适合嵌入作为子查询使用。
2. 逻辑说明：
   • LEAD(period_time) OVER (ORDER BY period_time ASC)：按时间正序排序后，直接取当前行下一行的时段时间作为当前时段的结束时间
   • MIN(period_time) OVER ()：取全表最小的时段时间，用COALESCE替换最后一行LEAD返回的空值，刚好对应跨天的晚间时段结束时间
3. 可选优化：如果给period_time字段创建B树索引，查询的排序步骤可以直接复用索引顺序，完全避免内存排序开销，性能会进一步提升。

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