PostgreSQL 查询优化与统计信息（扩展统计、计划稳定与验证）

概述

PostgreSQL通过统计信息进行代价估算与计划选择。扩展统计可捕获列间相关性与独立性, 提升选择度估算准确性, 结合合理的参数与索引设计, 可获得稳定且高效的查询计划。

关键实践与参数

• 扩展统计: `CREATE STATISTICS` 捕获 `dependencies`/`mcv`/`ndistinct`
• 分析频率: `ANALYZE` 保持统计新鲜, `default_statistics_target=100`
• 计划稳定: 控制 `enable_nestloop`/`enable_hashjoin`/`work_mem`
• 观测工具: `EXPLAIN (ANALYZE, BUFFERS)` 与 `pg_stat_statements`
• 索引策略: 覆盖查询与选择度优先, 减少回表

示例/配置/实现

CREATE INDEX idx_orders_cust_status_created ON orders(customer_id, status, created_at DESC);
CREATE STATISTICS st_orders (dependencies) ON customer_id, status FROM orders;
ANALYZE orders;

SET work_mem='64MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

验证

• 计划对比: 记录 `EXPLAIN ANALYZE` 的节点、耗时与缓冲命中率, 观察因扩展统计带来的选择度与计划变化
• 资源影响: 调整 `work_mem` 比较排序或哈希节点的溢出与内存使用
• 命中效率: 对比索引覆盖下的 `rows` 与 `loops`, 降低回表开销
• 稳定性: 在不同数据分布阶段重复测试, 验证计划稳定与性能一致性

注意事项

• 统计需定期更新, 大批量写入后务必 `ANALYZE`
• 计划稳定需要谨慎参数调优, 防止全局变更影响其他查询
• 关注版本支持的统计类型, 不同版本特性差异可能影响效果
• 与业务查询模式协同设计索引与统计, 防止过度优化