PostgreSQL 索引优化与查询计划入门

查询计划基础

• 使用 EXPLAIN ANALYZE 获取真实执行时长与行数估计偏差。
• 关注 Seq Scan、Index Scan、Bitmap Heap/Index Scan 的触发条件与代价模型。

索引类型与适用场景

• B-Tree：等值与范围查询首选；支持 ORDER BY；PostgreSQL 11 起支持 INCLUDE 覆盖列。
• GIN/GiST：全文搜索与复杂数据类型（数组、JSONB、地理数据）。
• 部分索引：为热点数据建立 WHERE 条件索引，降低写入与维护成本。

示例

-- 覆盖索引（减少回表）
CREATE INDEX idx_orders_user_created_incl
  ON orders (user_id, created_at)
  INCLUDE (status);

-- 部分索引（只为常查的状态建立索引）
CREATE INDEX idx_orders_status_partial
  ON orders (status)
  WHERE status IN ('paid', 'shipped');

-- 计划查看
EXPLAIN ANALYZE
SELECT user_id, created_at, status
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

维护与统计

• 定期 VACUUM 与 ANALYZE 保持可见性地图与统计信息新鲜。
• 避免在查询中对索引列进行函数转换，必要时建立函数索引。