PostgreSQL 查询计划与索引优化实战：EXPLAIN ANALYZE 与 pg_stat_statements

本文以可复现实验为主线，演示如何通过 `EXPLAIN ANALYZE` 与 `pg_stat_statements` 度量查询开销，结合不同索引类型（B-tree/GIN）与写法调整实现稳定可预期的性能提升。

## 适用版本与前提

• PostgreSQL 13–16（推荐 15/16）。
• 已安装并启用 `pg_stat_statements` 扩展用于查询指纹统计。

-- 在 postgresql.conf 中加入：shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

## 示例表与索引

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status SMALLINT NOT NULL,
  amount NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  note TEXT
);

-- B-tree 复合索引，最左前缀遵循高选择性列优先
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- 文本检索示例（GIN）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_note_trgm ON orders USING gin (note gin_trgm_ops);

## 执行计划与真实耗时

EXPLAIN (ANALYZE, BUFFERS) SELECT id, amount
FROM orders
WHERE user_id = 1001 AND status = 1
  AND created_at BETWEEN '2024-01-01 09:00:00+00' AND '2024-01-01 13:00:00+00';

观察要点：

• `Index Scan` 是否命中 `idx_user_status_created`；`Rows Removed by Filter` 是否显著。
• `BUFFERS` 显示共享缓冲读写情况，评估 I/O 压力。
• `Actual Rows` 与估算差异过大时，考虑 `ANALYZE` 或提高统计目标。

-- 提高统计目标，确保选择性与分布更准确
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 200;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 200;
VACUUM (ANALYZE) orders;

## 写法与索引优化建议

• 复合索引列顺序：高选择性列靠前；时间列用于范围过滤置后。
• 避免对索引列使用函数或隐式类型转换，改用范围查询或预计算列。
• 覆盖查询：仅选择索引包含列可减少回表；必要时考虑包含列索引（PostgreSQL 12+ 的 `INCLUDE`）。

-- 包含列索引示例（减少回表）
CREATE INDEX idx_user_status_created_inc ON orders (user_id, status, created_at) INCLUDE (amount);

## pg_stat_statements 指标观测

SELECT queryid, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

对比优化前后 `mean_time` 与 `rows` 变化；如需清理历史用于重新评估：

SELECT pg_stat_statements_reset();

## 并发与事务注意事项

• 大批量更新建议分批与禁用热点索引重建；必要时使用 `CREATE INDEX CONCURRENTLY`。
• 长事务会阻碍 `VACUUM` 清理，导致膨胀与统计失真，应及时提交。
• 自增主键热点可考虑 `BIGSERIAL` 与批插入均匀化或使用 `uuid`。

## 相关文章（同分类热门）

• [MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南](./MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南.md)
• [MySQL 索引设计与覆盖索引的可验证实践指南](./MySQL 索引设计与覆盖索引的可验证实践指南.md)

## 结语

通过 `EXPLAIN (ANALYZE, BUFFERS)` 与 `pg_stat_statements` 的闭环度量，可以将 PostgreSQL 的查询与索引优化转化为可验证的工程实践，确保每一次改动都能在数据指标上体现价值。