44 何时创建索引

索引在被索引列上进行过滤、排序或连接时很有帮助；它们会占用磁盘空间，并拖慢 INSERT/UPDATE/DELETE；对于小表，规划器可能无论如何都会执行顺序扫描。所以问题不是“该列是否应该有索引？”——而是“这个查询需要索引吗，哪一个能帮上忙？”答案来自 EXPLAIN 和 pg_stat_user_indexes。

本章要点

• 基本权衡：索引 = 读取更快、写入更慢、占用更多磁盘空间
• 哪些查询会受益（等值、范围、连接、ORDER BY）
• 为什么低基数列很少能从普通 B-树中受益
• 使用 pg_stat_user_indexes 检测哪些索引未被使用
• ANALYZE 在保持统计信息最新方面的作用

示例

-- Compare plans: without an index vs. with one
DROP INDEX IF EXISTS fruits_name_idx;
EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE name = 'apricot';

CREATE INDEX fruits_name_idx ON fruits (name);
ANALYZE fruits;   -- refresh planner statistics

EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE name = 'apricot';

-- Range queries also benefit
EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE id BETWEEN 2 AND 4;

-- Index usage stats — which indexes is the database actually using?
SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC
LIMIT 10;

B-树索引在以下情况下有帮助：

• 被索引列上的等值查找（WHERE x = ?）。
• 范围扫描（WHERE x BETWEEN ? AND ?、WHERE x < ?）。
• 在被索引列上连接（JOIN on parent.id = child.parent_id）。始终为外键列建立索引。
• 与索引列及其方向匹配的 ORDER BY。
• 锚定前缀 LIKE（LIKE 'abc%'）。

在以下情况下没有帮助（或帮助很小）：

• 查询无论如何都会扫描表中的大部分数据（不存在通用的百分比阈值；行宽、相关性、缓存状态和代价都很重要）。
• 列的非重复值很少（is_active boolean——索引无法帮助常见分支）。
• 条件对列应用了函数（WHERE lower(email) = ...）——除非你有一个表达式索引。
• 表非常小（顺序扫描比有额外开销的索引查找更快）。

在小表上，即使存在索引，规划器也经常更倾向于使用 Seq Scan——因为直接读取一两页中的整张表，比通过索引查找更便宜。这是一个特性，而不是缺陷。

ANALYZE table 会更新规划器用来选择计划的统计信息。大量数据变更后请运行它；自动清理（autovacuum）会根据行变更阈值自动运行，但有时仍需要手动运行。

运行示例

运行方式（需要 fruits）：

$ psql -f source/when-to-index.sql postgres
DROP INDEX
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on fruits  (cost=0.00..1.10 rows=1 width=36)
   Filter: (name = 'apricot'::text)
(2 rows)

CREATE INDEX
ANALYZE
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on fruits  (cost=0.00..1.10 rows=1 width=12)
   Filter: (name = 'apricot'::text)
(2 rows)
 ...

常见陷阱

• 为将来“可能”运行的查询添加索引。每个索引都会让每次写入都变慢。请根据真实工作负载建立索引。
• 某些索引是另一个索引的子集。如果存在 (a, b)，对于 a 的等值查找，(a) 就是多余的。同一个前导列也能支持范围扫描。在认定某个索引冗余前，请检查大小、唯一性、谓词和工作负载。
• 对被索引列使用函数会阻止使用索引：WHERE upper(name) = 'X' 会忽略 name 上的索引——请使用表达式索引或改写查询。
• 随时间增长而膨胀的索引：定期执行 REINDEX CONCURRENTLY 可以让它们保持紧凑。

下载

SQL 文件：when-to-index.sql · 全部示例文件 · 准备工作