执行计划 Seq Scan 真相：为何它不一定需要全表扫描 ？

Seq Scan 问题测试和场景复现

学无止境！遇到仔细深究好学的甲方，跟着一起测试验证学习下！

问题：在explain打印执行计划时的全表顺序扫描（ Seq Scan ）显示的 rows 行数与表行数对不上，Filter过滤后的数据量也对不上，跟limit分页的数目也不匹配

测试数据环境构建：

drop table t_test_1;
drop table t_test_2;
create table t_test_1(col1 int,col2 text);
create table t_test_2(col1 int,col2 text);
insert into t_test_1 select id,id||'OK' from generate_series(1,30050) as id;
insert into t_test_2 select id,id||'OK' from generate_series(1,30000) as id;
create index idx_t_test_1 on t_test_1(col2);
create index idx_t_test_2 on t_test_2(col2);

分析：
查询条件：可以发现WHERE子句过滤条件后面有两个，t1和t2的关联（substr(t1.col2,2)=t2.col2）+ （t1.col1<50），共同限制了需要扫描的行数，执行计划器会根据表的统计信息来估算满足条件的行数。
LiMIT子句：LIMIT 2 明确告诉执行计划器只需要前两行满足条件的数据。因此在生成执行计划时一旦找到两行满足条件的数据就会尽可能早的停止扫描。

explain (analyze,verbose) select * from t_test_1 t1,t_test_2 t2 where substr(t1.col2,2)=t2.col2 and t1.col1<50 limit 2;
explain (analyze,verbose) select * from t_test_1 t1,t_test_2 t2 where substr(t1.col2,3)=t2.col2 and t1.col1<50 limit 2;

当过滤条件刚好完全匹配，Seq Scan 开始表扫描时直接提取满足条件的 limit 数据量即可返回

explain (analyze,verbose) select * from t_test_1 t1,t_test_2 t2 where t1.col2=t2.col2 and t1.col1<50 limit 2;

结论：Seq Scan 的全表顺序扫描不一定需要扫描全表的所有数据

PostgreSQL 数据库的表扫描方式

1. 全表顺序扫描（Seq Scan）

• 场景：
  • 查询需要访问表中大部分数据（通常超过5-10%）
  • 表很小（通常小于几百行）
  • 表没有合适的索引
  • 查询条件无法使用索引（如使用了函数或运算符）
  • 全表扫描的成本低于使用索引时
• 特点：
  • 逐行读取表中的所有数据
  • 适用于小表或未建立索引的列
  • 成本较高，尤其是对于大表
• 示例：

  explain analyze select * from t_test_1 t1 where t1.col1<20;
  

2. 索引扫描（Index Scan）

• 场景：通过索引查找数据，然后访问表获取完整行
• 特点：
  • 通过索引快速定位到符合条件的数据行（查询只返回少量行）
  • 适用于大表且有适当索引的列（查询条件能有效使用索引）
  • 成本较低，尤其是对于选择性高的查询（不同值多）
• 示例：

  explain analyze select * from t_test_1 t1 where t1.col2='OK';
  

3. 仅索引扫描（Index Only Scan）

• 场景：直接从索引中获取数据，无需访问表中的数据
• 特点：
  • 仅扫描索引，不访问表数据
  • 适用于覆盖索引（即索引包含查询所需的所有列）
  • 成本最低，因为避免了表访问
• 示例：

  explain analyze select t1.col2 from t_test_1 t1 where t1.col2='1OK';
  

  

4. 位图索引扫描（Bitmap Index Scan）

• 场景：先通过索引创建位图，然后按物理顺序访问表(查询条件涉及多个列，且这些列都有索引)
• 特点：
  • 使用多个索引生成位图，然后合并位图以定位数据行
  • 适用于多列查询，尤其是当这些列的索引选择性不高时(多条件查询，每个条件有单独索引)
  • 成本较低，尤其是对于复杂查询
• 示例：

  create index idx_t_col1 on t_test_1(col1);
  explain analyze select t1.col2 from t_test_1 t1 where t1.col1<20 or t1.col2='OK';
  

  如果 col1 和 col2 列都有索引，可能会选择位图索引扫描。
  

5. TID 扫描（TID Scan）

• 场景：直接使用行的物理标识符（TID）
• 特点：
  • 直接通过 TID 访问数据行
  • 适用于已知行位置的情况
  • 成本较低，但通常不常见
• 示例：

  explain analyze select * from t_test_1 where ctid='(0,1)';
  

  ctid 是 PostgreSQL 的系统列，表示行的物理位置。类似Oracle的rowid
  

6. 并行扫描（Parallel Seq Scan / Parallel Index Scan）

• 场景：多个工作进程并行执行顺序扫描，表较大且系统资源允许时
• 特点：
  • 使用多个进程并行扫描表或索引
  • 适用于大表查询，可以显著提高性能
  • 成本取决于并行度和系统资源
  • max_parallel_workers_per_gather参数设置合理