如何优化Django Annotate查询以提升PostgreSQL查询效率？

当前查询的根本性问题 #

你的查询存在几个核心性能瓶颈：

1. 大表JOIN+分组统计的冗余开销：Chart与DiceRoll是一对多关系，原查询会将符合时间条件的所有DiceRoll记录与Chart做JOIN，再分组Count。当DiceRoll数据量极大（尤其是12个月的范围），JOIN后的数据集会非常庞大，统计过程需要遍历大量冗余数据，耗时剧增。
2. 索引缺失的潜在影响：如果DiceRoll表的timestamp和chart_id字段没有组合索引，PostgreSQL会被迫做全表扫描或低效的单索引组合查询，这是慢查询的核心诱因。
3. 不必要的字段查询：Chart.objects.all()会默认查询Chart的所有字段，但实际你只需要Chart的标识和统计次数，额外字段的传输与处理会增加无意义的开销。

无需缓存的即时优化方案 #

1. 添加组合索引 #

在DiceRoll表上创建(timestamp, chart_id)的组合索引，让PostgreSQL可以快速过滤时间范围，同时直接按chart_id分组统计，避免全表扫描。

在Django模型中添加索引：

class DiceRoll(models.Model):
    chart = models.ForeignKey(Chart, on_delete=models.CASCADE)
    timestamp = models.DateTimeField()
    
    class Meta:
        indexes = [
            models.Index(fields=['timestamp', 'chart_id'], name='diceroll_time_chart_idx'),
        ]

执行迁移生效：

python manage.py makemigrations && python manage.py migrate

2. 重构查询语句 #

从DiceRoll侧发起统计，避免大表JOIN，再批量关联Chart对象，大幅减少数据传输量：

from django.db.models import Count

# 先在DiceRoll表内完成统计，仅返回chart_id和对应次数
roll_counts = DiceRoll.objects.filter(
    timestamp__gt=time_delta,
    timestamp__lte=timestamp_now
).values('chart_id').annotate(num_dicerolls=Count('id')).order_by('-num_dicerolls')

# 批量获取对应的Chart对象，避免N+1查询
chart_ids = [item['chart_id'] for item in roll_counts]
charts = Chart.objects.filter(id__in=chart_ids).in_bulk()

# 组装最终结果
result = [
    {'chart': charts[item['chart_id']], 'num_dicerolls': item['num_dicerolls']}
    for item in roll_counts
]

3. 限制结果数量 #

如果只需要排名靠前的Chart（比如Top 100），在排序后添加切片限制，减少排序和返回的数据量：

roll_counts = DiceRoll.objects.filter(
    timestamp__gt=time_delta,
    timestamp__lte=timestamp_now
).values('chart_id').annotate(num_dicerolls=Count('id')).order_by('-num_dicerolls')[:100]

缓存方案的适用场景 #

如果上述优化后，12个月范围的查询仍无法满足性能要求，再考虑预统计缓存：

• 创建ChartRollMonthlyStats模型，存储chart_id、year、month、roll_count
• 用定时任务（如Celery）每日/每月统计上月的掷骰次数，更新到该表
• 查询时，根据时间范围累加对应月份的统计数据再排序
  该方案适合频繁查询历史数据的场景，但需要维护定时任务和数据一致性（比如处理实时数据的补统计）

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