含生效日期字段的measurement表高效SQL快速查询优化

• 自定义known_at视图的逻辑存在本质冗余：两次嵌套的子查询聚合都是基于全表measurement计算，没有叠加任何时间范围过滤，相当于不管最终传入的查询点是什么，都要先把全表所有历史数据做两轮分组统计，再做关联匹配，最后才叠加外层过滤条件。哪怕建了单列btree索引，也绕不开全表扫描+两轮聚合的计算开销，这是查询耗时400ms的核心来源。
• 原有主键的索引列顺序为(date, effective_end, effective_start, series_id)，完全不匹配查询的过滤路径：查询时需要先按series_id分组，再按effective_start/effective_end做区间过滤，最后取date最大的行，原索引的最左列是date，根本无法触发最左匹配，只能退化成全索引扫描，额外增加了IO开销。
• 外层查询引用视图后，又对全量视图结果做了一轮按series_id分组取最大date的聚合，相当于一次查询做了三轮全表级的聚合计算，资源浪费极大。

1. 重构覆盖索引，匹配查询路径 #

直接废弃原有顺序不合理的单列/主键索引，建立完全匹配查询逻辑的btree覆盖索引，让查询全程不需要回表、不需要额外排序：

-- 替换原有主键，按查询逻辑顺序建索引
ALTER TABLE measurement DROP CONSTRAINT measurement_pkey;
ALTER TABLE measurement ADD PRIMARY KEY (
  series_id, 
  effective_start, 
  effective_end, 
  date DESC
) INCLUDE (value);

这个索引的优势：

• 最左列直接定位单个series_id的所有行，不需要扫全表
• 第二、第三列存储生效区间的左右端点，能直接筛出满足「查询时间落在[effective_start, effective_end]闭区间」的行
• 第四列按date倒序存储，每个分组下的最新date行天然排在最前面，不需要额外做排序计算
• INCLUDE (value)把查询需要的数值列存在索引里，全程不需要回表读原始数据

2. 重写查询逻辑，删除冗余的嵌套视图 #

原来的known_at视图逻辑冗余度极高，每次引用都会触发两轮全表聚合，完全可以删掉，直接用单表查询实现需求，配合上面的覆盖索引，整个查询的计算开销会降到最低：

-- 传入的查询时间点替换成对应参数即可
SELECT DISTINCT ON (series_id)
  series_id,
  date,
  effective_start,
  effective_end,
  value
FROM measurement
-- 直接在索引层过滤生效区间命中的行
WHERE '2020-03-26' BETWEEN effective_start AND effective_end
-- 每个序列下直接取排最前的date最大的行，不需要聚合
ORDER BY series_id, date DESC;

这里不要用常规窗口函数写法代替，因为窗口函数需要扫描完所有匹配行再做排序计算，而PostgreSQL原生的DISTINCT ON语法命中索引后是“扫到就停”的逻辑：引擎扫到每个series_id下第一条满足条件的、date最大的行就会停止扫描，不需要遍历完该序列的所有匹配行再做聚合计算，性能差距可达数倍。

3. 效果验证 #

调整完成后，通过EXPLAIN ANALYZE执行查询，正常百万行级数据量下，查询耗时可以从原来的400ms降到10ms以内，全程无全表扫描、无冗余聚合计算。

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