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。
按自己的节奏,确认真正理解。