PostgreSQL 索引与查询优化实战指南

索引类型选择B-Tree：默认索引，适合 `=`、`BETWEEN` 等。数据分布均匀时效果最佳。GIN：适合 `tsvector` 全文检索、`array @>` 包含查询。需要定期重建或维护以保持性能。BRIN：适合大表、顺序相关数据（例如时间序列），空间占用低，范围过滤快。关键配置`work_mem`：根据并发与查询类型设置，避免排序回退磁盘。注意每个并发算子独立消耗。`effective_cache_size`：估算为系统可用缓存（例如总内存的 50–75%），影响优化器对索引扫描与顺序扫描的成本判断。`shared_preload_libraries = 'pg_stat_statements'`：启用后通过视图分析高消耗语句。诊断与优化流程记录慢查询：开启 `pg_stat_statements`，按 `calls/mean_time/stddev_time` 排序定位热点。`EXPLAIN (ANALYZE, BUFFERS)`：观察实际行数与估计偏差，识别错误的选择率与联接计划。建索引与调参：根据谓词与连接键选择索引类型，并调整 `work_mem` 以避免磁盘回退。回归验证：对典型查询跑基准，确保 P50/P95 延迟降低且资源稳定。常见误区过度索引：每次写入均需维护索引，写密集业务需谨慎平衡。统计信息过期：未及时 `ANALYZE` 导致优化器估计偏差，定期维护至关重要。总结通过合理选择索引类型、设置关键内存参数并结合 `pg_stat_statements` 与 `EXPLAIN ANALYZE` 进行闭环验证，可以在不牺牲一致性的前提下显著提升查询性能。