可高效查询多类时间区间事件关联关系的数据库模型选型

核心需求为对分别存储在独立数据表中的、带起始/结束时间属性的多类业务实体（单表数据量约10^7级别），快速匹配存在时间关联的记录，典型场景为查询住院登记周期内、对应手术开展时段发生的所有用药记录。

传统设计的性能痛点 #

按照经典RDBMS分表设计惯例，住院登记、手术、用药记录分别独立存储，保留各实体的专属业务字段。若直接通过手写时间比较表达式做跨表区间关联，千万级数据量下会触发全表顺序扫描，单查询耗时通常达到秒级甚至分钟级，无法满足高频查询需求。

核心优化路径 #

• 保留原分表设计的基础上统一字段标准
  维持三类实体独立存储的原有设计不变，每张表保留自身专属业务字段，同时统一存储event_start_time、event_end_time两个时间字段，保持跨表的字段命名、数据类型一致，消除查询时的类型转换、字段名映射开销，避免为了时间关联查询强行合并宽表带来的性能损耗。
• 适配区间查询的索引选型
  开启btree_gist扩展后，针对每张表的两个时间字段创建GiST类型多列索引，替代传统单列B树索引。这类索引原生支持快速判断两个时间区间的重叠、包含、邻接等关联关系，千万级数据下的区间匹配效率比顺序扫描提升2个数量级以上，且索引整体内存占用远低于多列独立B树索引的组合。
• 简化查询逻辑适配索引下推
  跨表时间关联的判断直接使用Postgres原生的区间匹配逻辑，避免手写复杂嵌套的时间计算表达式导致优化器无法命中索引。通用的“两时间区间存在重叠”判断条件可写为：

  表A.event_start_time < 表B.event_end_time
  AND 表A.event_end_time > 表B.event_start_time
  

  针对两层嵌套的关联查询（先匹配住院对应的手术记录，再匹配手术时段内的用药记录），优先通过GiST索引逐层缩小筛选范围，避免一次性加载全表数据到内存做嵌套循环计算。

超大数据量下的进阶优化 #

• 当单表数据量突破10^8级别时，可将时间字段转换为Postgres内置的tsrange/tstzrange区间类型存储，进一步压缩GiST索引的体积，提升区间匹配速度。
• 针对高频运行的关联查询，匹配查询涉及的临时内存排序需求调整work_mem参数值，避免排序过程中触发磁盘临时表写入拖慢查询性能。

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