PostgreSQL索引未被使用导致查询过慢的优化咨询

环境与数据情况 #

使用AWS RDS PostgreSQL作为数据库，现有两张核心表：

• childbirth_data表：约500万条记录，每月新增约30万条
• messages表：约3000万条记录，每月新增约300万条

数据写入通过夜间定时任务执行，因此无需担心新增索引导致的写入性能下降。

当前查询语句 #

SELECT message_date, message_from, sms_type,  message_type, direction, status,
 cd.state ,cd.date_uploaded
FROM "Suvita".messages m, "Suvita".childbirth_data cd
where m.contact_record_id = cd.id

初始执行计划 #

Hash Join  (cost=568680.28..6272284.94 rows=29688640 width=319) (actual time=5473.787..96501.807 rows=30893261 loops=1)
  Hash Cond: (m.contact_record_id = cd.id)
  ->  Seq Scan on messages m  (cost=0.00..2739237.50 rows=29719350 width=174) (actual time=2.364..41071.274 rows=30936614 loops=1)
  ->  Hash  (cost=402612.68..402612.68 rows=5347568 width=121) (actual time=5448.157..5448.158 rows=5349228 loops=1)
        Buckets: 32768  Batches: 256  Memory Usage: 3518kB
        ->  Seq Scan on childbirth_data cd  (cost=0.00..402612.68 rows=5347568 width=121) (actual time=0.011..3013.382 rows=5349228 loops=1)
Planning Time: 18.349 ms
Execution Time: 97849.004 ms

已做的优化措施 #

1. 为仪表盘创建了聚合表，根据报表需求创建了不同状态/消息类型的物化视图
2. 已创建的索引：

   childbirth_data - id, state, mother name, mother phone, rchid, telerivet_contact_id
   messages - id, contact_record_id, message_date and state
   

3. 补充操作：已在m.contact_record_id = cd.id关联条件上创建索引；关闭哈希连接（enable_hashjoin = false）后，执行计划如下：

Workers Planned: 2
Workers Launched: 2
->  Merge Join  (cost=9142830.70..9948915.12 rows=12996738 width=317) (actual time=109544.770..123398.539 rows=10500973 loops=3)
Merge Cond: (m.contact_record_id = cd.id)
->  Sort  (cost=7447866.74..7480412.83 rows=13018435 width=172) (actual time=85772.254..90202.085 rows=10500974 loops=3)
Sort Key: m.contact_record_id
Sort Method: external merge  Disk: 1497424kB
Worker 0:  Sort Method: external merge  Disk: 1485808kB
Worker 1:  Sort Method: external merge  Disk: 1481472kB
->  Parallel Seq Scan on messages m  (cost=0.00..2572228.35 rows=13018435 width=172) (actual time=0.367..40690.536 rows=10515424 loops=3)
->  Sort  (cost=1694830.27..1708199.61 rows=5347737 width=121) (actual time=23772.489..25077.382 rows=5349239 loops=3)
Sort Key: cd.id
Sort Method: external merge  Disk: 722360kB
Worker 0:  Sort Method: external merge  Disk: 722384kB
Worker 1:  Sort Method: external merge  Disk: 722368kB
->  Seq Scan on childbirth_data cd  (cost=0.00..402625.37 rows=5347737 width=121) (actual time=0.035..16575.124 rows=5349239 loops=3)
Planning Time: 0.886 ms
Execution Time: 136276.437 ms

疑问 #

• 为何索引未被使用？如何让PostgreSQL使用索引？
• 按state分区是否有用？某一州数据占比90%，且不想改动写入代码
• 还有哪些数据库/表层面的优化手段可以提升读性能？

一、让索引生效的关键调整 #

1. 创建覆盖索引
   当前查询需要从两张表读取特定字段，普通单字段索引无法直接返回所需数据，PostgreSQL会选择全表扫描更划算。针对该查询创建覆盖索引：

• 对messages表：

  CREATE INDEX idx_messages_contact_record_id_covering ON "Suvita".messages (contact_record_id) INCLUDE (message_date, message_from, sms_type, message_type, direction, status);
  

• 对childbirth_data表：

  CREATE INDEX idx_childbirth_id_covering ON "Suvita".childbirth_data (id) INCLUDE (state, date_uploaded);
  

覆盖索引包含查询所需所有字段，PostgreSQL可直接从索引获取数据，无需回表，大幅降低IO开销。

2. 更新统计信息
   如果统计信息过时，优化器可能做出错误判断。执行以下命令更新：

ANALYZE "Suvita".messages;
ANALYZE "Suvita".childbirth_data;

对于大表，可使用ANALYZE VERBOSE查看进度，或调整default_statistics_target参数（如设为1000）提升统计精准度。

3. 恢复哈希连接默认设置
   哈希连接是PostgreSQL处理大表关联的高效方式，禁用后改用合并连接反而因磁盘排序开销更大导致变慢，恢复默认：

SET enable_hashjoin = true;

二、分区方案的可行调整 #

按state分区因数据倾斜效果不佳，可换以下策略：

1. 按时间分区
   数据每月新增，且仪表盘查询多按时间范围，可按message_date（messages表）和date_uploaded（childbirth_data表）做范围分区。AWS RDS支持自动分区，无需改动写入代码——只需创建分区表并迁移原数据，后续写入会自动路由到对应分区。
2. 列表分区+倾斜数据单独处理
   若必须按state分区，把占90%数据的州单独作为一个分区，其他州合并为一个分区，查询该大州数据时可直接扫描单个分区，减少扫描范围。

三、其他读性能优化手段 #

1. 优化物化视图
   针对当前查询创建专属物化视图，直接从视图读取数据避免实时关联大表：

CREATE MATERIALIZED VIEW mv_message_childbirth AS
SELECT m.message_date, m.message_from, m.sms_type, m.message_type, m.direction, m.status,
       cd.state, cd.date_uploaded
FROM "Suvita".messages m
JOIN "Suvita".childbirth_data cd ON m.contact_record_id = cd.id;

利用夜间写入窗口自动刷新：

REFRESH MATERIALIZED VIEW mv_message_childbirth;

若需避免锁表，可添加CONCURRENTLY选项（需先给物化视图创建唯一索引）。

2. 调整RDS实例配置

• 提升实例内存规格，让PostgreSQL将更多数据缓存到shared_buffers和操作系统缓存，减少磁盘IO。当前哈希连接内存不足会触发磁盘交换，拖慢速度。
• 开启RDS只读副本，将仪表盘查询流量分流到副本，减轻主库压力。

3. 表结构与存储优化

• 检查字段类型：比如state若为固定枚举值，改用ENUM或SMALLINT替代VARCHAR，减少存储空间与IO。
• 执行VACUUM ANALYZE清理死元组，优化表存储结构：

  VACUUM ANALYZE "Suvita".messages;
  VACUUM ANALYZE "Suvita".childbirth_data;
  

4. 限制查询范围
   若无需实时全量数据，添加时间过滤条件减少扫描行数：

SELECT ...
FROM "Suvita".messages m
JOIN "Suvita".childbirth_data cd ON m.contact_record_id = cd.id
WHERE m.message_date >= CURRENT_DATE - INTERVAL '3 months';

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