English
devexamples
← 中文资源

简体中文版本

为活跃数据创建部分索引

只索引查询真正读取的行，在软删除或按状态过滤的表上显著降低索引体积与写入开销。

阅读英文原文

概述

大多数业务表都存在热数据子集：未完成订单、活跃会话、未删除记录。全量索引会为每一行付出写入成本，并占用本应留给热数据的内存。部分索引只包含满足谓词的行，因此体积更小、更容易常驻缓存、维护成本更低。

步骤

  1. 确认应用查询的谓词，确保规划器能够匹配：
EXPLAIN SELECT id FROM orders
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC LIMIT 20;
  1. 使用相同谓词并发建索引，并按查询的排序方式排列列：
CREATE INDEX CONCURRENTLY idx_orders_open_tenant
  ON orders (tenant_id, created_at DESC)
  WHERE status = 'open';
  1. 执行 ANALYZE orders; 后用 EXPLAIN (ANALYZE, BUFFERS) 确认走的是部分索引。
  2. 对比部分索引与全量索引的大小，确认差异符合预期。

验证

预期执行计划显示 Index Scan using idx_orders_open_tenant，共享缓冲区读取远低于全表扫描，部分索引体积通常小一个数量级。检查 pg_index.indisvalid 为 true，避免并发建索引失败留下无效索引。

注意事项

  • 查询必须包含与索引完全一致的谓词，否则无法使用部分索引。
  • 若同时存在查询全部状态的路径，仍需保留全量索引。
  • CREATE INDEX CONCURRENTLY 不能在事务中执行，耗时长且需要两次表扫描，应避开高峰。
  • 状态机新增取值后要同步检查索引谓词，否则新状态会退化为全表扫描。