Django annotation搭配Subquery过滤时查询过慢如何优化

• 重复执行相关子查询：你在filter条件里两次写入相同的Subquery(mba_start_subq)，数据库会对Job表的每一行都执行两次Education表的关联扫描，数据量稍大就会产生嵌套循环全表扫描，查询复杂度是O(M*N)（M是Job表行数，N是Education表行数），必然会出现长时间无响应的情况。硬编码字符串过滤快是因为常量值会被查询优化器提前计算，不需要逐行执行子查询。
• 子查询取最早日期的写法效率低：用order_by('startdate')[:1]取最早日期需要数据库先排序再截断，性能远低于直接用Min聚合函数取最小值。
• 字符串存储日期无法利用索引：虽然YYYY-MM-DD格式的字符串字典序和日期顺序一致，可以直接做大小比较，但数据库无法对字符串字段使用日期类查询优化，一旦有格式异常的脏数据还会导致比较逻辑出错。
• 外键定义存在笔误：你定义的User模型类名为User，但Education和Job的ForeignKey第一个参数传的是'Users'，这个笔误会导致ORM关联逻辑本身就存在异常。

第一步：修正模型笔误 #

把Education、Job模型的外键关联参数修正为正确的模型名：

class Education(models.Model):
    hash_code = models.ForeignKey('User', models.CASCADE) # 原参数为'Users'，修正为对应模型名User
    startdate = models.TextField(blank=True, null=True)
    enddate = models.TextField(blank=True, null=True)

class Job(models.Model):
    hash_code = models.ForeignKey('User', models.CASCADE) # 原参数为'Users'，修正为对应模型名User
    jobends = models.TextField(blank=True, null=True)
    jobstarts = models.TextField(blank=True, null=True)

第二步：用单次聚合+单次子查询实现逻辑 #

避免重复执行子查询，用Min聚合代替排序取首条，子查询只在annotate阶段执行一次，filter阶段直接引用注解字段即可：

from django.db.models import Min, F, Q

# 构造每个用户最早教育起始时间的聚合查询
mba_start_subq = Education.objects.filter(
    hash_code=OuterRef('hash_code')
).values('hash_code').annotate(
    earliest_start=Min('startdate')
).values('earliest_start')[:1]

# 关联查询+过滤，子查询仅执行1次
jobs = Job.objects.annotate(
    mba_start=mba_start_subq
).filter(
    Q(jobstarts__lt=F('mba_start')),
    Q(jobends__gt=F('mba_start'))
)

如果要实现你给出的原生SQL里「教育起始时间+1年落在工作起止区间内」的逻辑，因为当前日期是字符串存储，可以借助PostgreSQL内置函数做日期计算：

from django.db.models import Func, DateField

# 定义日期加1年的数据库函数
class AddOneYear(Func):
    template = "to_date(%(expressions)s, 'YYYY-MM-DD') + interval '1 year'"
    output_field = DateField()

jobs = Job.objects.annotate(
    mba_start=mba_start_subq,
    mba_start_plus_1y=AddOneYear('mba_start')
).filter(
    jobstarts__lt=F('mba_start_plus_1y'),
    jobends__gt=F('mba_start_plus_1y')
)

这个写法生成的SQL和你给出的原生查询逻辑完全等价，性能比重复子查询高1~2个数量级。

• 把所有日期字段从TextField改为DateField：当前你存储的是标准YYYY-MM-DD格式字符串，PostgreSQL可以直接隐式转换为日期类型，执行迁移不需要额外清洗数据。改成日期类型后，数据库可以对日期范围查询做专项优化，还能自动拦截格式错误的脏数据。
• 给高频查询字段加索引：给Education.hash_code、Education.startdate、Job.hash_code、Job.jobstarts、Job.jobends添加db_index=True，关联查询和范围过滤时可以直接走索引，避免全表扫描。
• 数据量超过10万条时，直接用Join代替子查询：可以通过select_related直接关联Education表做聚合，性能会比相关子查询更高。

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