新增new_id列并赋值后查询成本上升的原因及优化方法

成本上升的原因 #

1. 表膨胀推高扫描成本：PostgreSQL的UPDATE操作不会直接修改原有数据，而是标记旧元组为死元组，再插入包含新列值的新元组。这次批量更新生成了大量死元组，导致表的磁盘占用量大幅增加，查询时需要扫描更多磁盘数据，所以Parallel Seq Scan的成本直接翻倍。
2. 统计信息滞后：新增列并完成批量更新后，若未及时更新表的统计信息，PostgreSQL查询优化器可能基于旧数据估算成本，导致计算出的查询成本偏高。

降低查询成本的方法 #

1. 清理表膨胀并更新统计信息 #

• 执行VACUUM FULL public.deals;：该操作会重建表，彻底回收死元组占用的磁盘空间，缩小表的实际体积，从而降低全表扫描的成本。注意：此操作会独占锁表，必须在业务低峰期执行。
• 执行ANALYZE public.deals;：强制更新表的统计信息，让查询优化器基于准确的表数据量做成本估算。

2. 利用new_id的顺序特性优化查询 #

由于new_id是严格按created_at排序生成的，二者顺序完全一致，可以通过以下方式优化：

• 给new_id创建索引：

  CREATE INDEX idx_deals_new_id ON public.deals(new_id);
  

• 将查询语句改为SELECT * FROM public.deals ORDER BY new_id ASC LIMIT 100;，优化器会直接利用索引快速定位前100条数据，避免全表排序，查询成本会大幅降低。

3. 直接给created_at建索引（保留原查询语句） #

如果不想修改原有查询语句，直接给created_at创建索引：

CREATE INDEX idx_deals_created_at ON public.deals(created_at);

原查询SELECT * FROM public.deals ORDER BY created_at ASC LIMIT 100;会使用索引扫描，无需全表排序，查询成本会显著下降。

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