PostgreSQL复杂报表查询优化求助：大表关联后执行效率低

1. 深挖External Merge on Disk的核心问题 #

虽然已经把work_mem调到1GB，但得确认查询实际吃的内存是否达标：

• 跑EXPLAIN ANALYZE看执行计划里的Sort Method字段，如果还是显示External Merge Disk: XXXkB，要么是会话级的work_mem被其他配置覆盖了，要么是查询里的排序/聚合需要的内存确实超过1GB，这时可以临时给当前会话加内存：SET work_mem = '2GB';再测。
• 注意：如果查询里有多个并行的排序、聚合操作，每个都会单独占work_mem，叠加起来可能超量，得根据实际情况调整。

2. 验证索引真的在干活 #

188万行的historial表，建了索引不代表优化器会用：

• 用EXPLAIN看计划里有没有Index Scan或Bitmap Index Scan，如果没有，大概率是索引建错了：
  • 复合索引要把等值过滤列放前面，日期这类范围列放后面，比如报表查日期+状态的话，建CREATE INDEX idx_historial_date_status ON historial (status, create_time);
  • 别建重复值太多的单列索引，比如状态只有2种值，单独建索引基本没用。
• 清理无效索引：跑SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;删掉从来没被用过的索引，减少写入时的额外开销。

3. 重构查询逻辑，拆繁为简 #

如果查询涉及多表关联、多层聚合，直接硬跑肯定慢：

• 先把historial表的核心聚合结果存临时表：

  CREATE TEMP TABLE tmp_hist_agg AS
  SELECT date_trunc('day', create_time) AS stat_date, count(*) AS total, sum(amount) AS sum_amount
  FROM historial
  WHERE create_time BETWEEN '初始日期' AND '指定日期'
  GROUP BY stat_date;
  

  给临时表加个索引：CREATE INDEX idx_tmp_stat_date ON tmp_hist_agg (stat_date);再用它关联其他表生成报表，速度会快很多。
• 别在WHERE子句里对索引列做函数运算，比如DATE(create_time) = '2024-01-01'会让索引失效，改成create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'。

4. 针对性调优9.5版本的配置 #

• 调大maintenance_work_mem：如果表的统计信息过时，优化器会生成烂计划，把这个参数设成2GB，然后跑ANALYZE historial;更新统计数据。
• 开并行查询：9.5支持并行扫描，设max_parallel_workers_per_gather = 4;（根据CPU核心数调整），让大表扫描能多线程跑。
• 修正effective_cache_size：设成服务器总内存的70%-80%，比如16G内存就设12GB，让优化器更愿意选索引扫描而非全表扫描。

5. 给大表做分区或归档 #

如果historial表是按时间堆数据的，这步能从根本上减少扫描量：

• 做时间分区：按月份或季度拆分表，查询时只会扫指定日期范围内的分区，9.5支持声明式分区，比如：

  CREATE TABLE historial (id INT, create_time TIMESTAMP, ...) PARTITION BY RANGE (create_time);
  CREATE TABLE historial_2023q1 PARTITION OF historial FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
  

• 归档旧数据：把报表很少用到的历史数据迁到单独的归档表，缩小主表的数据量。

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