# 何时创建索引

> 根据真实查询选择索引，并检查 PostgreSQL 是否使用它们。

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="44.1" caption="何时创建索引"}
-- 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）会根据行变更阈值自动运行，但有时仍需要手动运行。

## 运行示例 {#run}

运行方式（需要 `fruits`）：

```text
$ 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)
 ...
```

## 常见陷阱 {#pitfalls}

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

> [!TIP] 提示
> 教科书式规则：每个外键列都应建立索引。没有索引的连接很慢，级联删除会扫遍所有数据。Postgres *不会*像对待主键那样自动为外键列建立索引。

> [!NOTE] 动手练习
> 在创建索引前后运行 `EXPLAIN ANALYZE`（它会实际执行查询）进行比较——行数和耗时会说明真相。然后创建一个故意效果很差的索引（例如建在 boolean 列上），观察规划器忽略它。

## 下载 {#downloads}

SQL 文件：[when-to-index.sql](/examples/source/when-to-index.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)