朋友们好，我是有9年Python后端开发经验的老码农。今天想和大家聊聊PostgreSQL索引优化这个老生常谈但又极其重要的话题。

相信很多后端兄弟都遇到过这样的场景：一个查询昨天还跑得飞快，今天就慢得像蜗牛；明明建了索引，执行计划却显示全表扫描。别急，这很可能就是索引出了问题。

通过这些年踩过的坑，我总结了5个最常见也最经典的慢查询案例，每个都有真实的场景、可复现的SQL和具体的优化方案。咱们不搞理论堆砌，直接上实战！

案例1：缺少复合索引导致的“全表扫描”悲剧

问题场景

去年我们项目用了Hangfire做定时任务，hangfire.set表很快积累了500多万条记录。一个简单的查询突然从几毫秒飙到1.3秒：

SELECT "value"
FROM "hangfire"."set" 
WHERE "key" = 'console:848ac0fc791286418' 
ORDER BY "id" 
LIMIT 1 OFFSET 0;

EXPLAIN分析（优化前）

Limit  (cost=0.43..233.35 rows=1 width=114) (actual time=1317.599..1317.600 rows=1 loops=1)
  Output: value, id
  Buffers: shared hit=1226557 read=183314 written=317
  ->  Index Scan using set_pkey on hangfire.set  (cost=0.43..365210.11 rows=1568 width=114) (actual time=1317.597..1317.598 rows=1 loops=1)
        Output: value, id
        Filter: (set.key = 'console:848ac0fc791286418'::text)
        Rows Removed by Filter: 5115528
        Buffers: shared hit=1226557 read=183314 written=317

关键问题：虽然用上了主键索引，但实际扫描了超过500万行数据，因为缺少针对(key, id)的复合索引。

优化方案

-- 创建复合索引（注意用CONCURRENTLY避免锁表）
CREATE INDEX CONCURRENTLY set_key_id_idx ON "hangfire"."set" (key, id);

优化后效果

查询时间从 1317毫秒 降到 0.037毫秒，提升了 35000倍！执行计划变为高效的索引扫描：

Index Scan using set_key_id_idx on hangfire.set (cost=0.56..1.68 rows=1 width=114)
  Index Cond: (set.key = 'console:848ac0fc791286418'::text)

个人经验

• 最左前缀原则：复合索引(key, id)既能满足WHERE key='xxx'的过滤，又能直接提供ORDER BY id的排序
• 索引顺序很重要：如果查询是WHERE id > 100 AND key='xxx'，索引应该是(id, key)吗？不！过滤性强的字段放前面，所以还是(key, id)更优

案例2：函数操作让索引“瞬间失效”

问题场景

我们有个订单表orders，order_date字段上建了索引。业务需要查询某一天的订单：

SELECT * FROM orders WHERE DATE(order_date) = '2024-01-01';

结果这个查询巨慢无比，明明order_date有索引啊！

问题分析

这就是典型的“函数操作导致索引失效”。PostgreSQL的B-tree索引是按照原始值排序的，当你对列使用函数（DATE()、LOWER()、EXTRACT()等），索引结构就被破坏了。

优化方案

方案一：改写成范围查询（推荐）

SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2024-01-02';

方案二：创建函数索引（按需使用）

CREATE INDEX idx_orders_order_date_date ON orders (DATE(order_date));

性能对比

查询方式	执行计划	查询时间
WHERE DATE(order_date) = '2024-01-01'	全表扫描	1200ms
WHERE order_date >= '2024-01-01' AND order_date < '2024-01-02'	索引范围扫描	15ms

个人踩坑

• LIKE '%keyword%'同样会让索引失效，这种场景可以考虑pg_trgm扩展+GIN索引
• 隐式类型转换也是“隐形杀手”：WHERE phone = 13800138000（phone是TEXT类型）会触发类型转换，索引失效

案例3：覆盖索引——减少“回表”的神器

问题场景

用户中心有个高频查询：根据用户ID查用户名和头像

sql

SELECT username, avatar FROM users WHERE user_id = 123;

user_id上有主键索引，但执行计划显示：

plaintext

Index Scan using users_pkey on users (cost=0.29..8.31 rows=1 width=72)
  Index Cond: (user_id = 123)

虽然用了索引，但还是要“回表”（从索引回到主表）取username和avatar字段。

优化方案：覆盖索引

-- 使用INCLUDE子句包含查询所需的其他列
CREATE INDEX idx_users_covering ON users(user_id) INCLUDE (username, avatar);

优化后的执行计划：

Index Only Scan using idx_users_covering on users (cost=0.15..2.37 rows=1 width=72)
  Index Cond: (user_id = 123)

看到没？Index Only Scan，直接从索引返回数据，避免了回表的随机IO。

性能提升数据

表格

场景	IO次数	查询延迟
普通索引+回表	平均5次随机IO	0.8-1.2ms
覆盖索引	1次顺序IO	0.1-0.2ms

使用建议

• 覆盖索引适用于：1）查询字段固定 2）查询频率高 3）表数据量大
• 不要无脑加INCLUDE：宽字段会显著增大索引体积，影响写入性能

案例4：部分索引——优化历史数据的“精准打击”

问题场景

电商系统有个订单表orders，90%的订单是“已结算”（billed = true），但我们经常需要查“未结算订单”：

SELECT * FROM orders WHERE billed is not true AND amount > 5000;

问题分析

为billed字段建普通索引会包含所有行，但90%的“已结算”订单我们很少查询，造成索引空间浪费和性能下降。

优化方案：部分索引

CREATE INDEX orders_unbilled_idx ON orders(order_id, amount) 
WHERE billed is not true;

这个索引只包含未结算订单，体积减少90%，查询效率却大幅提升。

优化效果

索引类型	索引大小	查询速度	适用场景
普通索引	850MB	45ms	需要查全部状态
部分索引	85MB	8ms	只查未结算订单

个人经验

• 部分索引适合：1）数据分布不均匀 2）只查询特定子集 3）数据变更不频繁
• 千万别用部分索引替代分区表：曾经见过有人为每个类别建一个部分索引，结果优化器选择索引的时间比查询还长

案例5：定期重建索引——清除“索引碎片”

问题场景

有个日志表每天大量INSERT和DELETE，3个月后发现相关查询越来越慢。检查索引健康度：

-- 安装pgstattuple扩展
CREATE EXTENSION pgstattuple;

-- 检查索引碎片
SELECT * FROM pgstatindex('idx_logs_created_at');

结果：avg_leaf_density（平均叶子页密度）只有55%，leaf_fragmentation（叶子页碎片率）高达35%。

问题分析

频繁的DELETE操作导致索引页产生大量“空洞”（死元组），索引虽然很大但有效数据很少。

优化方案：定期重建索引

方案一：REINDEX（生产环境慎用）

-- 会锁表，影响业务
REINDEX INDEX idx_logs_created_at;

方案二：CONCURRENTLY重建（推荐）

-- 无锁重建，但时间更长、资源消耗更大
REINDEX INDEX CONCURRENTLY idx_logs_created_at;

方案三：新建替换（最安全）

-- 1. 新建索引
CREATE INDEX CONCURRENTLY idx_logs_created_at_new ON logs(created_at);

-- 2. 删除旧索引
DROP INDEX CONCURRENTLY idx_logs_created_at;

-- 3. 重命名
ALTER INDEX idx_logs_created_at_new RENAME TO idx_logs_created_at;

重建前后对比

指标	重建前	重建后
索引大小	2.3GB	1.1GB
查询延迟	120ms	25ms
叶子页密度	55%	92%

最佳实践

1. 监控指标：每周检查pg_stat_user_indexes的idx_scan和idx_tup_read
2. 重建阈值：avg_leaf_density < 60% 或 leaf_fragmentation > 30%
3. 执行时间：业务低峰期，凌晨2-4点
4. 资源准备：确保有足够的磁盘空间（新+旧索引同时存在）

个人经验总结与避坑指南

干了9年后端，关于索引优化我总结了这几条血泪教训：

1. 索引不是越多越好

• 每增加一个索引，INSERT/UPDATE/DELETE成本就增加一份
• 我见过一张表建了15个索引，写入速度比没索引还慢
• 黄金法则：单表索引不超过5-8个，低频查询优先考虑业务层缓存

2. 联合索引的顺序是门学问

-- 错误示例：把选择性低的放前面
CREATE INDEX idx_bad ON users(gender, user_id);

-- 正确示例：选择性高的放前面
CREATE INDEX idx_good ON users(user_id, gender);

记住：最左前缀原则，但也要考虑实际查询模式。如果95%的查询都是WHERE gender='男' AND city='北京'，那么(gender, city)可能是更好的选择。

3. 定期维护比临时救火重要

• 每月一次：检查索引使用率（pg_stat_user_indexes）
• 每季度一次：分析慢查询日志，优化低效索引
• 每半年一次：重建高碎片率索引

4. 测试环境要模拟生产数据量

最坑的一次：在测试环境（1万条数据）索引表现完美，上线到生产（1000万条）直接崩了。数据量差异会导致完全不同的执行计划。

5. 监控要到位，不能靠猜

• 配置慢查询日志（log_min_duration_statement = 1000）
• 使用pg_stat_statements追踪高频查询
• 设置告警：当索引扫描比例低于70%时触发

互动时间

看完这5个案例，不知道大家有没有类似的踩坑经历？我抛几个问题，欢迎评论区交流：

1. 你们项目中有没有遇到过“明明有索引却不用”的情况？最后是怎么解决的？
2. 对于历史数据的查询优化，除了部分索引，你们还用过哪些好方法？
3. 索引重建时，你们是怎么平衡“业务影响”和“优化效果”的？

每个团队的技术栈和业务场景不同，优化的思路也会不一样。期待听到大家的实战经验！

最后的话

索引优化是个持续的过程，没有一劳永逸的方案。随着业务增长和数据变化，需要定期回顾和调整。

关键是要建立数据驱动的优化机制：监控指标 → 发现问题 → 分析原因 → 实施优化 → 验证效果。

希望今天的分享能给大家带来一些启发。如果你觉得有用，欢迎点赞收藏，也欢迎分享给你的团队成员。

我是9年Python后端老码农，后续还会分享更多数据库优化、系统架构方面的实战经验。保持关注，我们一起成长！