PostgreSQL 索引优化实战

数据库性能优化中，索引是最重要的手段——没有之一。PostgreSQL 提供了丰富的索引类型和灵活的索引策略，但用错了比不用更糟：不仅查询没变快，写入还变慢了，磁盘空间也白白浪费。

本文从索引类型对比开始，逐步深入复合索引设计、部分索引、覆盖索引、执行计划分析，最后通过真实慢查询案例串联全部知识。

一、PostgreSQL 索引基础：五大类型

PostgreSQL 不是只有 B-Tree。它提供了 5 种内置索引类型，每种都有专属的使用场景：

选型决策

查询类型是什么？
├── 等值查询
│   ├── 数据量大、重复值少 → B-Tree（首选）
│   └── 纯等值、不排序 → Hash（不推荐，B-Tree 通常更好）
├── 范围查询 / 排序 → B-Tree
├── 数组包含 / JSONB → GIN
├── 几何 / 空间查询 → GiST（或 PostGIS 的 SP-GiST/GiST）
├── 全文搜索
│   ├── 英文为主 → GIN（tsvector）
│   └── 需要排序 → GiST
└── 超大表 + 自然排序 → BRIN

二、B-Tree 索引深度解析

B-Tree 是 PostgreSQL 的默认索引类型，也是最常用的。理解它的内部结构有助于写出更高效的查询。

结构特点

• 平衡多路搜索树：所有叶子节点在同一深度，查询时间稳定 O(log N)
• 按排序存储：ORDER BY 可以利用索引避免额外排序
• 支持前缀匹配：LIKE 'abc%' 可以用索引，LIKE '%abc' 不行
• 支持 IS NULL：NULL 值也存储在 B-Tree 索引中（与其他数据库不同）

B-Tree 的边界情况

-- ✅ 能用索引
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE age > 18 AND age < 30;
SELECT * FROM users WHERE name LIKE 'Ali%';
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- ❌ 不能用索引
SELECT * FROM users WHERE name LIKE '%lice';    -- 前缀通配符
SELECT * FROM users WHERE LOWER(name) = 'alice'; -- 函数包裹（需表达式索引）
SELECT * FROM users WHERE age::text = '25';      -- 类型转换
SELECT * FROM users WHERE abs(age) < 5;          -- 函数包裹

排序与索引

B-Tree 索引默认按 ASC NULLS LAST 排序。你可以显式指定排序方向：

-- 按 created_at 降序创建索引，ORDER BY DESC 可直接使用
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);

-- 组合排序：先按 status 升序，再按 created_at 降序
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 查询必须匹配索引排序才能避免额外排序步骤
SELECT * FROM orders
WHERE status = 'active'
ORDER BY created_at DESC;  -- ✅ 匹配索引第二列排序方向

三、复合索引与列顺序

复合索引（Composite Index）是在多个列上创建的索引。列的顺序决定了索引能否被特定查询使用——这是索引设计中最容易出错的点。

最左前缀原则

-- 复合索引
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- ✅ 能用索引的查询
WHERE user_id = 1
WHERE user_id = 1 AND status = 'paid'
WHERE user_id = 1 AND status = 'paid' AND created_at > '2026-01-01'

-- ⚠️ 部分能用（只用到 user_id 列）
WHERE user_id = 1 AND created_at > '2026-01-01'  -- status 缺失，created_at 无法用索引

-- ❌ 完全不能用索引
WHERE status = 'paid'                            -- 缺少 user_id
WHERE status = 'paid' AND created_at > '2026-01-01'  -- 缺少 user_id
WHERE created_at > '2026-01-01'                  -- 缺少 user_id

列顺序设计原则

冗余索引识别

-- 这两个索引同时存在时，前者是冗余的
CREATE INDEX idx_a ON orders (user_id);              -- 冗余
CREATE INDEX idx_ab ON orders (user_id, created_at); -- 包含前者

-- 因为 (user_id, created_at) 的最左前缀就是 user_id，
-- 所有只用 user_id 的查询都能用 idx_ab

-- 删除冗余索引
DROP INDEX idx_a;

四、部分索引与表达式索引

部分索引（Partial Index）

部分索引只对满足条件的行建立索引，大幅减少索引体积和维护开销。

-- 场景：订单表中 90% 是已完成状态，只查活跃订单
-- 不需要为 completed 的行建索引
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE status IN ('pending', 'processing', 'shipped');

-- 查询匹配 WHERE 条件时才能用索引
SELECT * FROM orders
WHERE status = 'pending' AND user_id = 123;
-- ✅ 用到 idx_orders_active

SELECT * FROM orders
WHERE status = 'completed' AND user_id = 123;
-- ❌ 不用 idx_orders_active，走全表扫描或其他索引

-- 场景：只对非 NULL 值建索引
CREATE INDEX idx_users_email ON users (email)
WHERE email IS NOT NULL;

-- 场景：只对未删除记录建索引（软删除）
CREATE INDEX idx_articles_active ON articles (slug)
WHERE deleted_at IS NULL;

-- 场景：只对大额订单建索引
CREATE INDEX idx_orders_large ON orders (user_id)
WHERE total_amount > 10000;

表达式索引（Expression Index）

当查询条件包含函数或表达式时，普通索引无法使用。表达式索引对计算结果建索引。

-- 问题：LOWER(name) 无法用普通索引
SELECT * FROM users WHERE LOWER(name) = 'alice';
-- → Seq Scan

-- 解决：对表达式建索引
CREATE INDEX idx_users_name_lower ON users (LOWER(name));

SELECT * FROM users WHERE LOWER(name) = 'alice';
-- → Index Scan using idx_users_name_lower

-- 场景：JSONB 字段查询
CREATE INDEX idx_events_data_type ON events ((data->>'type'));

SELECT * FROM events WHERE data->>'type' = 'click';
-- → Index Scan

-- 场景：日期提取
CREATE INDEX idx_orders_month ON orders ((EXTRACT(MONTH FROM created_at)));

-- 场景：数组长度
CREATE INDEX idx_tags_count ON posts ((array_length(tags, 1)));

-- ⚠️ 查询中的表达式必须与索引定义完全一致
-- 这两个不会走索引：
SELECT * FROM users WHERE lower(name) = 'alice';   -- 函数名大小写不同
SELECT * FROM users WHERE LOWER(name::text) = 'alice'; -- 表达式不同

五、覆盖索引（Index-Only Scan）

当查询需要的所有列都在索引中时，PostgreSQL 可以直接从索引返回数据，无需回表——这就是 Index-Only Scan。

-- 需求：查用户的 name 和 email
SELECT name, email FROM users WHERE user_id = 123;

-- 普通索引：需要回表取 name 和 email
CREATE INDEX idx_users_id ON users (user_id);
-- → Index Scan + 回表

-- 覆盖索引：索引包含所有查询列
CREATE INDEX idx_users_id_covering ON users (user_id) INCLUDE (name, email);
-- → Index-Only Scan（不回表）

INCLUDE 子句

PostgreSQL 11+ 支持 INCLUDE 子句，将额外列存储在索引的叶子节点中，但不参与排序和搜索：

-- INCLUDE 列不参与 B-Tree 排序，只存储在叶子节点
-- 适合：查询需要这些列，但不会作为查询条件

-- 场景：按 user_id 查订单，同时需要 order_no 和 total
CREATE INDEX idx_orders_uid_cover ON orders (user_id)
INCLUDE (order_no, total_amount);

SELECT order_no, total_amount FROM orders WHERE user_id = 123;
-- → Index-Only Scan

覆盖索引 vs 普通复合索引

Visibility Map 与 Index-Only Scan

Index-Only Scan 依赖 Visibility Map 判断索引元组是否对所有事务可见。如果页面的 all-visible 标志未设置，仍需回表检查可见性——退化成 Index Scan。

-- 检查 Index-Only Scan 的回表率
EXPLAIN (ANALYZE, BUFFERS)
SELECT name, email FROM users WHERE user_id = 123;

-- 输出中关注：
-- Heap Fetches: 0    ← 完美，无需回表
-- Heap Fetches: 150  ← 需要回表 150 次，Index-Only Scan 名不副实

-- 解决：VACUUM 更新 Visibility Map
VACUUM users;

-- 自动清理配置（确保及时更新 Visibility Map）
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.05);

六、索引扫描类型：Index Scan / Bitmap Scan / Sequential Scan

PostgreSQL 有三种主要的数据扫描方式，理解它们的区别有助于判断索引是否真正有效。

三种扫描对比

Index Scan

EXPLAIN SELECT * FROM users WHERE id = 1;

-- Index Scan using users_pkey on users
--   Index Cond: (id = 1)

适合点查和返回极少行数的场景。每找到一行都要回表，行数多了随机 I/O 爆炸。

Bitmap Scan

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Bitmap Heap Scan on orders
--   Recheck Cond: (user_id = 123)
--   -> Bitmap Index Scan on idx_orders_user_id
--        Index Cond: (user_id = 123)

Bitmap Scan 分两步：

1. Bitmap Index Scan：扫描索引，收集匹配行的物理位置，在 Bitmap 中标记
2. Bitmap Heap Scan：按物理位置排序后访问堆表，减少随机 I/O

Sequential Scan 不一定坏

-- 100 万行的表，查 30% 的行
EXPLAIN SELECT * FROM orders WHERE status = 'completed';

-- Seq Scan on orders
--   Filter: (status = 'completed')

全表扫描看似低效，但当返回行数占比大时，顺序读比随机读快得多。PostgreSQL 的优化器会自动选择成本最低的方案。

七、EXPLAIN 分析执行计划

EXPLAIN 是 PostgreSQL 性能分析的基石。读懂它是诊断慢查询的必备技能。

基础用法

-- 只看计划（不执行）
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 执行并看实际耗时（⚠️ 会在生产执行查询）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';

-- 更详细的信息
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

关键指标解读

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

-- 输出示例：
-- Index Scan using idx_orders_user_status on orders  (cost=0.42..8.45 rows=1 width=72) (actual time=0.015..0.016 rows=2 loops=1)
--   Index Cond: ((user_id = 123) AND (status = 'paid'))
--   Buffers: shared hit=4

常见问题诊断

1. 预估行数严重偏差

-- 预估 1 行，实际 50000 行 → 统计信息过期
-- Index Scan (cost=0.42..8.45 rows=1 width=72) (actual time=0.015..12.345 rows=50000 loops=1)

-- 解决：更新统计信息
ANALYZE orders;

-- 或增加统计信息精度
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

2. 意外的 Seq Scan

-- 索引存在但优化器选择 Seq Scan
-- 可能原因：
-- 1. 统计信息不准 → ANALYZE
-- 2. 返回行数太多 → 检查查询条件
-- 3. 成本参数不合理 → 检查 random_page_cost

-- 查看当前成本参数
SHOW random_page_cost;  -- 默认 4.0（机械硬盘），SSD 建议 1.1
SHOW seq_page_cost;     -- 默认 1.0

-- SSD 环境调整（让优化器更倾向使用索引）
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

3. Sort 节点消耗大量时间

-- Sort (cost=100000..105000 rows=50000 width=72) (actual time=500..600 rows=50000)
--   Sort Key: created_at DESC
--   Sort Method: external merge Disk: 5000kB

-- 问题：排序用磁盘而非内存
-- 解决 1：增加 work_mem
SET work_mem = '64MB';  -- 临时生效
ALTER SYSTEM SET work_mem = '64MB';  -- 永久生效

-- 解决 2：让 ORDER BY 走索引
CREATE INDEX idx_orders_created_desc ON orders (created_at DESC);

EXPLAIN 输出中的危险信号

八、索引维护：膨胀、重建、bloat 检测

索引不是建完就完事了。随着大量 UPDATE/DELETE，索引会产生膨胀（Bloat）——索引中存在大量空洞，物理体积远大于实际数据量。

为什么会膨胀

• PostgreSQL 的 MVCC 机制：UPDATE = DELETE + INSERT，旧版本仍占用空间
• VACUUM 只标记空间可复用，不归还给操作系统
• 频繁 UPDATE 的表，膨胀可能达到实际数据的 2-5 倍

膨胀检测

-- 方法1：使用 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       avg_leaf_density,
       leaf_pages,
       internal_pages
FROM pgstatindex('idx_orders_user_id');

-- avg_leaf_density < 50% 说明膨胀严重（健康值 90%+）

-- 方法2：对比索引大小与表大小
SELECT schemaname, relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       indexrelname
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

-- 如果索引比表还大，大概率膨胀或设计有问题

-- 方法3：估算膨胀比例（无需扩展）
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       ROUND(100 * pg_relation_size(i.indexrelid)::numeric /
             NULLIF(pg_relation_size(c.oid), 0), 1) AS index_to_table_pct
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
JOIN pg_stat_user_indexes USING (indexrelid)
WHERE pg_relation_size(i.indexrelid) > 100 * 1024 * 1024  -- > 100MB
ORDER BY pg_relation_size(i.indexrelid) DESC;

索引重建

-- 方法1：REINDEX（锁表，生产环境慎用）
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;  -- 重建表上所有索引

-- 方法2：REINDEX CONCURRENTLY（PG 12+，不锁表）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- 方法3：先建新索引再删旧索引（兼容旧版本，不锁表）
CREATE INDEX CONCURRENTLY idx_orders_user_id_new ON orders (user_id);
DROP INDEX CONCURRENTLY idx_orders_user_id;
ALTER INDEX idx_orders_user_id_new RENAME TO idx_orders_user_id;

自动维护建议

-- 配置自动清理更激进（针对高更新频率的表）
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,  -- 5% 行变化后触发（默认 20%）
    autovacuum_analyze_scale_factor = 0.02, -- 2% 行变化后分析
    autovacuum_vacuum_cost_delay = 10       -- 降低清理速度，减少对业务影响
);

-- 监控最后清理时间
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

九、常见索引设计错误

错误 1：过度索引

-- ❌ 为每个查询条件单独建索引
CREATE INDEX idx1 ON orders (user_id);
CREATE INDEX idx2 ON orders (status);
CREATE INDEX idx3 ON orders (created_at);
CREATE INDEX idx4 ON orders (total_amount);
-- 4 个索引 = 4 倍写入开销 + 4 倍磁盘空间

-- ✅ 用复合索引覆盖常见查询组合
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
CREATE INDEX idx_orders_created ON orders (created_at);
-- 2 个索引覆盖大部分查询，写入开销减半

错误 2：忽略查询模式建索引

-- ❌ 不看实际查询，凭直觉建索引
CREATE INDEX idx_users_phone ON users (phone);  -- 从未按 phone 查询

-- ✅ 根据实际慢查询建索引
-- 先看 pg_stat_statements 找出慢查询
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

错误 3：低选择性列单独建索引

-- ❌ 性别列只有 2-3 个值，索引几乎无用
CREATE INDEX idx_users_gender ON users (gender);

-- ✅ 低选择性列与高选择性列组合
CREATE INDEX idx_users_gender_city ON users (gender, city);  -- city 区分度高

-- ✅ 或者用部分索引
CREATE INDEX idx_users_rare_status ON users (status)
WHERE status = 'vip';  -- 只有 1% 是 VIP

错误 4：忘记处理 NULL

-- ❌ 大量 NULL 值污染索引
CREATE INDEX idx_users_middle_name ON users (middle_name);
-- 80% 的用户没有 middle_name，索引 80% 是 NULL

-- ✅ 排除 NULL
CREATE INDEX idx_users_middle_name ON users (middle_name)
WHERE middle_name IS NOT NULL;

错误 5：忽略数据类型不匹配

-- ❌ 类型不匹配导致隐式转换，索引失效
-- phone 是 VARCHAR，但传入整数
SELECT * FROM users WHERE phone = 13800138000;
-- PostgreSQL 会将 phone 转为整数再比较 → 索引失效

-- ✅ 保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

-- ✅ 或建表达式索引
CREATE INDEX idx_users_phone_int ON users ((phone::bigint));
-- 但这会增加写入开销，不如统一类型

错误 6：CREATE INDEX 不加 CONCURRENTLY

-- ❌ 锁表！生产环境大表会导致服务不可用
CREATE INDEX idx_orders_user ON orders (user_id);

-- ✅ 不锁表，允许并发读写
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

-- ⚠️ CONCURRENTLY 不能在事务块中执行
-- 也不能同时创建多个 CONCURRENTLY 索引

十、实战场景：慢查询优化案例

案例 1：订单列表分页查询

场景：电商后台订单列表，按用户 + 状态筛选，按创建时间倒序分页。

-- 原始查询（慢：3.2s）
SELECT id, order_no, total_amount, status, created_at
FROM orders
WHERE user_id = 12345
  AND status IN ('paid', 'shipped', 'completed')
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- EXPLAIN 结果
-- Seq Scan on orders (cost=0.00..50000.00 rows=50 width=72) (actual time=50..3200 rows=48)
--   Filter: (user_id = 12345 AND status = ANY(...))
--   Rows Removed by Filter: 999952

-- 分析：缺少合适索引，全表扫描过滤 100 万行

-- 优化步骤1：添加复合索引
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

-- 优化步骤2：用覆盖索引避免回表
CREATE INDEX CONCURRENTLY idx_orders_user_status_created_cover
ON orders (user_id, status, created_at DESC)
INCLUDE (order_no, total_amount);

-- 删除旧索引
DROP INDEX CONCURRENTLY idx_orders_user_status_created;

-- 优化后 EXPLAIN
-- Index Only Scan using idx_orders_user_status_created_cover
--   (cost=0.42..12.50 rows=50 width=72) (actual time=0.02..0.05 rows=48)

-- 结果：3.2s → 0.05ms，提升 64000 倍

案例 2：JSONB 字段查询

场景：事件追踪表，按 JSONB 中的 event_type 查询。

-- 原始查询（慢：1.8s）
SELECT id, user_id, data, created_at
FROM events
WHERE data->>'event_type' = 'page_view'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- EXPLAIN 结果
-- Seq Scan on events (cost=0.00..180000.00 rows=5000 width=200)
--   Filter: ((data->>'event_type' = 'page_view') AND (created_at > ...))

-- 优化：表达式索引 + 复合索引
-- 方案1：表达式 + B-Tree 复合索引
CREATE INDEX CONCURRENTLY idx_events_type_created
ON events ((data->>'event_type'), created_at DESC);

-- 方案2（推荐）：GIN 索引 + jsonb_path_ops（更高效）
CREATE INDEX CONCURRENTLY idx_events_data_gin
ON events USING GIN ((data->>'event_type'));

-- 方案3：如果查询模式固定，用部分索引
CREATE INDEX CONCURRENTLY idx_events_page_view
ON events (created_at DESC)
WHERE data->>'event_type' = 'page_view';

-- 优化后（方案3）
-- Index Scan using idx_events_page_view
--   (cost=0.42..80.00 rows=5000 width=200) (actual time=0.01..2.50 rows=4987)

-- 结果：1.8s → 2.5ms

案例 3：多条件筛选 + 全文搜索

场景：文章搜索，按分类 + 状态筛选，同时搜索标题。

-- 原始查询（慢：5.6s）
SELECT id, title, category, status, published_at
FROM articles
WHERE category_id = 5
  AND status = 'published'
  AND to_tsvector('simple', title) @@ to_tsquery('simple', 'postgresql & index')
ORDER BY published_at DESC
LIMIT 20;

-- 问题：to_tsvector 每行计算，无法用索引

-- 优化步骤1：预计算 tsvector 列
ALTER TABLE articles ADD COLUMN title_tsvector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', title)) STORED;

-- 优化步骤2：创建 GIN 索引
CREATE INDEX CONCURRENTLY idx_articles_title_gin
ON articles USING GIN (title_tsvector);

-- 优化步骤3：复合 B-Tree 索引
CREATE INDEX CONCURRENTLY idx_articles_cat_status_published
ON articles (category_id, status, published_at DESC)
WHERE status = 'published';

-- 优化后查询
SELECT id, title, category, status, published_at
FROM articles
WHERE category_id = 5
  AND status = 'published'
  AND title_tsvector @@ to_tsquery('simple', 'postgresql & index')
ORDER BY published_at DESC
LIMIT 20;

-- 优化器可能选择 BitmapAnd：
-- BitmapAnd
--   -> Bitmap Index Scan on idx_articles_cat_status_published
--   -> Bitmap Index Scan on idx_articles_title_gin

-- 结果：5.6s → 8ms

案例 4：OFFSET 分页性能退化

场景：深分页查询，页码越大越慢。

-- 第1页快（0.01ms）
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 0;

-- 第50000页慢（12s）
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 999980;

-- 原因：OFFSET 必须扫描并跳过前面所有行

-- 优化：Keyset Pagination（游标分页）
-- 第1页
SELECT * FROM orders ORDER BY id LIMIT 21;  -- 多取1行判断是否有下一页

-- 第2页（基于上一页最后一条的 id）
SELECT * FROM orders WHERE id > 999980 ORDER BY id LIMIT 21;

-- 结果：无论翻到第几页，都是 0.01ms

十一、速查表

索引类型选择

索引设计原则

EXPLAIN 关键指标

维护命令速查