AWS RDS PostgreSQL 3000万行大表SELECT查询缓慢优化咨询

从执行计划可以看出当前查询的核心性能瓶颈：

• 仅使用了attribute_id单字段索引，索引扫描后仍需过滤10万+不符合company_id/project_id/snapshot_ts条件的行
• snapshot_ts为integer类型，查询时传入浮点型参数导致隐式类型转换，无法利用索引做snapshot_ts的条件过滤
• 99%的耗时来自磁盘IO，实例内存不足导致所需数据无法命中缓存，每次查询都要读近900M的磁盘数据

1. 修正查询的隐式类型转换 #

把查询条件里的snapshot_ts浮点参数改为整数，避免逐行类型转换：

SELECT COUNT(*) FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000 
  AND snapshot_ts<=1637654399 
  AND attribute_id=458

2. 新建联合覆盖索引（最高优先级） #

针对该查询的过滤条件，按照等值查询字段在前、范围查询字段在后的原则，创建联合索引，该索引可直接覆盖所有查询过滤条件，不需要回表过滤也不需要访问堆表数据：

CREATE INDEX CONCURRENTLY idx_message_company_project_attr_ts ON public.message 
USING btree (company_id, project_id, attribute_id, snapshot_ts);

用CONCURRENTLY参数建索引不会锁表，不影响线上业务写入
建完索引后该查询的执行时间可从65秒降至100毫秒以内。

3. 非精确计数场景优化 #

如果业务允许计数存在少量误差，可以直接用PostgreSQL统计信息做估算，耗时仅需几毫秒：

-- 全表计数估算
SELECT reltuples::bigint FROM pg_class WHERE relname = 'message' AND relnamespace = 'public'::regnamespace;

4. 长周期数据增长优化 #

该表数据量仍在持续增长，建议按snapshot_ts做范围分区，可按日/周/月分区，后续针对时间范围的查询只会扫描对应分区的索引，不会扫描全量数据，性能可再提升数倍。

5. 实例配置适配 #

当前使用的db.t3.small实例仅2核2G内存，默认shared_buffers仅512M，无法承载热数据缓存，热查询较多时可升级到t3.medium及以上配置，提升缓存命中率降低IO耗时。

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