Menu

PostgreSQL 索引教程:CREATE INDEX 与索引类型

CREATE INDEX 默认创建 B-tree 索引,让 PostgreSQL 不必读整张表就能找到行。其他类型(GIN、GiST、BRIN、hash)适用于 JSONB、数组、全文搜索、范围类型和超大表。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

CREATE INDEX name ON table (column); 创建一个索引,让 PostgreSQL 不必读取整张表就能找到匹配的行。默认类型是 B-tree,它支持 =、<、>、BETWEEN、IN、IS NULL 和 ORDER BY。用 USING 可以选择其他类型:gin、gist、spgist、brin 或 hash。

计划的第一行是 Index Scan using users_email_idx on users:PostgreSQL 在索引中查找这个值,只读取匹配的那一行。去掉 CREATE INDEX 那一行再运行一次,会看到 Seq Scan on users,它会读取全部 10,000 行。

对于只有几十行的表,即使有索引,查询规划器也常常选择顺序扫描:读几个页面反而更便宜。如何阅读这些计划,见 EXPLAIN ANALYZE。

选择索引类型

类型适用场景
B-tree(默认)等值、范围、排序、LIKE 'prefix%'(需要 C 排序规则或 text_pattern_ops)
GIN由多个部分组成的值:jsonb(@>、?)、数组(@>、&&)、全文搜索的 tsvector
GiST几何类型、范围类型(&&,重叠)、全文搜索、最近邻 ORDER BY ... <->
SP-GiST能划分成互不重叠区域的数据:点、IP 地址、文本前缀
BRIN值的顺序和物理存储顺序一致的超大表,比如只追加的 created_at
Hash只支持等值;B-tree 能做同样的事而且更多,所以很少选它

在 jsonb 列上建 GIN 索引,包含查询就能用上索引:

计划显示在 GIN 索引上做了 Bitmap Index Scan,结果交给 Bitmap Heap Scan 读取匹配的行。它的操作符见 JSONB 页面。

多列索引

多列 B-tree 索引先按第一列排序,在第一列的每个值内部再按第二列排序。它适用于按前导列筛选的查询,或者按前导列及其后几列筛选的查询。只按第二列筛选时,它帮不上什么忙,所以把总是用 = 筛选的列放在最前面。

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE(PostgreSQL 11+)把额外的列存进索引,但不按它们排序:CREATE INDEX ON orders (customer_id) INCLUDE (total);。只读取这些列的查询就可以只靠索引得到结果,也就是 Index Only Scan。

唯一索引和部分索引:只对部分行施加规则

CREATE UNIQUE INDEX 拒绝重复的值。部分索引加上一个 WHERE,只为符合条件的行建索引。它更小,还能让唯一性规则只作用于一部分行:

两行都被接受,因为其中只有一行没有被删除。再插入一行邮箱相同且未删除的行,就会报错 duplicate key value violates unique constraint "accounts_email_live"。只有当查询的 WHERE 能推出索引的条件时(这里是 WHERE NOT deleted),查询才会使用部分索引。

表达式索引

索引可以存储一个表达式的结果。查询必须写出相同的表达式才能用上它,而 email 上的索引对 WHERE lower(email) = ... 没有帮助:

计划用上了 users_email_lower_idx,因为 WHERE 中完全照写了 lower(email)。表达式必须是不可变的,所以 now() 或者依赖某个设置的函数会被拒绝。

在线上数据库中查看、创建和删除索引

pg_indexes 列出每个索引,以及能重建它的语句:

主键和 UNIQUE 约束各自创建了索引:users_pkey 和 users_email_key。在 psql 中,\d users 会在表的列下面显示同样的列表。

对于有写入的表,创建和删除索引时要加上 CONCURRENTLY,这样不会阻塞写入。这些形式不能在事务块中运行。并发构建失败时会留下一个 INVALID 索引,必须删掉再重新创建:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

常见问题

PostgreSQL 会自动创建索引吗?

只会为 PRIMARY KEY、UNIQUE 和排除约束创建。外键列不会有索引,所以在它上面做连接或者从被引用表删除行时,要自己加索引。

为什么 PostgreSQL 不用我建的索引?

常见原因:表很小,整张读一遍更便宜;查询返回了很大比例的行;WHERE 把列包在了索引没有的函数或类型转换里;或者统计信息过时了(运行 ANALYZE)。EXPLAIN 会显示选择了哪个计划。

索引多少个算太多?

没有固定的数字。每个索引都会加快某些读取,同时拖慢这张表上的每次 INSERT 和大多数 UPDATE,还会占用磁盘空间。pg_stat_user_indexes.idx_scan 显示每个索引被使用的次数,一直是 0 的索引就可以删掉。

CREATE INDEX 会锁表吗?

普通的 CREATE INDEX 在完成之前会阻塞这张表上的 INSERT、UPDATE 和 DELETE,不过读取可以继续。CREATE INDEX CONCURRENTLY 在构建期间允许写入,耗时更长,而且不能在事务块中运行。

Coddy 编程语言插图

和 Coddy 一起学编程

开始