从MS SQL迁移至Postgres14后分析查询性能骤降，求优化方案

优化方案 #

核心问题定位 #

1. 执行计划显示查询需要扫描事实表1460万行数据，占总数据量的33%，该场景下B树索引的随机IO开销远高于顺序扫描，PostgreSQL优化器选择顺序扫描是合理的，强制走索引无提升符合预期。
2. 缓存命中率极低，仅7%左右，说明PostgreSQL内存配置不足，大部分数据需要从磁盘读取，是性能差的核心原因之一。
3. 查询存在严重冗余：子查询中查询了大量后续聚合不需要的字段，大幅增加了IO和内存开销。

具体优化措施 #

1. 简化查询逻辑：删除子查询中除分组需要的Product Type、Product Type Group、Main Product Categories之外的所有冗余字段，可直接将查询改写为无嵌套的聚合结构，减少无效数据读取。
2. 调整PostgreSQL配置：笔记本环境建议按如下规则调整postgresql.conf：
   • shared_buffers调整为物理内存的25%，用于缓存高频访问数据
   • work_mem调整为64MB及以上，提升哈希连接、排序操作的内存执行比例
   • random_page_cost调整为1.1（SSD环境），让优化器更倾向于选择索引扫描（小数据量过滤场景）
3. 使用物化视图预聚合：BI场景的聚合查询维度相对固定，可创建对应维度的物化视图定期刷新，查询直接访问物化视图即可达到毫秒级返回，和MS SQL Server的索引视图逻辑一致。
4. 按日期分区事实表：以KeyBillingMonth为分区键对事实表做范围分区，查询时只会扫描符合日期过滤条件的分区，可减少70%以上的扫描数据量。
5. 列存优化：如果以分析类查询为主，可使用cstore_fdw扩展或者PostgreSQL 14+的原生列存功能存储事实表，列存对宽表聚合查询的性能提升可达10倍以上，之前Citus列存性能差是配置或使用方式不正确导致。

关于集群和分区的疑问 #

你当前的查询量级单机完全可以承载，不需要引入集群。你的理解正确，这类简单聚合查询在优化后单机性能可以达到和MS SQL Server相当的水平，甚至更高。

内容的提问来源于stack exchange，提问作者David Johnson