PostgreSQL已创建相关索引但时间范围查询缓慢，如何优化？

问题根因分析 #

• 查询的时间范围为2015年到2020年共5年，该区间大概率覆盖了表中绝大多数甚至全部740万条记录。这种场景下time字段的普通索引不会被优化器选中，因为走索引需要先扫描索引再回表读取所有字段，开销远高于直接顺序扫描全表，最终执行计划选择全表扫描，是查询慢的核心原因。
• 从运行指标可以看到磁盘IO利用率长时间处于满负载状态，全表扫描需要将整个表的数据从磁盘读取到内存，IO瓶颈直接拉长了查询耗时。

优化方案 #

1. 创建覆盖索引
   如果该查询是高频查询，可创建包含所有返回字段的覆盖索引，避免回表开销：

CREATE INDEX idx_time_cover ON test_prices(time) INCLUDE (price, set_id);

创建后查询可以直接从索引中获取所有需要的字段，不需要回表，查询效率会有明显提升。
2. 按时间字段做表分区
如果经常需要按大范围时间区间查询数据，可将test_prices表改造为按time字段分区的分区表，按照年/季度/月的粒度拆分分区。查询时只会扫描对应时间范围内的分区，大幅减少需要扫描的数据量。
3. 配置物化视图
如果该查询的结果不要求强实时性，可创建对应时间范围的物化视图，定期刷新即可。后续查询直接读取物化视图，耗时可降到毫秒级。
4. 调整数据库参数与硬件配置

• 适当调大PostgreSQL的shared_buffers、work_mem参数，提升内存缓存占比，减少磁盘IO次数
• 如果当前实例使用的是低IOPS的存储，可升级到更高IOPS的SSD存储，解决磁盘IO瓶颈。

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