# CREATE INDEX

> 创建普通索引、多列索引、部分索引和表达式索引，并使用适当的锁。

---

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

---

*索引*是一种辅助数据结构，使 PostgreSQL 可以按列值查找行，而无需扫描整张表。主键会自动创建索引。对于其他列，你可以选择：`CREATE INDEX idx ON table (col)`。默认且最常用的索引类型是 B-树，它可以加速等值（`=`）、范围（`<`、`>`、`BETWEEN`）和前缀（`LIKE 'abc%'`）查找，以及 `ORDER BY`。PostgreSQL 还提供 GIN（用于全文检索、数组和 jsonb）、GiST（用于范围和几何数据）、BRIN（用于巨大且天然有序的表）、Hash 和 SP-GiST。

## 本章要点 {#learn}

- 在列或一组列上创建 B-树索引
- 命名和列出索引
- 多列索引的列顺序及其含义
- 部分索引和表达式索引
- 生产环境中的 `CREATE INDEX CONCURRENTLY`

## 示例 {#example}

```sql {num="43.1" caption="创建索引"}
-- Single-column B-tree index
CREATE INDEX IF NOT EXISTS fruits_name_idx ON fruits (name);

-- Multi-column index. Order matters!
-- Useful for queries that filter by customer_id, or by (customer_id, id).
CREATE INDEX IF NOT EXISTS orders_customer_idx
  ON orders_example (customer_id, id);

-- Partial index: only index rows matching a predicate. Tiny and fast.
CREATE INDEX IF NOT EXISTS tasks_open_idx
  ON tasks_example (id) WHERE done = false;

-- Expression index: lets queries that use LOWER(email) hit an index.
-- (using a temp table to avoid clutter)
DROP TABLE IF EXISTS people_example;
CREATE TABLE people_example (id integer PRIMARY KEY, email text);
INSERT INTO people_example VALUES (1, 'Alice@example.com'), (2, 'bob@example.com');
CREATE INDEX people_lower_email_idx ON people_example (lower(email));

-- Use it
SELECT * FROM people_example WHERE lower(email) = 'alice@example.com';

-- List indexes on a table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('fruits', 'orders_example', 'tasks_example', 'people_example')
ORDER BY tablename, indexname;
```

B-树索引是一棵有序的平衡树，支持 `=`、`<`、`>`、`BETWEEN`、`IS NULL`、`ORDER BY` 和 `LIKE 'prefix%'`（锚定前缀）。它是默认类型，在典型模式中约占索引总数的 90%。

多列索引遵循*最左前缀*规则：对于 `(a, b, c)` 上的索引，可以服务于按 `a`、`(a, b)` 或 `(a, b, c)` 过滤的查询，也可以针对只涉及 `b` 或 `(b, c)` 的谓词进行扫描，不过没有前导列限制时，可能需要扫描索引的更大部分。请根据实际的过滤条件和排序需求选择列顺序。PostgreSQL 18 为其中一些情况增加了跳跃扫描优化。

部分索引只为谓词为真的行建立索引。它非常适合“is_active = true”这类查询——索引更小、速度更快，也不必覆盖大多数不需要关注的行。表达式索引可以为转换后的值（`lower(email)`、`(data ->> 'name')`）建立索引。它是让 `WHERE lower(email) = 'x'` 通过索引快速执行的唯一方式。

`CREATE INDEX CONCURRENTLY` 建立索引时不会获取表级写锁。它需要更长时间（对表进行两次遍历），但不会阻塞写入——生产环境中需要这样做。

## 运行示例 {#run}

运行方式（需要前面章节创建的表）：

```text
$ psql -f source/create-index.sql postgres
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
CREATE TABLE
INSERT 0 2
CREATE INDEX
 id |        email
----+---------------------
  1 | Alice@example.com
(1 row)
 ...
```

## 常见陷阱 {#pitfalls}

- 索引过多：插入和许多更新都会创建索引条目；HOT 更新可以避免索引工作，而死索引条目的清理通常会推迟到 vacuum。对于写入繁重的表，实际不用的索引纯属额外开销。
- 单独为低基数列建索引（例如 `boolean is_active`）通常没有帮助——规划器无论如何都会执行顺序扫描。请与另一列组合，或使用部分索引。
- 索引命名不一致会让调试变得痛苦。请采用类似 `tablename_column_idx` 的命名约定。
- 不带 `CONCURRENTLY` 的 `CREATE INDEX` 会获取 `SHARE` 锁：普通读取可以继续，但写入会等待。写入必须继续时，请使用 `CONCURRENTLY`；它不能在事务块内运行，并且有额外的工作和失败处理要求。

> [!TIP] 提示
> `pg_stat_user_indexes` 会显示每个索引的使用频率（`idx_scan`）。请定期检查；如果某个索引连续几周扫描次数为零，就可以考虑删除它。

> [!NOTE] 动手练习
> 只在 `customer_id` 上创建一个索引，再在 `(customer_id, total)` 上创建另一个。查看 `EXPLAIN SELECT * FROM orders_example WHERE customer_id = 1 ORDER BY total;`，看看规划器选择哪一个。删除未使用的那个。

## 下载 {#downloads}

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