PostgreSQL 索引与查询优化

概述 PostgreSQL 提供丰富的索引类型与强大的执行计划分析能力。合理选择索引与维护统计信息，是保障查询性能的关键。 已验证技术参数 - 常见索引类型：B-Tree（默认）、Hash、GIN、GiST、BRIN - 覆盖索引（INCLUDE 列）可在多列查询中减少回表，提高性能 - 执行计划分析使用 `EXPLAIN ANALYZE`；结合 `buffers` 观察 I/O 行为 - 推荐启用 `pg_stat_statements` 用于全局慢查询分析与基线构建 实践示例 ```sql -- 文本搜索：GIN 适合倒排索引 CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops); -- 空间/范围：GiST 适合区间/地理位置 CREATE INDEX idx_events_period ON events USING GiST (period); -- 多列查询 + 覆盖索引 CREATE INDEX idx_orders_user_time_inc ON orders (user_id, created_at) INCLUDE (status); -- 执行计划分析 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = $1 AND created_at > NOW() - INTERVAL '7 days'; ``` 维护建议 - 周期性刷新统计信息：`VACUUM ANALYZE`（或自动化） - 监控膨胀与无效索引：清理长期未使用索引与过度索引 - 按查询模式设计索引：避免“面向字段”的机械建索引 结语 索引设计的核心是“以查询为中心”。持续分析执行计划与真实负载，才能让数据库在增长与变更中保持稳定的查询性能。