PostgreSQL / 系统课时

理解顺序扫描与索引扫描

理解顺序扫描与索引扫描

存在索引并不代表查询一定使用索引。规划器会比较读取范围、回表成本和数据分布,选择顺序扫描或索引扫描。本课通过高选择性与低选择性条件观察两种选择。

学习目标

  • 区分顺序扫描和索引扫描的工作方式
  • 理解选择率对扫描方式的影响
  • 判断索引未被使用是否真的异常

构造不同选择率的数据

下面创建 100000 行数据,其中绝大多数状态为 PAID,只有少量为 FAILED,并为状态列建立索引。

sql
CREATE TEMP TABLE scan_orders (
    id integer PRIMARY KEY,
    status text NOT NULL,
    amount numeric(10, 2) NOT NULL
);

INSERT INTO scan_orders (id, status, amount)
SELECT
    n,
    CASE WHEN n <= 1000 THEN 'FAILED' ELSE 'PAID' END,
    (n % 1000 + 1)::numeric(10, 2)
FROM generate_series(1, 100000) AS n;

CREATE INDEX idx_scan_orders_status ON scan_orders(status);
ANALYZE scan_orders;

EXPLAIN (COSTS OFF)
SELECT * FROM scan_orders WHERE status = 'FAILED';

EXPLAIN (COSTS OFF)
SELECT * FROM scan_orders WHERE status = 'PAID';

预期结果

text
Index Scan 或 Bitmap Heap Scan
  Index Cond: (status = 'FAILED'::text)

Seq Scan on scan_orders
  Filter: (status = 'PAID'::text)

少量行查询通常使用索引;返回约 99% 数据时,顺序扫描通常更便宜。具体节点可能受版本、缓存和成本参数影响。

顺序扫描的特点

Seq Scan 按数据页顺序读取整张表,再应用过滤条件。它访问的数据多,但读取模式连续,对大范围查询通常很高效。

当条件返回大量行时,即使有索引,逐条索引定位并频繁回表也可能比一次顺序读取更慢。因此,看到顺序扫描不能直接判定为性能问题。

索引扫描的特点

Index Scan 先通过索引找到行位置,再访问堆表获取所需列。它适合返回少量、分散记录的高选择性查询。

如果查询列都包含在索引中,并且可见性条件满足,规划器还可能选择 Index Only Scan,从而减少回表访问。

位图扫描是中间方案

当结果行数不算少,但仍明显小于全表时,PostgreSQL 可能先用索引生成页位置位图,再按数据页批量读取,形成 Bitmap Index Scan 与 Bitmap Heap Scan 组合。

它在随机回表与整表扫描之间折中,因此也是正常选择,不应强制所有查询都变成普通索引扫描。

索引未使用时检查什么

先确认统计信息是否更新,再检查条件返回比例、列类型是否一致、表达式是否阻止索引使用,以及排序或连接是否改变了整体成本。

不要长期通过关闭 enable_seqscan 强迫索引扫描。该参数适合教学或诊断,不能替代正确索引、准确统计信息和合理 SQL。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解顺序扫描与索引扫描

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通