服务端与数据 / 进阶

PostgreSQL 索引：从执行计划反推访问路径

通过 EXPLAIN ANALYZE 识别扫描、过滤和排序成本，再围绕高频查询设计复合索引、部分索引和覆盖索引。

12 分钟PostgreSQL · Index · EXPLAIN · Performance

先用真实参数看执行计划

重点比较 estimated rows 与 actual rows、被过滤掉的行数、是否出现额外 Sort，以及 shared read/hit。估算偏差很大时先检查统计信息和数据分布，不要立刻归因于索引缺失。

EXAMPLE / 01PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

等值过滤列通常放前面，范围或排序列放后面。INCLUDE 可以减少回表，但会增加索引体积和写放大。字段顺序必须围绕真实查询，而不是机械地按选择性排序。

EXAMPLE / 02PostgreSQL

CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (total);

当查询长期只关注少量活跃行时，部分索引更小、更容易驻留缓存。上线后继续观察 pg_stat_user_indexes，长期不使用的重复索引也会拖慢写入和 vacuum。

大表上使用 CREATE INDEX CONCURRENTLY，并预留磁盘和 I/O 空间。它减少写阻塞，但执行更久，失败后还可能留下 invalid 索引。

EXAMPLE / 03PostgreSQL

CREATE INDEX CONCURRENTLY jobs_pending_run_at_idx
ON jobs (run_at)
WHERE status = 'pending';