Django+PostgreSQL子查询性能优化问题求助

需求说明 #

需要查询活跃（未关闭、未删除）且带有当前位置的Player：

• 当前位置定义：active_since非未来时间且最接近当前时刻的记录
• 查询结果按Player.registered_at排序
• 支持通过DRF对Player的当前位置进行过滤

系统规模：70万条Player记录，仅1万条为活跃状态。现有查询虽已使用索引，但性能仍不理想。

现有模型定义 #

from django.db import models
from django.db.models import Q

class Player(models.Model):
    class Meta:
        indexes = [
            models.Index(
                fields=["closed", "deleted"],
                condition=Q(closed=False, deleted=False),
                name="opened_player_index",
            )
        ]
    registered_at = models.DateTimeField(_("registerd at"), db_index=True)
    name = models.CharField(max_length=100)
    closed = models.BooleanField(_("closed"), default=False)
    deleted = models.BooleanField(_("deleted"), default=False)
    
    
class Location(models.Model):
    name = models.CharField(max_length=100)

class PlayerLocation(models.Model):
    class Meta:
        indexes = [
            models.Index(
                fields=["player", "active_since", "location"],
                name="player-active-since-location",
            )
        ]

    active_since = models.DateTimeField(_("active since"), db_index=True)

    player = models.ForeignKey(
        Player, on_delete=models.PROTECT, related_name="locations"
    )
    location = models.ForeignKey(
        Location, on_delete=models.PROTECT, related_name="+"
    )

当前查询实现 #

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

class PlayerQuerySet(models.QuerySet):
    def with_location(self):
        # 获取当前位置的子查询
        current_player_location = PlayerLocation.objects.filter(
            player_id=OuterRef("pk"), active_since__lte=Now()
        ).order_by("-active_since")

        return self.annotate(
            lookup_location=Subquery(
                current_player_location.values("location")[:1]
            ),
            location_name=Subquery(
                current_player_location.values("location__name")[:1]
            ),
        )

现有查询执行计划 #

执行SQL #

EXPLAIN ANALYZE
SELECT
  COUNT(*) AS "__count"
FROM
  "player"
WHERE
  (
    NOT "player"."closed"
    AND NOT "player"."deleted"
    AND (
      SELECT
        U0."location_id"
      FROM
        "player_location" U0
      WHERE
        (
          U0."active_since" <= (STATEMENT_TIMESTAMP())
          AND U0."player_id" = ("player"."id")
        )
      ORDER BY
        U0."active_since" DESC
      LIMIT
        1
    ) = 1
  );

执行计划结果 #

Aggregate  (cost=115889.72..115889.73 rows=1 width=8) (actual time=35.327..35.330 rows=1 loops=1)
   ->  Bitmap Heap Scan on core_episode  (cost=119.41..115889.56 rows=64 width=0) (actual time=9.575..34.905 rows=3219 loops=1)
         Recheck Cond: ((NOT closed) AND (NOT deleted))
         Filter: ((SubPlan 1) = 1)
         Rows Removed by Filter: 9800
         Heap Blocks: exact=136
         ->  Bitmap Index Scan on opened_player_index  (cost=0.00..119.39 rows=12800 width=0) (actual time=0.469..0.470 rows=13019 loops=1)
         SubPlan 1
           ->  Limit  (cost=0.43..8.45 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=13019)
                 ->  Index Only Scan using player-active-since-location on player_location u0  (cost=0.43..8.45 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=13019)
                       Index Cond: ((player_id = player.id) AND (active_since <= statement_timestamp()))
                       Heap Fetches: 1
 Planning Time: 4.303 ms
 JIT:
   Functions: 14
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 0.854 ms, Inlining 0.000 ms, Optimization 0.516 ms, Emission 8.393 ms, Total 9.762 ms
 Execution Time: 71.447 ms
(18 rows)

优化方案 #

1. 用窗口函数减少子查询次数 #

现有查询对每个活跃Player执行一次子查询（共13019次），累计耗时高。改用窗口函数一次性筛选出所有Player的最新有效位置，再与Player表关联，大幅减少查询次数。

from django.db.models import Window, F
from django.db.models.functions import RowNumber
from django.db.models import Subquery, OuterRef, Now

class PlayerQuerySet(models.QuerySet):
    def with_current_location(self):
        # 第一步：筛选所有Player的最新有效位置
        latest_locations_subq = PlayerLocation.objects.filter(
            active_since__lte=Now()
        ).annotate(
            row_num=Window(
                expression=RowNumber(),
                partition_by=F('player_id'),  # 按Player分组
                order_by=F('active_since').desc()  # 取最新的记录
            )
        ).filter(row_num=1).values('player_id', 'location_id', 'location__name')
        
        # 第二步：关联Player，仅保留活跃用户，添加位置注解并排序
        return self.filter(
            closed=False, 
            deleted=False,
            id__in=Subquery(latest_locations_subq.values('player_id'))
        ).annotate(
            lookup_location=Subquery(
                latest_locations_subq.filter(player_id=OuterRef('pk')).values('location_id')[:1]
            ),
            location_name=Subquery(
                latest_locations_subq.filter(player_id=OuterRef('pk')).values('location__name')[:1]
            )
        ).order_by('registered_at')

2. 优化索引设计 #

现有player-active-since-location索引的排序方向与查询需求不匹配，调整索引以支持倒序排序，避免额外排序开销：

class PlayerLocation(models.Model):
    class Meta:
        indexes = [
            models.Index(
                fields=["player", "-active_since", "location"],  # 改为-active_since（倒序）
                name="player-active-since-location-desc",
            ),
            # 新增索引：支持按位置反向查询最新Player
            models.Index(
                fields=["location", "-active_since", "player"],
                name="location-active-since-player-desc",
            )
        ]
    # ... 其他字段不变

3. 重构COUNT查询逻辑 #

针对统计类查询（如DRF的分页count），直接用JOIN替代子查询过滤，进一步提升性能：

SELECT COUNT(*)
FROM player p
INNER JOIN (
    SELECT player_id, location_id
    FROM (
        SELECT 
            player_id, 
            location_id,
            ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY active_since DESC) AS row_num
        FROM player_location
        WHERE active_since <= STATEMENT_TIMESTAMP()
    ) AS latest_loc
    WHERE row_num = 1
) AS pl ON p.id = pl.player_id
WHERE p.closed = FALSE 
  AND p.deleted = FALSE 
  AND pl.location_id = 1;

4. DRF过滤适配 #

在DRF FilterSet中直接使用注解后的lookup_location字段实现位置过滤：

from django_filters import rest_framework as filters

class PlayerFilter(filters.FilterSet):
    location = filters.NumberFilter(field_name='lookup_location', lookup_expr='exact')
    
    class Meta:
        model = Player
        fields = ['location']

优化效果说明 #

• 窗口函数方式将子查询执行次数从13019次减少到2次，大幅降低累计开销
• 调整后的索引直接支持active_since倒序排序，避免Limit时的额外排序操作
• JOIN式COUNT查询避免了逐行过滤的开销，统计性能提升明显

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