最近接手一个历史悠久的 PostgreSQL 库线上一个分页接口越跑越慢从最初的 200ms 一直退化到 2 秒开外。翻出慢查询日志定位到一条按用户维度的订单列表 SQL单表数据量已经超过 800 万行执行计划里赫然写着Seq Scan on orders全表扫了一遍又排序。说实话这种问题一点都不新鲜数据量上来了索引没跟上查询就像在图书馆里没有目录却要翻完一整层楼的书架。我建了一个普通的 B-Tree 复合索引后接口查询时间回到了 20ms 左右。事情就这样解决了但真正的核心问题值得好好说清楚PostgreSQL 添加索引是提升查询性能的常用手段但索引怎么建、建什么类型、建了以后会不会被用到这里面有一套完整的逻辑。这篇文章就围绕 PostgreSQL 的索引使用展开会讲清楚索引为什么能提速、B-Tree 之外的索引类型怎么选、复合索引的列顺序怎么定、哪些场景会让索引失效、主键索引和唯一索引一字之差到底差在哪里最后还会聊一聊索引的日常监控和维护。适用人群包括正在被慢查询折磨的 CRUD 开发、需要更深入了解索引行为的 DBA以及刚接触 PostgreSQL 想系统理解索引工作机制的初学者。不夸张地讲把这篇内容消化掉你再去处理慢查询时基本能从瞎猜加索引变成按图索骥出方案。1. 索引提速的本质从全表扫描到命中索引的成本博弈很多人加索引纯粹是加了就快的经验主义但实际遇到索引没被选中的时候就彻底懵了。要真正理解 PostgreSQL 里的索引你得先接受一个观点规划器Planner选不选索引本质是一次成本的数学比较而不是有没有索引的二元判断。1.1 规划器眼里的世界顺序扫描与随机扫描的差价PostgreSQL 的查询规划器在选择执行路径时会基于pg_class.relpages表占用的磁盘页数和reltuples估算行数来估算代价。两个最基础的成本参数是seq_page_cost和random_page_cost默认值分别是 1.0 和 4.0。直白点翻译数据库认为一次随机磁盘读取的成本是顺序读取的 4 倍。为什么会有这个差价因为顺序扫描可以依赖操作系统的预读机制磁盘磁头机械臂一路推过去块与块之间接续读取吞吐量高得多而走索引访问则是跳着读每一条数据行可能散布在不同的数据页上每跳一次就是一次随机 IO。在传统机械硬盘时代随机读比顺序读慢 10 倍都不止默认值 4.0 已经算保守了。哪怕你全是用 NVMe SSD这个比例变成了 1.5 到 2 左右可以考虑调整random_page_cost 1.5或1.1索引扫描仍然不是免费的。这也是我常跟团队说的一句话PostgreSQL 不建索引是在按牌理出牌它基于成本模型做了判断。如果建了一个索引规划器算出来走索引比顺序扫描还贵那这个索引在数据量不大、选择性不高的场景下就是多余的。1.2 B-Tree 加速器的人话解读B-Tree 可以说是数据库索引的默认答案。你可以把它想象成一本按拼音部首编好的字典不是从第一页翻起而是从目录开始逐级缩小范围三层到四层的 B-Tree 就能覆盖百万到千万行量级的数据。具体到 PostgreSQL一个 B-Tree 索引包含根节点、内部节点和叶子节点。叶子节点存的是索引键值和指向数据行的 TID块号和行偏移量。相同键值的记录在叶子节点上有序排列相邻叶子页之间还有双向指针连接所以 PostgreSQL 的 B-Tree 天然支持正向和反向扫描。这也是ORDER BY 字段 DESC能直接走索引的原因不需要额外的排序运算。有一回我排查一个报表查询SQL 里带着ORDER BY created_at DESC LIMIT 20数据量 200 万行。因为没有在created_at上建索引执行计划里出现了 Sorting 步骤耗时用到 700ms。建了一个created_at DESC索引之后排序消失了变成Index Scan Backward耗时掉到 20ms 以内。这就是 B-Tree 索引的典型价值它既能为等值条件定位数据也能为范围查询和排序节省大量 CPU。1.3 临界点数据量不到多少索引反而拖后腿一条很朴素的经验数据量只有几百行或几千行的表顺序扫描几乎总是赢家。因为一个表页能装几百行记录几千行的表也就几十个数据页全部加载进内存读完都花不了 1ms而索引扫描意味着先读索引页再回表读数据页多一次树干遍历和块跳转成本反而高。但数据量到了百万行级或者查询条件的选择性足够好能过滤掉 99% 的行索引扫描的优势就会碾压顺序扫描。我在实际优化时常用的验证方法就是EXPLAIN ANALYZE对比两个版本EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 10;观察执行计划里的actual time、Buffers重点看命中的块数shared hit与shared read。如果顺序扫描计划读 10 万块索引扫描计划只需要读两三百块那这个索引方案显然值得做。反过来如果两个计划的读块数差不多说明索引建了也白建得考虑换个类型或者调整列顺序。2. B-Tree 之外的选择PostgreSQL 索引类型全景对比说一个最常见的误区很多人以为索引就是 B-Tree无论什么查询都是CREATE INDEX idx ON table(col)。其实 PostgreSQL 内置了至少 5 种索引类型B-Tree、Hash、GIN、GiST、BRIN。每种索引解决的问题不一样选错了代价比不加索引还大。2.1 B-Tree 的适用边界B-Tree 适合等值查询、范围查询、、BETWEEN以及需要排序的ORDER BY。它也是唯一支持ORDER BY方向扫描和索引唯一约束的类型。凡是主键、唯一键底层走的一定是 B-Tree。日常 90% 以上的业务查询B-Tree 都是正确答案。2.2 GIN 与内置数组、全文检索、JSONB 场景GIN 是倒排索引的产物它的核心特点是把一个值内部的元素拆散建立条目。最典型的应用场景有三个数组类型的包含查询、全文检索以及 JSONB 内部的键值查询。我拿一个跟m3u8 索引热词相关的例子来说事如果一张资源表里存着playlist_paths text[]这样的数组字段里面的值是若干个 ts 片段文件的路径你想反查包含 /segments/1001.ts 这个文件路径的播放列表有哪些普通 B-Tree 根本处理不了数组内元素的匹配只能用ANY配合全表展开。但如果你建了 GIN 索引CREATE INDEX idx_playlist_paths ON media_library USING GIN (playlist_paths); SELECT * FROM media_library WHERE playlist_paths ARRAY[/segments/1001.ts]::text[];毫秒级返回。这就是 GIN 的价值适用于一个列里包含多个独立元素的场景包括全文检索的to_tsvector和 JSONB 的、?等操作符。2.3 GiST 与地理空间/范围类型GiST 是广义搜索树它不要求数据本身严格排序而是支持可重叠的判断逻辑。PostgreSQL 里地理位置数据PostGIS 的 geometry、范围类型tsrange、int4range、网络地址类型iprange这些都会用到 GiST。举一个业务例子会议室预约表里有booking_range tsrange要查某个时间段内哪些会议室已被占用SQL 长这样CREATE INDEX idx_booking_range ON bookings USING GIST (booking_range); SELECT * FROM bookings WHERE booking_range tsrange(2024-06-01 10:00, 2024-06-01 11:00);是重叠操作符普通 B-Tree 没法支持这种语义更复杂的查询。GiST 专门解决这类维度数据结构的检索。2.4 BRIN超大数据表的时间序列利器BRINBlock Range INdex是 PostgreSQL 9.5 引入的索引类型索引对象不是每一行而是一个块区间内这一列的最小值和最大值。它的体积可以做得非常小但依赖物理存储的顺序性。最适合的场景就是日志表、流水表这类按时间顺序持续追加写入、很少更新的表。比如一张接收端埋点日志表一天写入几千万行做报表要查某个时间范围的数据。如果数据基本按时间顺序写入ts列和物理存储顺序高度相关BRIN 索引只需要几千个块区间元数据索引体积可能只有 B-Tree 的百分之一却能把全表扫描缩小到一个区间范围扫描。B-Tree 在这种表上反而可能因为随机更新和索引膨胀导致锁竞争和维护开销过大。需要注意BRIN 不适合频繁 UPDATE 或 DELETE 的表也不适合数据物理顺序和查询条件列毫无关联的表。2.5 索引类型速选表索引类型典型操作符推荐场景需要注意B-Tree,,,BETWEEN,ORDER BY业务表等值/范围/排序数据量大时索引膨胀Hash超长字符串等值匹配例如 URL不能排序不能范围查询GIN,,,?数组、JSONB、全文检索写入性能有一定开销GiST,,地理坐标、范围类型非空间类业务用得少BRIN,,BETWEEN超大日志表/时间序列表需要物理有序不适用更新频繁场景选索引类型不是追求花哨。表里的数据长什么样、查询怎么问决定了索引类型。我在大多数项目里仍然以 B-Tree 为主力只有在明确的数组/JSONB/地理查询需求出现时才会引入 GIN 和 GiST。选择的原则只有一个让索引结构贴合查询模式而不是让查询方式适应某种酷炫索引。。3. 复合索引的正确姿势WHERE a AND b 到底怎么建热搜词里有一句很经典的话mysql where 条件 a and b 应该怎么建索引。这个问题在 PostgreSQL 里同样重要而且答案经常出乎意料不是给 a 建一个、给 b 建一个就能让a AND b变快更常见的情况是建一个复合索引(a, b)把两个列塞进同一棵树。3.1 为什么两个单列索引经常不如一个复合索引单独给 a 和 b 各建一个索引后WHERE a 1 AND b 2的逻辑规划器可能采用 BitmapAnd 的方案把两个索引分别扫描得到的行号集合做交集再回表。这个方案只有在两个条件各自都有很高选择性时才会高效。如果 a 和 b 的选择性一般两个 Bitmap 交集的结果依然很大回表次数和无效读页都相当可观。复合索引的好处是它在一个索引的叶子页里同时存 a 和 b 两个键值。规划器从根节点开始先按 a 定位再在相同的 a 值内部按 b 定位一轮索引遍历就能精确定位到目标数据行整个搜索路径缩短了一大截。我以前优化过一条订单查询当时线上是先给user_id和status各建了一个独立的索引查询还是慢。执行计划里出现的是BitmapAnd回表之后还要做过滤。把两个单一索引换成(user_id, status)复合索引之后执行计划变成了Index Scan耗时从 100ms 左右降到了 5ms 以内。这个案例很典型地说明索引合并BitmapAnd是规划器的兜底方案不是最优解。3.2 列顺序的决定规则复合索引里列顺序的决定是最容易让新手犹豫不决的地方。我总结了一套实战规则按优先级排列等值条件列优先WHERE a 1 AND b 2把两个条件都放到索引前面的列上让定位尽可能一步到位。选择性高的列靠前选择性指的是这个值区分度有多高。比如user_id有几十万个不同值status只有三五个不同值那就让user_id在前面。道理很简单先从区分度大的维度砍掉最多的数据剩下的区间就小。用于排序的列放在最后如果 SQL 里有ORDER BY created_at DESC把created_at作为复合索引的最后一列这样索引扫描天然有序不需要额外排序。注意BETWEEN、、这类范围条件只能享用一列一旦列上用了范围条件其后的索引列无法继续用于过滤范围属于中途刹车。看一个具体例子SELECT * FROM orders WHERE user_id 1001 AND status PAID ORDER BY created_at DESC LIMIT 10;合理索引是CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);user_id等值放最前status等值且选择性尚可放中间created_at用于排序放最后并指定DESC正好贴合查询的方向。这样规划器走索引时既不回表排序又能直接倒序取前 10 条。如果把这个规则反过来写成(status, created_at, user_id)效果会大打折扣。因为status只有少数几个值即使命中剩余倒序区间里还是要扫描大量user_id不匹配的行。索引列的排列顺序直接决定树的剪枝效率。3.3 ORDER BY 混搭升降序时的特殊处理PostgreSQL 的 B-Tree 支持正向和反向扫描所以ORDER BY a ASC和ORDER BY a DESC都可以利用同一个索引。但如果你遇到ORDER BY a ASC, b DESC这种混合方向排序B-Tree 就没法在一次单向扫描中满足需要在内存里额外做排序。解法就是建一个满足混合方向的索引CREATE INDEX idx_mixed ON table (a ASC, b DESC);PostgreSQL 从 9.5 开始支持在索引定义中分别指定 ASC/DESC这样一个索引就能严格匹配a ASC, b DESC的排序需求。如果你没建这样的索引规划器往往还得拉一排数据出来做 Sort性能差距在数据量大时非常明显。3.4 INCLUDE 技巧用覆盖索引避免回表PostgreSQL 11 开始支持INCLUDE子句允许把非索引键的列添加到索引的叶子节点上。用途很直接当查询只需要索引里包含的列时执行计划会走Index Only Scan不用回到表里取数据IO 减少一大截。举个例子统计所有已支付订单的金额SELECT user_id, sum(amount) FROM orders WHERE status PAID GROUP BY user_id;如果建的是普通复合索引(status, user_id)要拿到amount还得回表读每一行。但如果你这样建CREATE INDEX idx_orders_status_user_amount ON orders (status, user_id) INCLUDE (amount);PostgreSQL 会在索引叶子节点里额外存一份amount的值。查询时只需要扫描索引而不用回表。这里要特别注意INCLUDE列不参与索引的排序和搜索条件只负责捎带数据避免回表。对于部分高频查询这个技巧能把查询时间进一步压缩超过一半。但这个索引会占用更多物理空间写入时的维护成本也更高所以只建议在条件稳定、查询频繁的 SQL 上用。4. 索引明明建了却不用这些失效场景值得逐条排查建了索引但查询还是慢这是排查慢查询时最让人上头的情况。索引没有进执行计划不等于索引坏了多半是写法让索引无用武之地。4.1 函数包裹列让索引丧失匹配方向最常见的坑就是SELECT * FROM payment_log WHERE date(created_at) 2024-06-01;这条 SQL 乍一看在查created_at实际上是对created_at先调用了date()函数再把函数结果和常量比较。B-Tree 索引存的是原始列值不是函数处理后的结果所以规划器根本无法使用索引只能遍历全表并对每行做一次函数计算。正确写法有两个方向。方向一是改写成范围查询让索引能直接命中SELECT * FROM payment_log WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;方向二是如果这个date(created_at)的写法在业务中无法避免那就建一个表达式索引CREATE INDEX idx_payment_log_created_date ON payment_log (date(created_at));表达式索引的代价是要理解索引键值和数据列值不完全一致。但无论如何不改 SQL 也不建表达式索引只是两手一摊抱怨索引没用是解决不了问题的。4.2 隐式类型转换的隐形杀手PostgreSQL 对类型的严谨程度比其他数据库高得多。一条过滤列是varchar的 SQL如果写成了数值比较比如WHERE phone_no 13800001111PostgreSQL 会做隐式类型转换。这里有个关键细节如果类型转换发生在索引列这一侧通常会导致索引失效如果转换发生在常量那一侧一般还能继续用索引。最稳妥的排查方法是直接看执行计划中的Filter条件如果看到类似(phone_no)::bigint 13800001111这样的内容说明列被隐式转换了。改写为WHERE phone_no 13800001111索引就能生效。团队协作时我会直接建议在字段类型定义阶段就严格控制不要存数字进字符串列避免这类问题彻底从源头消失。4.3 前导模糊查询LIKE %xxx 的命中逻辑这是一个非常高频的问题。LIKE abc%前缀模糊匹配可以用 B-Tree 索引因为索引是有序的只要定位到abc开头的区间即可。但LIKE %abc和LIKE %abc%这种前导通配符写法B-Tree 无从下手只能全表扫描。稍微懂点索引原理的人都应该知道这个坑但实际业务中模糊搜索需求太常见了。我的建议是分场景处理少量数据几万行以内直接让数据库顺序扫描别为了这个建复杂索引。中大型表且数据量百万以上引入pg_trgm扩展建 GIN 索引CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_goods_name_trgm ON goods USING GIN (name gin_trgm_ops); SELECT * FROM goods WHERE name LIKE %无线耳机%;GIN 加上gin_trgm_ops操作符类会让 PostgreSQL 支持对name的子串匹配走索引。实测下来在百万行商品表上前导模糊查询能从秒级降到几十毫秒。4.4 OR 条件、NOT IN、空值判断的坑WHERE a 1 OR b 2这种写法和AND不同要命的地方在于 PostgreSQL 很难把一个 OR 条件直接压成复合索引的单一扫描路径。除非 a 和 b 各自都有单列索引且选择性极高规划器才会考虑 BitmapOr否则更常见的结局是放弃索引、顺序扫描。简单可靠的改写方案是用UNION ALLSELECT * FROM table WHERE a 1 UNION ALL SELECT * FROM table WHERE b 2;两个分支分开走各自的索引再把结果拼接回去。规模可控时这比让规划器硬算 OR 稳定得多。再看NOT IN和NOT EXISTS。NOT IN优化时容易引发全表相关子查询的扫描。还有一个容易被忽略的坑标准 SQL 语义下NULL NOT IN (列表)的结果是不确定所以规划器往往不会选择索引。如果业务语义明确排除 NULL更合适的写法是把NOT IN改成NOT EXISTS子查询或者去掉空值干扰再走索引。空值过滤本身也是一个坑。B-Tree 索引默认会存 NULL 值但IS NULL能不能走索引取决于操作符和规划器判断。常规经验如果你的查询频繁用到WHERE deleted_at IS NULL考虑建一个部分索引Partial IndexCREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;这样索引里只存未删除的用户体积小、检索快且天然满足deleted_at IS NULL这个条件。部分索引是我个人非常喜欢的一个高级特性在很多软删除业务模型里能带来指数级的性能提升。4.5 对索引列做运算等于逼着数据库放弃索引WHERE price * 0.9 100这类写法先把索引列乘以一个系数再做比较B-Tree 索引无法按照变换后的值定位区间。改法是先把运算挪到常量侧WHERE price 100 / 0.9;这是成本最低的优化方式改动小、见效快。类似的还有DATE_PART(year, created_at) 2024同样属于函数包裹建议改写为范围条件或表达式索引。4.6 规划器用不用索引还得看数据分布最后强调一点有时候 SQL 写法没问题索引也建得很标准但规划器就是选择顺序扫描。这往往是因为数据分布太偏。比如 status 字段 99% 的数据都是ACTIVE只有 1% 是DISABLED你查WHERE status ACTIVE规划器算一笔账就算走索引也要回表读 99% 的行还不如全表顺序扫描来得便宜。这种时候规划器的选择是理性且正确的你不需要强行让 SQL 走索引反而应该思考业务是否能改查那些选择性更高的字段。场景失效原因解决方案WHERE date(col) ...函数包裹索引列改写范围查询 / 建表达式索引WHERE varchar_col 123索引列隐式类型转换在常量侧写对类型LIKE %keyword前导通配符无法定位pg_trgm GIN 索引WHERE a 1 OR b 2OR 分支难以合并扫描改写 UNION ALLWHERE price * 0.9 100索引列参与运算运算移到常量侧查询涉及低选择性列回表成本高于顺序扫描调整查询目标列或数据类型5. 主键索引与唯一索引一字之差行为完全两样数据库面试题里有一个出现率极高的对比主键索引和唯一索引到底有什么区别。在 PostgreSQL 里这个问题可以从定义、行为、性能三个层面拆开看。5.1 定义上的区别一个表只有一个主键但可以有很多唯一索引主键Primary Key是一个表级约束它自动创建了一个 Unique Index同时要求列非空。一个表只能定义一个主键但可以建立任意多个唯一索引。也就是说唯一索引Unique Index本身只保证值不重复但不限制 NULL 的出现。这里就有一个重要的 MySQL 迁移陷阱MySQL 中主键列默认不允许 NULL而唯一索引列允许 NULL。PostgreSQL 同样如此主键列隐式带有NOT NULL约束唯一索引则允许 NULL且允许多个 NULL 同时存在因为在标准 SQL 语义里NULL不等于NULL。实际业务中如果你用唯一索引来实现手机号唯一但允许用户不填手机号那 NULL 就是合法的占位。5.2 规划器对待主键与唯一索引的信任程度差异虽然主键在物理实现上就是唯一索引但规划器通常会对主键约束给予更高的信息置信度主键列参与连接、执行去重、处理DISTINCT和GROUP BY时规划器更有把握推导行数和基数。这不是性能上的绝对差异更多是优化器统计信息的利用方式差别。你在设计时优先选择真正的业务主键而不是把任意一个唯一索引当主键用更有利于 PostgreSQL 进行代价评估。5.3 对写入性能与批量导入的影响无论是主键索引还是唯一索引每次 INSERT/UPDATE 都意味着要向索引结构写入新条目而这又可能引发页分裂和索引膨胀。主键索引的开销至少是每插入一行索引页插入一个新键值。如果采用随机主键如 UUID v4新键值会散落在整个索引树的不同位置导致每次插入都可能触发页分裂和索引节点的物理更新写放大非常严重。我做过一个批量导数据的项目目标表有主键和三个唯一索引插入速度一度慢到每秒只有几百条。排查后发现每次插入都被四个索引的维护和随机 IO 拖累。当时的处理办法是大数据导入阶段先DROP掉非主键的唯一索引插入完成后再并发CREATE UNIQUE INDEX导入速度提升了一个数量级。如果你的业务涉及千万级数据的初始化或迁库这个技巧很实用。5.4 主键生成方案的选择直接影响写入和索引形态PostgreSQL 里常见的主键生成方案有序列自增、UUID v4、UUID v7、雪花 ID。序列自增是顺序主键写入时新键值总在索引树的右端页分裂少写入性能最好且 HDD 情况下很友好缺点是分布式场景下生成有中心依赖。UUID v4 随机分布单机大表写入时索引页不断随机分裂性能最差但优点是不担心合并冲突。对于数据量中等、写入不极端的业务它仍然可以接受。UUID v7 是近几年种草的方案前半部分带时间戳、后半部分带随机性既保留了时间排序性又保留了全局唯一性。在 PostgreSQL 17 里已经解除了 UUID 上限连接数量的限制v7 的可用性会越来越好。另外一个物理层面的参数是FILLFACTOR。默认是 100表示每一页都尽量塞满。对于频繁 UPDATE 的表建议把索引建在FILLFACTOR 70或 80 的表上让每个页预留一点空间减少页分裂。对只读历史表FILLFACTOR保持默认即可省空间且扫描效率高。6. 索引日常监控与维护从建索引进阶到养索引慢查询优化不是一次性工作。表的行数在增长、查询模式在变化索引也会因为大量更新而膨胀变慢。维持 PostgreSQL 查询性能需要一套可持续的监控和维护习惯。6.1 第一步永远是找出慢查询没有精确目标就大范围建索引属于空中楼阁。我每次接手调优前先做两件事第一在配置里开启慢查询日志log_min_duration_statement 250 log_duration on log_statement none第二直接查 PostgreSQL 内置的统计视图SELECT calls, round(total_exec_time::numeric / 1000, 2) AS total_ms, round(mean_exec_time::numeric / 1000, 2) AS avg_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;pg_stat_statements是 PostgreSQL 自带的语句统计扩展开启之后会记录每条 SQL 的总耗时、调用次数、平均耗时、IO 情况等。定位出耗时最高的 SQL再针对每一条 SQL 做EXPLAIN ANALYZE优化才有节奏感。这个过程你也可以把EXPLAIN的输出贴给支持 SQL 分析的 AI 工具比如现在流行的 DeepSeek让它帮你判断可疑点但我个人的习惯还是先自己看执行计划里有没有Seq Scan、Sort、Index Scan的条件差异AI 给结论我做复核。6.2 统计信息过期会让规划器走眼前面说过规划器是基于pg_class和直方图统计信息做成本估算的。如果一张表在导入大量数据之后没有运行ANALYZE统计信息停留在旧状态规划器可能低估表大小继续选择全表扫描。这个问题往往比索引本身更容易被忽略。常见的做法是在批量写入之后立刻执行ANALYZE TABLE;或者开启autovacuum的自动分析。生产环境中我会在每天晚上对当日数据增长明显的核心表主动执行一次ANALYZE防止统计信息过度滞后。如果执行计划长期不更新优先怀疑统计信息而不是索引。6.3 索引也会膨胀维护与 REINDEX索引叶子页随着数据的更新和删除会留下空槽位这就是索引膨胀。膨胀的索引占空间变大扫描 IO 变多还有可能让缓存命中率下降。判断索引膨胀最直接的方法是看索引占用空间和数据行数的比例。比如SELECT tablename, indexname, pg_size_pretty(pg_relation_size(indexname::text)) AS index_size FROM pg_indexes WHERE tablename orders;如果索引体积大得离谱往往意味着索引页碎片很多。处理办法是重建索引。PostgreSQL 12 之前只能离线REINDEX会阻塞写入12 之后有了REINDEX CONCURRENTLY它会在原索引旁边建新索引完成后替换不阻塞读写REINDEX INDEX CONCURRENTLY idx_orders_created_at;注意CONCURRENTLY模式会更耗资源和时间适合在低峰期执行但只要空间够这个操作对线上业务友好得多。6.4 识别僵尸索引该删就删不是所有索引都有存在价值。如果一个索引长期没有被任何查询扫描过它就只是写入的累赘。PostgreSQL 的pg_stat_user_indexes提供了每个索引的扫描次数、插入次数、更新次数等指标SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY relname;idx_scan 0表示这个索引从来没人用过。但别急着删先确认两件事一是统计数据是否被重置过二是某些约束索引比如唯一索引虽然有维护成本但承担着业务数据唯一性保障不能因为扫描次数为 0 就删除。只有明确排除上述情况才建议DROP INDEX。6.5 深分页场景的索引陷阱还有一个常见场景表和索引长得都很正常但翻页越翻越慢比如SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;这个 SQL 看起来走id主键索引没有问题但 PostgreSQL 为了找到第 100000 行之后的 20 行需要把索引里的前 100000 个键值逐个跳过。偏移量越大扫描越多。解决思路有两个一是 keyset pagination 替代 OFFSET把分页条件改成基于上一页最后一行的时间戳或 IDSELECT * FROM orders WHERE (created_at, id) (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20;二是如果必须保留完整跳页能力那只能接受这个成本但可以考虑对相关复合索引做精确设计让每一页扫描的无效行数尽量少。大多数业务场景其实都可以改造成 keyset 方式。这个优化直接决定列表接口在大数据量下的最终表现。我在日常维护中沉淀的三条索引习惯这些年在不同规模的项目里折腾 PostgreSQL 索引踩过不少坑也慢慢形成了一套自己的操作方法。首先是每一条 SQL 都跑一次 EXPLAIN ANALYZE 再决定建不建索引。很多看起来需要索引的慢查询病根在 SQL 写法或统计信息而不是缺少索引。先看执行计划再动手建索引顺序千万不能乱。其次是建索引时尽量把业务查询模式想清楚。复合索引的列顺序、是否加 INCLUDE、是否用部分索引这些问题在写 DDL 之前花十分钟想明白比后续反复改索引高效得多。最后是把索引监控纳入常态化巡检。我会用pg_stat_user_indexes和pg_stat_statements以一周为周期巡检一次核心表的索引使用情况及时清掉那些从未被使用过的索引痕迹也在数据量变化后主动更新统计信息。索引不是越多越好也不是建完就一劳永逸。忽略维护索引就可能从提速工具变成写入瓶颈。把上面这些内容消化掉对 PostgreSQL 的索引建立起完整认知再遇到慢查询你基本能顺着执行计划一路追到根本原因。