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

# PostgreSQL 索引优化与查询计划入门 ## 查询计划基础 - 使用 `EXPLAIN ANALYZE` 获取真实执行时长与行数估计偏差。 - 关注 `Seq Scan`、`Index Scan`、`Bitmap Heap/Index Scan` 的触发条件与代价模型。 ## 索引类型与适用场景 - B-Tree：等值与范围查询首选；支持 `ORDER BY`；PostgreSQL 11 起支持 `INCLUDE` 覆盖列。 - GIN/GiST：全文搜索与复杂数据类型（数组、JSONB、地理数据）。 - 部分索引：为热点数据建立 `WHERE` 条件索引，降低写入与维护成本。 ## 示例 ```sql -- 覆盖索引（减少回表） 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` 保持可见性地图与统计信息新鲜。 - 避免在查询中对索引列进行函数转换，必要时建立函数索引。