2242 字
11 分钟
PostgreSQL 索引与查询优化入门
2026-11-15

先说清楚:慢查询到底慢在哪#

上一篇用 psql 常用命令与操作实战 已经能熟练地连库、查表、跑 SQL 了。但真实项目里最常遇到的问题不是「不会写 SQL」,而是「SQL 写出来了,一跑就等半天」。数据量小的时候什么都快,表里攒到几十万、上百万行之后,没有索引的查询就会从毫秒级劣化到秒级。

这篇的主线很明确:先学会看执行计划,再学会建对索引,最后形成一套排查慢查询的标准动作。优化的前提是定位,没有定位的「优化」基本都是碰运气。如果你的 PostgreSQL 还没装好,先回 PostgreSQL 安装与初始配置 把环境跑起来。

索引优化的第一原则:先测量,再动手。EXPLAIN ANALYZE 的输出就是你的测量仪。

没有索引时,PostgreSQL 在做什么#

准备一张测试表,灌入一百万行数据,后面的例子都基于它:

CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
amount NUMERIC(10, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO orders (user_id, status, amount, created_at)
SELECT
(random() * 10000)::bigint,
(ARRAY['paid', 'pending', 'cancelled', 'refunded'])[1 + (random() * 3)::int],
(random() * 1000)::numeric(10, 2),
now() - (random() * 365) * INTERVAL '1 day'
FROM generate_series(1, 1000000);

查某个用户的订单:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234;

输出大致是这样:

Seq Scan on orders (cost=0.00..20927.00 rows=100 width=40) (actual time=0.052..118.431 rows=98 loops=1)
Filter: (user_id = 1234)
Rows Removed by Filter: 999902
Planning Time: 0.083 ms
Execution Time: 118.512 ms

关键词是 Seq Scan(顺序扫描):PostgreSQL 把一百万行挨个读了一遍,只为找出其中一百行。表越大,这个数字越难看。这就是索引要解决的问题。

看懂 EXPLAIN 输出的几个关键点#

EXPLAIN 只给预估计划,EXPLAIN ANALYZE 会真正执行并给出实际耗时,日常排查用后者。看输出时抓四个重点:

字段含义怎么看
Scan 类型Seq Scan / Index Scan / Index Only Scan / Bitmap Heap Scan大表出现 Seq Scan 且过滤后行数很少,就是缺索引信号
cost优化器估算的相对成本,格式为 启动成本..总成本只作相对比较,不等于毫秒
rows预估返回行数与 actual rows 差太多,说明统计信息过期
actual time实际执行时间,格式为 首行时间..全部行时间这才是真实耗时

还有两个高频节点要认识:

  • Index Only Scan:需要的列全在索引里,连表都不用回,是最快的常见形态。
  • Bitmap Heap Scan:命中行数中等时,PostgreSQL 先用索引收集行位置,再批量回表取数据,比逐行 Index Scan 更划算。

如果预估 rows 和实际 rows 差了几个数量级,先执行一次统计信息更新再判断:

ANALYZE orders;

B-tree 索引:默认且万能的选择#

给 user_id 建索引再查一次:

CREATE INDEX idx_orders_user_id ON orders (user_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234;

计划会变成 Index Scan 或 Bitmap Heap Scan,耗时通常直接掉到 1 毫秒以内。CREATE INDEX 默认建的就是 B-tree 索引,它支持等值、范围、排序和前缀匹配,覆盖 95% 的日常场景:

-- 等值与范围都走索引
SELECT * FROM orders WHERE user_id = 1234 AND created_at >= '2026-01-01';
-- 排序也能用上索引(索引本身就是有序的)
SELECT * FROM orders WHERE user_id = 1234 ORDER BY id DESC LIMIT 20;

其他索引类型了解即可,按需选用:

索引类型适合场景典型语法
B-tree等值、范围、排序,默认选择CREATE INDEX ... ON t (col)
Hash仅等值查询,优势不明显,极少用USING hash (col)
GINJSONB、数组、全文检索的包含查询USING gin (col)
GiST几何、范围类型、相似度搜索USING gist (col)
BRIN超大表且物理顺序与列值强相关(如按时间追加)USING brin (col)

联合索引:列的顺序比数量重要#

业务查询很少只按一列过滤。给 (user_id, created_at) 建联合索引:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

联合索引遵循最左前缀原则:查询条件必须从最左列开始连续使用,索引才完全生效。

-- 用得上:先等值定位 user_id,再按 created_at 范围扫
SELECT * FROM orders WHERE user_id = 1234 AND created_at >= '2026-06-01';
-- 用得上:只有最左列
SELECT * FROM orders WHERE user_id = 1234;
-- 用不上这个索引:跳过了最左列
SELECT * FROM orders WHERE created_at >= '2026-06-01';

经验法则:等值列在前、范围列在后、排序列尽量贴合。把选择性最高( distinct 值最多)的列放前面通常也更划算。

进阶索引:部分索引、表达式索引与覆盖索引#

部分索引:只给需要的数据建索引#

如果业务上 90% 的查询只关心未取消的订单,没必要把全部行都塞进索引:

CREATE INDEX idx_orders_active_user
ON orders (user_id)
WHERE status <> 'cancelled';

索引体积更小、维护成本更低。注意查询条件必须能被优化器证明蕴含索引的 WHERE 条件,否则用不上。

表达式索引:函数查询也能走索引#

按邮箱小写匹配是经典翻车点:

-- 这样写,email 上的普通索引用不上
SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- 建表达式索引后才走索引
CREATE INDEX idx_users_lower_email ON users (lower(email));

覆盖索引:用 INCLUDE 免回表#

CREATE INDEX idx_orders_user_cover
ON orders (user_id) INCLUDE (status, amount);

查询只需要 user_id、status、amount 时就是 Index Only Scan,省掉回表开销。代价是索引变胖,写入时维护成本上升,高频更新的列不要往里塞。

WARNING

索引不是越多越好。每多一个索引,INSERT 和 UPDATE 都要多维护一份数据结构,写入吞吐会下降。给低选择性列(如只有两三个取值的 status)单独建索引通常收益很低,优化器甚至会直接放弃使用它。先确认查询真的需要,再建。

慢查询排查的标准流程#

把上面的知识串成一套可重复执行的动作:

  1. 抓到慢 SQL:开启慢查询日志,或从 pg_stat_statements 扩展里按总耗时排序找元凶。
  2. 复现并看计划:EXPLAIN (ANALYZE, BUFFERS) 看实际耗时和读了多少数据块。
  3. 对症下药:Seq Scan 且过滤性强 → 补索引;rows 预估严重偏差 → ANALYZE;索引建了不用 → 检查类型转换、函数包裹列、隐式拼接等问题。
  4. 验证效果:改完再跑一次 EXPLAIN ANALYZE,前后数字留档对比。

pg_stat_statements 需要在 postgresql.conf 里预加载,配置方法在 PostgreSQL 安装与初始配置 里有说明。开启后常用的一条排查 SQL:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

更系统的监控与告警思路,放在系列最后一篇 数据库性能监控与常见故障排障 里统一讲。

索引的日常维护#

索引会随着大量更新和删除产生膨胀,查询逐渐变慢。常用维护动作:

-- 查看每个索引的大小,揪出异常膨胀的
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- 在线重建索引,不锁表写操作(会短暂占双倍空间)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
-- 查看从未被使用过的索引,考虑删除以减轻写入负担
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

注意 pg_stat_user_indexes 的统计会随服务重启清零,判断「从未使用」前先确认统计周期足够长,别把低频但关键的索引误删了。

常见问题#

Q:索引建了,为什么查询还是 Seq Scan? 返回行数占全表比例较高时(比如超过 10%–20%),优化器认为顺序扫描更便宜,这是正常决策。另外检查统计信息是否过期、查询里是否有对列做函数运算或类型转换。

Q:主键还需要单独建索引吗? 不需要。主键会自动创建唯一 B-tree 索引。但要注意:联合主键同样遵循最左前缀,单查第二列用不上。

Q:CREATE INDEX 会锁表吗? 普通 CREATE INDEX 会阻塞写入。生产环境大表建索引用 CREATE INDEX CONCURRENTLY,代价是更慢且不能在事务块里执行:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

Q:LIKE ‘%keyword%’ 能走索引吗? 普通 B-tree 不行(前缀不确定)。全文检索场景应使用 GIN 索引配合 pg_trgm 扩展或 tsvector,不要硬套 B-tree。

小结#

  • 慢查询先看 EXPLAIN ANALYZE,Seq Scan 加高过滤性是大表缺索引的典型信号。
  • B-tree 是默认选择;联合索引记住等值在前、范围在后、最左前缀。
  • 部分索引、表达式索引、覆盖索引分别解决「只查一部分数据」「函数查询」「免回表」三类问题。
  • 索引有写入代价,宁可少而准,不要一股脑全建;定期清理未使用索引、重建膨胀索引。

至此 PostgreSQL 部分告一段落,下一篇进入 MySQL:MySQL 安装与初始配置。

PostgreSQL 索引与查询优化入门
https://www.anexns.cloudns.org/posts/postgresql-index-optimization/
作者
Anexns
发布于
2026-11-15
许可协议
CC BY-NC-SA 4.0