开发者日记

外观

一文搞懂 Seq Scan 与 Bitmap Scan 选择逻辑 ​

为什么同一条 SQL 有时走索引、有时全表扫 ​

很多 PostgreSQL 性能问题最终都会落到执行计划里的扫描算子上：明明已经建了索引，EXPLAIN 却显示 Seq Scan；或者优化器在小结果集时选择 Index Scan，数据量稍大又切换成 Bitmap Heap Scan。这不是随机行为，而是优化器基于统计信息与成本参数计算后的结果。

本文聚焦三个最常见算子：Seq Scan、Index Scan、Bitmap Heap Scan，重点拆解顺序扫描与位图扫描的选择逻辑，并解释 random_page_cost 为什么会显著影响决策。

三类扫描算子的核心差异 ​

Seq Scan 从头到尾读取堆表，每一页都按物理顺序访问，适合取出表中较大比例的数据。它的优点是预读友好、单次 I/O 摊销低；缺点是即使只要一行，也要扫描全部页面。

Index Scan 先访问索引，再根据堆指针逐行回表。如果结果集很小，这种方式非常快；但当匹配行分散在大量堆页中时，回表会产生大量随机 I/O，成本迅速上升。

Bitmap Scan 是两者之间的折中方案。它通常由两个算子组成：Bitmap Index Scan 先在索引上收集所有匹配条目的堆指针，并在内存中构建位图；Bitmap Heap Scan 再按页面汇总后批量回表。这样多个命中同一数据页的行只需读取一次页面，并且回表前可按物理页号排序，明显减少随机访问。

Seq Scan顺序读取全部堆页Index Scan索引定位后逐行回表Bitmap Scan索引建位图后批量回表适合高选择率读取大量行更划算适合低选择率少量精确匹配适合中等选择率或多索引组合减少重复页与随机回表

优化器如何比较扫描成本 ​

PostgreSQL 优化器并不直接知道查询实际要跑多久，它使用一个抽象成本模型。关键参数包括：

  • seq_page_cost：顺序读取一个页面的成本，默认 1.0；
  • random_page_cost：随机读取一个页面的成本，默认 4.0；
  • cpu_tuple_cost：处理一行的 CPU 成本，默认 0.01；
  • cpu_index_tuple_cost：处理一个索引条目的 CPU 成本，默认 0.005；
  • effective_cache_size：告诉优化器操作系统与数据库大概能缓存多少数据，影响索引扫描的成本估计。

粗略地说，顺序扫描成本主要是“读取表页数 × seq_page_cost + 表行数 × cpu_tuple_cost”。索引扫描成本则包含索引访问成本、回表页面数 × random_page_cost，以及元组处理成本。位图扫描多了构建位图的 CPU 与内存成本，但它能把多个行合并到页面级别，降低回表页数。

当条件匹配的行越来越多时，索引逐行回表的随机 I/O 成本会超过顺序扫描；在尚未达到全表扫描临界点之前，位图扫描往往成为最低成本路径。

构造可复现的执行计划实验 ​

下面的 SQL 可以直接在测试库中运行。示例使用 pg_sleep 不合适，这里通过填充不同分布的数据来观察算子切换。

sql
-- 建议在测试库中执行
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status int NOT NULL,
    buyer_id int NOT NULL,
    amount numeric(10,2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- 插入 100 万行：status=1 约 90%，status=2 约 9%，status=9 约 1%
INSERT INTO orders(status, buyer_id, amount)
SELECT
    CASE
        WHEN random() < 0.90 THEN 1
        WHEN random() < 0.99 THEN 2
        ELSE 9
    END,
    (random() * 100000)::int,
    round((random() * 1000)::numeric, 2)
FROM generate_series(1, 1000000);

CREATE INDEX idx_orders_status ON orders(status);

-- 必须分析表，否则优化器缺少列分布与页面统计
ANALYZE orders;

先查询极少量的异常订单：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS ON)
SELECT id, amount
FROM orders
WHERE status = 9;

典型计划会使用 Index Scan 或 Bitmap Heap Scan。如果 status = 9 的行非常分散，优化器可能更倾位图扫描；如果表很小或缓存效果很好，也可能直接选择顺序扫描。

再查询约 9% 的订单：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS ON)
SELECT id, amount
FROM orders
WHERE status = 2;

这一选择率通常容易触发 Bitmap Heap Scan：

text
Bitmap Heap Scan on orders
  Recheck Cond: (status = 2)
  ->  Bitmap Index Scan on idx_orders_status
        Index Cond: (status = 2)

当查询约 90% 的数据时：

sql
EXPLAIN (ANALYZE, BUFFERS, COSTS ON)
SELECT id, amount
FROM orders
WHERE status = 1;

优化器通常会选择 Seq Scan，因为读取绝大多数堆页时，顺序扫描比通过索引大量回表更便宜。

random_page_cost 为什么会改变扫描算子 ​

机械盘时代，随机寻道明显慢于顺序读，因此默认 random_page_cost = 4.0 有其历史合理性。在 SSD、云盘以及数据大部分命中缓存的环境中，随机读与顺序读的差距已经缩小。如果仍使用偏高的随机读成本，优化器可能高估索引回表代价，从而过度倾向 Seq Scan。

可以在会话级调整参数进行对比：

sql
-- 模拟机械盘或随机 I/O 代价较高的环境
SET random_page_cost = 4.0;

EXPLAIN (COSTS ON)
SELECT id, amount FROM orders WHERE status = 2;

-- 模拟 SSD / 高缓存命中环境
SET random_page_cost = 1.1;

EXPLAIN (COSTS ON)
SELECT id, amount FROM orders WHERE status = 2;

观察输出中的 cost= 变化，比只看是否走索引更重要。若降低 random_page_cost 后，位图扫描或索引扫描成本下降并被选中，说明之前的成本估计偏保守。但不要把参数全局改成 1.0 后就认为问题解决：如果业务表很大、缓存命中率低、存储延迟抖动明显，过低估值会让大量本该顺序扫描的 SQL 走索引，造成更严重的 I/O 放大。

更稳妥的做法是结合存储类型设置：普通 SSD 可考虑 1.1~2.0，云盘根据基准压测结果决定；同时合理设置 effective_cache_size，通常可设为主机可用内存的 50%~75%，但不应超过操作系统可用于缓存的实际范围。

Bitmap Scan 的两个关键细节 ​

第一，位图扫描可能出现 Recheck Cond。这是因为位图在内存不足时会从“逐行位图”降级为“逐页有损位图”。此时只记录哪些页面可能包含匹配行，回表后必须重新检查条件，因此计划中会出现：

text
Rows Removed by Index Recheck: ...

如果该值很高，应关注 work_mem 是否过小。可以在会话或业务角色级别适度增加：

sql
-- 不要盲目全局调大，应按并发数与排序/哈希需求评估
SET work_mem = '16MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM orders WHERE status IN (2, 9);

第二，位图扫描可以组合多个索引。优化器可能分别在两个索引上生成位图，再进行 BitmapOr 或 BitmapAnd：

sql
CREATE INDEX idx_orders_buyer ON orders(buyer_id);
CREATE INDEX idx_orders_amount ON orders(amount);

EXPLAIN (COSTS ON)
SELECT id, amount
FROM orders
WHERE buyer_id = 123
   OR amount > 950;

但多索引位图组合并不总是比复合索引高效。如果某组条件长期固定出现，例如 status + created_at 联合过滤，应优先考虑与谓词顺序、范围查询匹配的复合索引。

用系统视图验证统计信息与 I/O 假设 ​

执行计划异常时，先确认统计信息是否陈旧：

sql
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

大量 n_dead_tup、长期未自动分析、批量导入后未执行 ANALYZE，都会让选择率估计失真。还可以查看索引使用情况：

sql
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

如果 idx_scan 长期为 0，不一定代表索引无用，可能是查询选择率太高、成本参数不合适，或索引列被函数、隐式类型转换破坏。

避坑总结：三个高频错误与修正方法 ​

  1. 只凭“有没有走索引”判断好坏。 常见现象是查出表中 80% 的数据，却通过索引产生大量随机回表，实际执行时间比 Seq Scan 更长。修正方法是使用 EXPLAIN (ANALYZE, BUFFERS) 对比 actual time、共享块读取数与返回行数，而不是强制 SET enable_seqscan = off。

  2. 建索引后忘记 ANALYZE，导致计划仍异常。 批量写入或迁移数据后，优化器可能仍使用旧统计信息，表现为估算行数与实际行数差几个数量级。修正方法是在大导入后执行 ANALYZE 表名，必要时调整 autovacuum 的 analyze_scale_factor。

  3. 在所有环境机械套用默认 random_page_cost=4.0。 SSD 或高缓存命中环境中，可能高估索引扫描成本，使中等选择率查询坚持全表扫描；但直接改成 0.1 又会诱发大量低效索引回表。修正方法是先按存储基准与缓存命中率设置合理区间，再用典型 SQL 的成本和实际耗时验证。

  4. 忽视有损位图导致的回表复核。 当计划中出现 Lossy Bitmap 或大量 Rows Removed by Index Recheck，说明 work_mem 不足，位图从行级降级为页级。修正方法是针对复杂查询会话或角色级调大 work_mem，并结合并发连接数评估总内存，而不是全局无限放大。

理解 Seq Scan 与 Bitmap Scan 的关键，不是背诵“多少比例走索引”，而是看懂选择率、回表页数、随机 I/O 与统计信息如何共同进入成本模型。只要能把 EXPLAIN 中的估算行数、实际行数、缓冲区读取与成本参数对应起来，绝大多数扫描路径异常都可以被稳定定位。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。