先说清楚:慢查询到底慢在哪
上一篇用 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: 999902Planning Time: 0.083 msExecution 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) |
| GIN | JSONB、数组、全文检索的包含查询 | 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_userON 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_coverON orders (user_id) INCLUDE (status, amount);查询只需要 user_id、status、amount 时就是 Index Only Scan,省掉回表开销。代价是索引变胖,写入时维护成本上升,高频更新的列不要往里塞。
WARNING索引不是越多越好。每多一个索引,INSERT 和 UPDATE 都要多维护一份数据结构,写入吞吐会下降。给低选择性列(如只有两三个取值的 status)单独建索引通常收益很低,优化器甚至会直接放弃使用它。先确认查询真的需要,再建。
慢查询排查的标准流程
把上面的知识串成一套可重复执行的动作:
- 抓到慢 SQL:开启慢查询日志,或从
pg_stat_statements扩展里按总耗时排序找元凶。 - 复现并看计划:
EXPLAIN (ANALYZE, BUFFERS)看实际耗时和读了多少数据块。 - 对症下药:Seq Scan 且过滤性强 → 补索引;rows 预估严重偏差 →
ANALYZE;索引建了不用 → 检查类型转换、函数包裹列、隐式拼接等问题。 - 验证效果:改完再跑一次 EXPLAIN ANALYZE,前后数字留档对比。
pg_stat_statements 需要在 postgresql.conf 里预加载,配置方法在 PostgreSQL 安装与初始配置 里有说明。开启后常用的一条排查 SQL:
SELECT query, calls, total_exec_time, mean_exec_time, rowsFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 10;更系统的监控与告警思路,放在系列最后一篇 数据库性能监控与常见故障排障 里统一讲。
索引的日常维护
索引会随着大量更新和删除产生膨胀,查询逐渐变慢。常用维护动作:
-- 查看每个索引的大小,揪出异常膨胀的SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))FROM pg_stat_user_indexesORDER BY pg_relation_size(indexrelid) DESC;
-- 在线重建索引,不锁表写操作(会短暂占双倍空间)REINDEX INDEX CONCURRENTLY idx_orders_user_id;
-- 查看从未被使用过的索引,考虑删除以减轻写入负担SELECT relname, indexrelname, idx_scanFROM pg_stat_user_indexesWHERE 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 安装与初始配置。