sql - Django Admin 多对多内联 View 的 SQL 查询过多

Question

我在四个模型之间建立了“钻石”关系。 AlignedScan 基本上是一种有效载荷承载模型，促进了 Scan 和 Alignment 之间的多对多关系。 Scan 和 Alignment 都与 Scan Project 具有一对一的关系。当尝试在 Alignment 详细信息窗口(在 Django Admin 中)中显示 AlignedScans 的内联列表时，它可以工作但由于为每个记录执行两个额外的 SQL 查询而非常慢。我做错了什么？

注意:内嵌 View 是只读的。记录仅通过脚本导入创建。

这些是我的模型:

from django.contrib.gis.db import models
from django.db.models import Count, Q

class ScanProject_Manager(models.Manager):

    def get_queryset(self):
        return (super().get_queryset()
                    .annotate(number_of_alignments=Count('alignments', distinct=True))
                    .annotate(number_of_usedscans=Count('scans', distinct=True, filter=Q(scans__used=True)))
        )

class ScanProject(models.Model):
    objects = ScanProject_Manager()


    slug = models.CharField(max_length=30,primary_key=True,) 
    label = models.CharField(max_length=100,)

    #...

class Scan(models.Model):
    name = models.CharField(max_length=24,)
    #...

    scanproject = models.ForeignKey(
        ScanProject,
        related_name='scans',
        on_delete=models.CASCADE,
    )

class Alignment(models.Model):
    aligned_when = models.CharField(max_length=13,)
    #...

    scanproject = models.ForeignKey(
        ScanProject,
        related_name='alignments',
        on_delete=models.CASCADE,
        )

class AlignedScan(models.Model):
    registered = models.BooleanField()
    x = models.FloatField(blank=True, null=True,)
    y = models.FloatField(blank=True, null=True,)
    z = models.FloatField(blank=True, null=True,)
    #...

    alignment = models.ForeignKey(
        Alignment,
        related_name='alignedscans',
        on_delete=models.CASCADE, \
        )
    
    scan = models.ForeignKey(
        Scan, 
        related_name='alignedscans', 
        on_delete=models.CASCADE,
        )

class AlignedScan_Manager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().select_related('scan')

我尝试过使用和不使用 AlignedScan_Manager，我尝试过使用开箱即用的普通 InlineModel:

class AlignedScan_Inline(admin.TabularInline):
    model = AlignedScan

我试过一个定制的:

class AlignedScan_Inline(admin.TabularInline):
    verbose_name = 'Scan'    # omit 'Aligned' from the UI, as it is implied
    verbose_name_plural = 'Scans'

    model = AlignedScan 
    fk_name = 'alignment'

    fields = ('registered', 'x', 'y', 'z')
    readonly_fields = ('registered', 'x', 'y', 'z') 
    extra = 0
    show_change_link = True  
    list_select_related = ['scan']  # tried with and without this


    def has_add_permission(self, request, owner):
        return False
    def has_change_permission(self, request, owner):
        return True #TBC: respect user permissions
    def has_delete_permission(self, request, owner):
        return False  #TBC: respect user permissions

最终版本还必须显示扫描表中的字段，但即使我不这样做，问题仍然存在。

我为每条记录获得两个 SQL 查询:

SELECT "scans_scan"."id",
       "scans_scan"."name",
        -- ....

       "scans_scan"."scanproject_id"
 FROM "scans_scan"
 WHERE "scans_scan"."id" = 3413
 LIMIT 21 10 similar queries.   

和

SELECT "scans_alignment"."id",
        "scans_alignment"."aligned_when",
        "scans_alignment"."scanproject_id",
        -- ...
        "scans_alignment"."imported_by_id"
    FROM "scans_alignment"
    WHERE "scans_alignment"."id" = 4
    LIMIT 21 11 similar queries.  Duplicated 11 times.

Answer 1

尝试将 scan 字段添加到 raw_id_fields .

class AlignedScan_Inline(admin.TabularInline):
    raw_id_fields = ['scan']
    ...

这很有用，因为 ModelForms 在关系字段方面做了一些开箱即用的低效工作。管理员上有许多内联显然加剧了这种情况。通过从呈现过程中删除关系字段，您可以消除这种低效率。