PostgreSQL / 系统课时

判断索引是否有效

判断索引是否有效

本课解决“索引已经创建,但到底有没有加速查询”的问题。你将结合执行计划、数据选择性和缓冲区读取量,判断索引是否被使用以及是否值得保留。

学习目标

  • 使用 EXPLAIN ANALYZE 查看真实执行路径
  • 根据选择性判断顺序扫描是否合理
  • 比较索引前后的扫描节点和缓冲区开销

先看执行计划,不凭感觉判断

EXPLAIN 展示优化器预计采用的计划,EXPLAIN ANALYZE 会真实执行 SQL,并补充实际行数和耗时。排查索引时,应重点观察扫描类型、实际返回行数、过滤掉的行数以及缓冲区访问量。常见扫描节点包括:

  • Seq Scan:顺序读取表中的数据页
  • Index Scan:沿索引定位并回表读取
  • Index Only Scan:主要从索引获取结果,是否回表取决于可见性映射
  • Bitmap Index Scan 与 Bitmap Heap Scan:先收集多个位置,再批量访问数据页

比较创建索引前后的计划

下面构造 10 万行数据,并查询一个选择性较高的分类值。示例输出中的缓冲区数量会因版本和环境不同而变化,因此以核心节点变化为判断标准。

sql
-- 重建演示表,保证两次计划可以直接比较
DROP TABLE IF EXISTS demo_events;

CREATE TABLE demo_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id integer NOT NULL,
    payload text NOT NULL
);

-- 1000 个分类平均分布,每个分类约 100 行
INSERT INTO demo_events (category_id, payload)
SELECT
    g % 1000,
    repeat('x', 50)
FROM generate_series(1, 100000) AS g;

ANALYZE demo_events;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, payload
FROM demo_events
WHERE category_id = 42;

-- 建索引后再次收集统计信息并执行同一查询
CREATE INDEX idx_demo_events_category
ON demo_events (category_id);

ANALYZE demo_events;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, payload
FROM demo_events
WHERE category_id = 42;

预期结果

text
创建索引前:
核心节点为 Seq Scan on demo_events。
实际返回约 100 行,并过滤其余大部分行。

创建索引后:
核心节点通常为 Bitmap Heap Scan 或 Index Scan。
下层出现 idx_demo_events_category,并且访问的数据页明显减少。

具体扫描类型可能因 PostgreSQL 版本、缓存状态和成本参数不同而变化,但索引后不应继续扫描绝大多数无关数据页。

选择性决定索引收益

选择性表示条件能排除多少数据。查询 category_id = 42 只返回约千分之一的数据,通常适合索引。

如果条件返回表中大部分行,例如布尔列中 90% 都是 true,顺序扫描可能更快。因为随机回表读取大量数据页的成本,可能高于连续扫描整张表。

因此,“出现 Seq Scan”不等于索引无效。要结合返回比例、表大小和实际耗时判断。

检查估算是否准确

执行计划中的预计行数与实际行数差距很大时,优化器可能做出错误选择。先执行 ANALYZE table_name 更新统计信息,再重新查看计划。

对于数据分布高度倾斜或多列强相关的场景,可以提高统计目标或创建扩展统计信息,但应先确认问题确实来自估算偏差。

识别无效或重复索引

索引长期未被扫描、与其他索引完全重复,或只服务极低频查询时,可能不值得保留。可以查看 pg_stat_user_indexes 中的 idx_scan,但该计数会在统计重置或实例重启后变化,不能单独作为删除依据。

删除索引前应覆盖业务高峰周期,并确认它没有用于唯一性约束、外键关联查询、定时任务或低频关键报表。

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

目录与阅读设置

阅读设置

17

记录一个想法

判断索引是否有效

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

登录哪都通