Rails优化：如何从PostgreSQL数十万数据高效生成统计

一、直接废掉循环查询，换成批量聚合查询 #

循环查数据库是低效根源——N次请求+多次数据传输，直接用PostgreSQL的聚合函数一次性拉取所有统计维度的数据，这是最立竿见影的优化。

比如原来你可能循环遍历每个分类，每次查count/sum，现在改成一次GROUP BY+FILTER子句搞定多维度统计：

SELECT
  cat1,
  cat2,
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE cat3 = 'xxx') AS cat3_xxx_count
FROM items
GROUP BY cat1, cat2, DATE_TRUNC('month', created_at)
ORDER BY month DESC;

一次查询就能拿到所有需要的统计结果，彻底避免多次数据库往返。

二、把单索引换成复合索引，针对性适配查询 #

单索引在多维度GROUP BY或过滤场景下基本没用，得根据你的统计查询字段组合建复合索引：

• 如果查询是按cat1+cat2+created_at分组，建：

CREATE INDEX idx_items_cat1_cat2_created_at ON items (cat1, cat2, created_at DESC);

• 如果统计需要按时间范围过滤（比如只查近半年），把created_at放前面：

CREATE INDEX idx_items_created_at_cat1_cat2 ON items (created_at DESC, cat1, cat2);

这样PostgreSQL可以直接通过索引完成聚合（索引扫描），不用扫全表。

三、预计算统计数据，用小表换速度 #

如果仪表盘统计不需要实时数据（允许延迟1小时/1天），直接预计算结果存在单独的统计表，仪表盘查小表速度能到毫秒级，两种方案选：

物化视图方案 #

创建物化视图存储预计算结果：

CREATE MATERIALIZED VIEW item_stats AS
SELECT
  cat1,
  cat2,
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total_count,
  SUM(CASE WHEN cat3 = 'A' THEN 1 ELSE 0 END) AS cat3_a_count
FROM items
GROUP BY cat1, cat2, DATE_TRUNC('month', created_at);

定期刷新（比如每天凌晨）：

REFRESH MATERIALIZED VIEW item_stats;

给物化视图加索引：

CREATE INDEX idx_item_stats_cat1_cat2_month ON item_stats (cat1, cat2, month);

之后仪表盘直接查item_stats就行。

定时任务方案 #

用Python/Java写定时脚本，或者用PostgreSQL的pg_cron扩展，每天定时计算统计数据插入到item_dashboard_stats表，仪表盘直接读这个小表。

四、代码层面的具体优化 #

1. 杜绝ORM的N+1查询：用Django/SQLAlchemy这类ORM时，直接写原生SQL或用ORM的聚合API，别循环分类查。比如Django写法：

from django.db.models import Count, Q
from django.db.models.functions import TruncMonth

stats = Item.objects.annotate(
    month=TruncMonth('created_at')
).values('cat1', 'cat2', 'month').annotate(
    total=Count('id'),
    cat3_xxx_count=Count('id', filter=Q(cat3='xxx'))
).order_by('-month')

一次查询拉全数据，不用循环。

2. 加时间范围限制：如果不需要全量历史数据，比如只查近1年，在查询里加WHERE created_at >= '2023-01-01'，减少扫描的数据量。

3. 按需加载：如果统计维度太多，别一次性加载所有数据，用户点哪个时间段/分类就加载对应的数据。

五、数据库配置与架构优化 #

• 调优PostgreSQL参数：比如提高work_mem（给聚合查询分配足够内存）、shared_buffers，让数据库能在内存里处理聚合，不用写临时文件。
• 做时间分区表：未来数据到100万+时，按created_at按月/季度分区，查询时只扫对应分区，速度会大幅提升。

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