为何基于两个子查询结果的Django浮点除法注解会造成性能损耗？

核心机制解释 #

• PostgreSQL 优化器对**关联标量子查询（即Django生成的带OuterRef关联的子查询）**存在明确的优化边界：当两个及以上关联标量子查询的返回值，被同时用于同一条SELECT投影行内的表达式计算时，优化器会放弃原本的批量优化路径，退化为逐行执行逻辑。
• 原本仅在注解列中单独返回points、price两个字段时，优化器可以将这两个关联子查询自动改写为LATERAL连接或批量GROUP BY聚合，一次性拉取所有关联数据，再与主表Join，这部分执行成本是固定的全量扫描+聚合，对应9秒的基础查询耗时。
• 一旦添加price_per_point的除法+防除零Case表达式，优化器会判定两个关联子查询的返回值是逐行动态依赖的，无法提前物化成临时结果集与主表连接。最终执行计划退化为：对每一条主表PlayerReport记录，都单独执行一次points子查询、一次带排序LIMIT1的price子查询，拿到两个值之后再代入计算表达式。
• 其中price子查询本身是ORDER BY date DESC LIMIT 1的关联查询，逐行执行时的单条成本远高于批量聚合：如果没有匹配的覆盖索引，每次执行都需要对PlayerPrice表做匹配扫描+排序，多次重复执行的成本直接将总耗时拉高到原有的2倍，和观测到的9秒到16秒的涨幅完全匹配。
• 其他除法计算没有触发问题的原因：要么使用的是主表自带的普通字段（读取无额外成本），要么仅引用了单个关联子查询的返回值，优化器可以保留原本的批量优化逻辑，不会退化为逐行执行模式。而_division_exp生成的防除零逻辑需要先判断子查询返回值是否为0，这个判断进一步强化了优化器对“子查询结果是逐行动态依赖”的判定，彻底阻断了批量物化的优化路径。

方案1：最低改造成本——将派生计算移到Python层 #

• 保持原有Django查询的注解逻辑，仅保留points、price的字段返回，完全移除数据库层面的price_per_point计算。
• 在遍历查询结果集时，逐行计算派生值：当price为0时取值为0，否则按照除法逻辑计算最终结果。
• 这种方式的数据库执行计划和原9秒版本完全一致，Python层面的浮点计算成本几乎可以忽略，是改造成本最低的方案。

方案2：数据库层优化——替换关联标量子查询为批量Join #

• 彻底避免容易触发执行计划退化的关联标量子查询，把points、price两个字段的获取逻辑改成可批量执行的聚合Join，参考实现如下：

  from django.db.models import Sum, OuterRef, Subquery, FloatField
  
  # 1. points字段直接用一对多关系聚合，无需独立关联子查询
  base_query = base_query.annotate(
      points=Sum('player_match__score', output_field=FloatField())
  )
  
  # 2. price字段用PostgreSQL的DISTINCT ON实现批量取最新价，无需逐行关联
  latest_price_qs = PlayerPrice.objects.filter(
      player_report=OuterRef('pk')
  ).order_by(
      'player_report_id', '-date'
  ).distinct('player_report_id').values('price')
  
  base_query = base_query.annotate(
      price=Subquery(latest_price_qs, output_field=FloatField())
  )
  
  # 3. 此时points、price都是Join出来的独立列，在数据库层计算派生字段不会触发执行计划退化
  final_query = base_query.annotate(
      price_per_point=_division_exp('points', 'price'),
      derived_field2=_division_exp('field1', 'field2'),
      derived_field3=_division_exp('field3', 'field4')
  )
  

• 配套检查PlayerPrice表的索引，确保存在复合索引(player_report_id, date DESC) INCLUDE (price)，让最新价的批量查询成为纯索引扫描，无需回表。

方案3：执行计划兜底——强制物化子查询结果 #

• 如果上述改造成本较高，可以利用PostgreSQL的HINT机制，强制优化器将两个子查询的结果提前全量物化（PostgreSQL 12+版本支持），避免逐行执行的退化问题。可以在子查询的SQL片段中添加/*+ MATERIALIZE */标记，固定优化路径。

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