PostgreSQL为何选择顺序扫描而非索引扫描？

你的核心困惑是：明明索引扫描在特定条件下速度远超顺序扫描，但PostgreSQL优化器有时仍会选择顺序扫描。这背后是优化器基于统计信息和成本模型的决策逻辑，拆解关键原因如下：

1. 统计信息过时或不准确 #

PostgreSQL优化器依赖pg_statistic系统表的统计数据判断扫描成本。如果表数据发生大量变更后未执行ANALYZE，优化器会误判数据分布：

• 比如你的表插入400万行后，若没跑ANALYZE t_test;，优化器可能不知道id的最大值是多少，误以为id > 2756021会返回大量行，此时会认为顺序扫描（无需额外索引IO）比索引扫描（先读索引再回表）成本更低。
• 执行ANALYZE t_test;更新统计信息后，优化器能更准确判断返回行数，大概率会切换到索引扫描。

2. 默认成本参数不匹配存储设备 #

PostgreSQL的成本估算基于一组默认参数，其中：

• seq_page_cost：顺序读一个数据页的成本（默认1.0）
• random_page_cost：随机读一个数据页的成本（默认4.0）
• 如果你用的是SSD，随机IO成本远低于机械硬盘，默认的random_page_cost设置过高，会让优化器认为索引扫描的回表IO成本太高，从而倾向选择顺序扫描。可以临时调整参数测试：

  SET random_page_cost = 1.1;
  EXPLAIN ANALYZE SELECT * from t_test where id > 2756021 LIMIT 2;
  

3. LIMIT子句的"快速返回"误判 #

当查询带LIMIT时，优化器会优先考虑快速拿到前N行的成本：

• 如果优化器误判目标行在表中的位置（比如认为id > X的行很靠前），会觉得顺序扫描能更快找到前2行，从而选择顺序扫描。
• 你的首次查询中目标行在表后半段，顺序扫描慢，但如果后续查询的X值变小，优化器判断目标行靠前，就会选顺序扫描。

4. 缓存命中导致的性能反转 #

你提到"重新开启顺序扫描后，部分查询顺序扫描效率更高"，这几乎是缓存命中导致的：

• 首次顺序扫描后，表的数据页已被加载到PostgreSQL的共享缓存（shared_buffers）中，后续顺序扫描直接从内存读，速度大幅提升，甚至超过索引扫描（索引扫描需要读索引页+数据页，而缓存中的顺序扫描是连续读，效率更高）。
• 可以重启数据库清空缓存后再测试，会发现顺序扫描速度回到初始的慢状态。

验证步骤 #

• 执行ANALYZE t_test;更新统计信息，重新跑EXPLAIN ANALYZE看计划变化。
• 调整random_page_cost参数，对比不同参数下的查询计划。
• 查看id列的统计信息：

  SELECT attname, n_distinct, most_common_vals, histogram_bounds FROM pg_stats WHERE tablename = 't_test' AND attname = 'id';
  

  确认histogram_bounds是否准确反映了id的分布范围。

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