索引类型选择

• B-Tree：默认索引，适合 =、BETWEEN 等。数据分布均匀时效果最佳。
• GIN：适合 tsvector 全文检索、array @> 包含查询。需要定期重建或维护以保持性能。
• BRIN：适合大表、顺序相关数据（例如时间序列），空间占用低，范围过滤快。

关键配置

• work_mem：根据并发与查询类型设置，避免排序回退磁盘。注意每个并发算子独立消耗。
• effective_cache_size：估算为系统可用缓存（例如总内存的 50–75%），影响优化器对索引扫描与顺序扫描的成本判断。
• shared_preload_libraries = 'pg_stat_statements'：启用后通过视图分析高消耗语句。

诊断与优化流程

1. 记录慢查询：开启 pg_stat_statements，按 calls/mean_time/stddev_time 排序定位热点。

2. EXPLAIN (ANALYZE, BUFFERS)：观察实际行数与估计偏差，识别错误的选择率与联接计划。

3. 建索引与调参：根据谓词与连接键选择索引类型，并调整 work_mem 以避免磁盘回退。

4. 回归验证：对典型查询跑基准，确保 P50/P95 延迟降低且资源稳定。

常见误区

• 过度索引：每次写入均需维护索引，写密集业务需谨慎平衡。
• 统计信息过期：未及时 ANALYZE 导致优化器估计偏差，定期维护至关重要。

总结

通过合理选择索引类型、设置关键内存参数并结合 pg_stat_statements 与 EXPLAIN ANALYZE 进行闭环验证，可以在不牺牲一致性的前提下显著提升查询性能。