PostgreSQL 适配时间范围聚合查询的复合索引列顺序选型

你当前拟定的create index project_index on timestamppsql(timestamp1,id13)是完全匹配业务场景的最优设计，无需调整列顺序，绝对不要把id13放到索引首位。

为什么不能采用(id13, timestamp1)的列顺序 #

• 你现在列的三类日常查询，WHERE条件全部只针对timestamp1做时间范围过滤，没有任何一条查询会带上id13的等值或范围筛选条件。
• 按照PostgreSQL B树索引的左前缀匹配规则，如果索引的首列是id13，因为查询条件完全没有指定id13的过滤值，数据库无法通过首列快速缩小扫描范围，只能遍历全索引的所有条目，完全发挥不了索引的过滤作用，最终查询效率甚至会和全表扫描差不多。
• 哪怕id13本身的重复值少、基数高、过滤性强，只要日常查询不会把它放到过滤条件里，再高的筛选性都无法转化为实际的查询效率收益。

为什么(timestamp1, id13)的顺序可以完美覆盖你的查询场景 #

这个列顺序刚好同时满足索引的两个核心价值：快速缩小扫描范围、零回表开销：

1. 首列直接承担全部时间范围过滤的诉求
   所有查询的时间筛选条件可以直接命中B树索引的首列，快速定位到目标时间区间对应的索引块，直接剔除所有不在筛选时间范围内的行，把扫描范围从全表缩小到目标时间对应的小范围索引区间。
2. 第二列天然形成覆盖索引，完全不需要回表读取主表数据
   你所有查询需要读取的字段，包括计算用的timestamp1、聚合/返回用的id13，都已经完整包含在这个复合索引的结构里。数据库扫完索引上的目标区间就可以直接完成全部计算和结果返回，不需要再回到主表读取行数据，整体查询效率比需要回表的索引高一个量级。
   • 针对按天维度聚合平均的查询：扫到目标时间区间的索引条目后，直接提取条目里存的timestamp1做按天分组、提取id13做平均值计算，全程不触碰主表。
   • 针对指定时间区间查id13的查询：索引结构本身已经存储了对应时间范围的所有id13值，扫完对应区间就可以直接返回结果。
   • 针对短时间窗口统计平均的查询：短窗口对应的索引扫描范围极小，加上无回表开销，统计速度会达到最优。

这里要纠正一个关于复合索引选列的常见认知误区：左列的筛选性优先级，永远要让位于「是否被查询条件实际用来过滤」这个核心前提。
哪怕某一列的重复值再少、基数再高，只要日常查询不会用它做过滤条件，把它放到索引首位就是资源浪费。反过来，哪怕首列的重复值不算极低，只要所有查询都固定用它做范围过滤，把它放首位的收益远高于其他列排序方案。

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