PostgreSQL选择顺序扫描而非索引扫描的场景与问题排查

首先明确核心逻辑：PostgreSQL优化器完全基于成本估算结果选择执行计划，不存在无理由的"异常选择"。你给出的两组执行计划里，顺序扫描的估算总成本为1.04，主键索引扫描的估算总成本为8.17，优化器判定顺序扫描成本更低才会做出对应选择，和表结构、索引定义是否被修改没有必然联系。

逐点排查方向 #

• 确认表实际数据量
  当表总数据量极小（通常是小于1个数据页、仅存几行到几十行数据）时，顺序扫描只需要读取极少量磁盘页，总IO开销确实低于索引扫描（索引扫描需要先读索引页、再回表读数据页，至少两次IO），属于优化器的正确选择，不是故障。重建表后如果测试数据量发生变化，成本估算阈值翻转，就会重新选择索引扫描。
  你可以执行以下命令确认实际值：

  -- 查看表实际行数
  select count(*) from tabverifies;
  -- 查看统计信息中记录的表占用页面数、行数
  select relpages, reltuples from pg_class where relname = 'tabverifies';
  

  如果返回的relpages值为1、reltuples在几十以内，走顺序扫描完全是预期行为，不需要修正，等表数据量增长后会自动切换为索引扫描。
• 刷新统计信息验证
  如果对表做过批量增删改操作后没有触发自动统计信息收集，统计信息记录的表大小、数据分布和实际值偏差过大，会导致成本估算错误。手动执行以下命令刷新统计信息后，再查看执行计划是否恢复：

  ANALYZE VERBOSE tabverifies;
  

• 检查索引有效性与膨胀率
  如果索引因为大量更新、删除操作产生高比例碎片，或者被意外标记为无效状态，会导致索引扫描的成本估算值异常升高，优化器会主动放弃索引。重建表时索引会被完全重建，碎片被清理，自然会恢复索引选择。
  执行以下命令检查索引状态：

  -- 检查主键索引是否有效，返回t为正常
  select indisvalid from pg_index where indexrelid = 'tabverifies_pkey'::regclass;
  

• 核对成本参数配置
  如果random_page_cost参数设置不符合实际存储性能（比如SSD存储下仍沿用机械盘默认的4.0配置），会高估索引回表的随机IO开销，小表场景下很容易判定索引扫描成本高于顺序扫描。可以执行show random_page_cost;查看当前值，SSD场景下建议设置为1.1~1.5。

执行计划参考 #

你提供的顺序扫描场景执行计划：

testdb=> \d tabverifies; 
                             Table "public.tabverifies" 
 Column |   Type   | Collation | Nullable |                 Default 
--------+----------+-----------+----------+------------------------------------------ 
 vid    | integer  |           | not null | nextval('tabverifies_vid_seq'::regclass) 
 lid    | integer  |           | not null | 
 verify | integer  |           | not null | 
 secret | text     |           | not null | 
Indexes: 
    "tabverifies_pkey" PRIMARY KEY, btree (vid) 
    "tabverifies_lid_verify_key" UNIQUE CONSTRAINT, btree (lid, verify) 
Foreign-key constraints: 
    "tabverifies_lid_fkey" FOREIGN KEY (lid) REFERENCES tablogins(lid)
 
testdb=> explain select * from tabverifies where vid=1000; 
                         QUERY PLAN 
------------------------------------------------------------ 
 Seq Scan on tabverifies  (cost=0.00..1.04 rows=1 width=44) 
   Filter: (vid = 1000) 
(2 rows)

重建表后索引扫描场景执行计划：

testdb=> \d tabverifies; 
                             Table "public.tabverifies" 
 Column |   Type   | Collation | Nullable |                 Default 
--------+----------+-----------+----------+------------------------------------------ 
 vid    | integer  |           | not null | nextval('tabverifies_vid_seq'::regclass) 
 lid    | integer  |           | not null | 
 verify | integer  |           | not null | 
 secret | text     |           | not null | 
Indexes: 
    "tabverifies_pkey" PRIMARY KEY, btree (vid) 
    "tabverifies_lid_verify_key" UNIQUE CONSTRAINT, btree (lid, verify) 
Foreign-key constraints: 
    "tabverifies_lid_fkey" FOREIGN KEY (lid) REFERENCES tablogins(lid)
 
sigserverdb=> explain select * from tabverifies where vid=1; 
                                     QUERY PLAN 
------------------------------------------------------------------------------------- 
 Index Scan using tabverifies_pkey on tabverifies  (cost=0.15..8.17 rows=1 width=44) 
   Index Cond: (vid = 1) 
(2 rows)

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