43 CREATE INDEX

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

本章要点

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

示例

-- 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 建立索引时不会获取表级写锁。它需要更长时间（对表进行两次遍历），但不会阻塞写入——生产环境中需要这样做。

运行示例

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

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

常见陷阱

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

下载

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