含GROUP BY与MAX的子查询无法使用索引的优化问询

针对你在PostgreSQL 15.3中处理百万级myapp1_task表，需要获取每个myapp2_item_id对应最高sequence行的问题，以下是具体优化步骤：

1. 构建正确的复合索引 #

单独的sequence索引无法满足分组取最值的查询需求，必须创建以分组字段为前缀、排序字段为后缀的复合索引，让PostgreSQL能直接通过索引定位每个分组的最大值：

• 直接执行SQL创建索引：

  CREATE INDEX idx_task_item_sequence ON myapp1_task (myapp2_item_id, sequence DESC);
  

• 在Django模型中定义索引：

  from django.db import models
  
  class Task(models.Model):
      myapp2_item = models.ForeignKey('myapp2.Item', on_delete=models.CASCADE)
      sequence = models.IntegerField()
      # 其他字段定义
  
      class Meta:
          indexes = [
              models.Index(fields=['myapp2_item', '-sequence'], name='idx_task_item_sequence'),
          ]
  

2. 改用PostgreSQL特有的DISTINCT ON查询语法 #

Django默认生成的GROUP BY + MAX聚合查询，通常需要先聚合再关联原表获取整行数据，效率极低。改用PostgreSQL的DISTINCT ON语法可以直接拿到目标行，且能完美利用上述复合索引：

from django.db.models import F

# 获取每个myapp2_item_id下sequence最大的完整行
top_tasks = Task.objects.order_by('myapp2_item_id', '-sequence').distinct('myapp2_item_id')

该查询的逻辑是：先按myapp2_item_id分组排序，再按sequence降序排列，DISTINCT ON会保留每个分组的第一行，也就是sequence最大的行。

3. 排查索引未被使用的原因 #

如果创建复合索引后仍未被使用，可尝试以下操作：

• 更新表统计信息：PostgreSQL的查询优化器依赖统计信息判断是否使用索引，执行ANALYZE myapp1_task;更新统计数据。
• 临时禁用顺序扫描验证索引有效性：在当前会话中执行SET enable_seqscan = OFF;，再执行查询查看执行计划是否使用索引（仅用于排查，不建议长期开启）。
• 检查是否有额外过滤条件：如果查询包含WHERE子句，需将过滤字段加入复合索引前缀（例如常用过滤字段status，索引改为(myapp2_item_id, status, sequence DESC)）。

4. 备选：优化聚合查询写法 #

如果必须使用聚合查询，确保Django生成的SQL是直接关联而非嵌套子查询。可以通过query属性查看生成的SQL：

print(Task.objects.values('myapp2_item_id').annotate(max_seq=Max('sequence')).query)

若生成的SQL包含嵌套子查询，可手动调整ORM写法，改用Subquery和OuterRef实现更高效的关联：

from django.db.models import Subquery, OuterRef, Max

max_seq_subquery = Task.objects.filter(myapp2_item_id=OuterRef('myapp2_item_id')).values('myapp2_item_id').annotate(max_seq=Max('sequence')).values('max_seq')
top_tasks = Task.objects.filter(sequence=Subquery(max_seq_subquery))

但此写法效率仍不如DISTINCT ON，仅在无法使用DISTINCT ON时作为备选。

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