PostgreSQL 部分索引：索引里存什么，决定它能不能被用上

一张订单表跑到两千万行，其中 99.3% 的行状态是 paid 或 closed，只有十几万行是 pending。后台的待处理队列每隔几秒查一次 status = 'pending'，走全表索引既浪费空间又拖慢写入。给 status 加部分索引是标准做法，但写法有讲究——写对了查询能用上，写错一个字符就完全失效，而且计划器不会有任何警告。

先看完整索引的实际代价

CREATE INDEX idx_orders_status ON orders (status);

两千万行里 status 只有五种取值，选择度极低。按 status = 'pending' 查询时，优化器大概率不会用这个索引——它要扫两千万条索引项才能捞出十几万行，再回表，成本比顺序扫描还高。索引白建，还让每次 INSERT 多一次 B-tree 维护。

换成部分索引：

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

索引项直接从两千万降到十几万，体积小了约两个数量级，写入维护成本同步下降。查询改成：

SELECT id, created_at FROM orders
WHERE status = 'pending' AND created_at > now() - interval '1 hour'
ORDER BY created_at;

EXPLAIN 里应看到 Index Scan using idx_orders_pending，Index Cond 带 created_at > ...，且没有 Filter: (status = 'pending')。如果 Filter 还在、走的是 Seq Scan，索引没被识别。

失效的第一种原因：谓词没有严格蕴含

部分索引的可用条件是查询的 WHERE 必须逻辑上蕴含索引的 WHERE。上面那条查的是 status = 'pending' AND ...，蕴含成立。下面这些改法都会让它失效：

-- 常量类型不一致：status 是 varchar，'pending' 被强制转换，蕴含判断失败
WHERE status = 'pending'::text AND ...

-- 用 IN 列出多个状态，不蕴含单值条件
WHERE status IN ('pending', 'paying') AND ...

-- 用不等号，更不蕴含
WHERE status <> 'closed' AND ...

第二种特别容易踩：业务上想把 pending 和 paying 一起查，顺手写成 IN。这时代码逻辑没错，但索引用不上了。要么拆成两条 SQL 用 UNION ALL，要么把索引改成 WHERE status IN ('pending','paying')——注意索引谓词里的表达式必须和查询里的写法字面一致，IN ('pending', 'paying') 与 IN ('paying', 'pending') 在语义上等价，但计划器做蕴含推理时按语法树匹配，顺序不同不一定能匹配上，稳妥做法是两边照抄同一段。

第二种原因：函数包裹了索引列

CREATE INDEX idx_orders_pending ON orders (lower(status))
  WHERE status = 'pending';

索引键是 lower(status)，查询用的是 status = 'pending'，蕴含关系不成立，索引直接不可用。要么查询也写 lower(status)，要么索引键就用原始列。这类问题在线上最难发现，因为 CREATE INDEX 成功、\d orders 里也能看到这个索引，只有 EXPLAIN 会暴露它从未被使用。

第三种原因：模型统计信息过期

部分索引的可用量估算依赖 pg_statistic 里对谓词的记录。大量数据增删后，pending 行的真实占比变化很大，如果 ANALYZE 没跟上，计划器可能错误地认为这个索引扫出来的行数太多，转而选择顺序扫描。先跑一次 ANALYZE orders; 再看计划，如果之前是 Seq Scan、之后变成 Index Scan，问题就在统计信息。

怎么确认索引真被用上了

不要凭 CREATE INDEX 成功就认为生效。三个可验证的手段：

-- 1) 看计划，重点是没有 Filter: (status = 'pending')
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... WHERE status = 'pending' AND ...;

-- 2) 看索引的实际体积与扫描次数
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'orders';

-- 3) 强制关闭顺序扫描做对照（仅调试环境）
SET enable_seqscan = off;
EXPLAIN SELECT ...;

第 2 条里的 idx_scan 是累计扫描次数。上线一段时间后它仍是 0，说明这个索引一次都没被用过，要么谓词不匹配，要么统计信息让计划器放弃了它。

一个容易被忽略的副作用

部分索引只覆盖满足谓词的行，所以不能用它来支撑其他条件的查询。如果同一条 SQL 里还有 AND user_id = ?，索引里没有 user_id，user_id 就只能作为过滤条件在回表后执行。行数少时没问题，行数多时要权衡是否建成复合部分索引：

CREATE INDEX idx_orders_user_pending ON orders (user_id, created_at)
  WHERE status = 'pending';

这种写法把选择度最高的列放前面，再按时间排序，可以同时服务"某用户的待处理订单"和"全部待处理订单按时间"两类查询（后者利用索引前缀的 created_at 排序时会不够理想，因为 user_id 在前打断了全局有序）。

小结式的判据

判断一个部分索引是否值得建、是否真的生效，按这个顺序看：

部分索引的价值不在"少写一个 WHERE"，而在于把索引从"全表数据的副本"变成"只包含关注数据的结构"。它的失效方式又格外安静——不报错、不警告，只在执行计划里少一行。所以建完之后的那次 EXPLAIN 不是可选动作。