Too many SQL queries with Django Admin many-to-many inline view

Viewed 726

I have a 'diamond' relationship between four models. AlignedScan is basically a pay-load carrying model facilitating a many-to-many relationship between Scan and Alignment. Both Scan and Alignment have a to-one relationship to Scan Project. When trying to display a inline-list of AlignedScans in an Alignment detail window (in Django Admin), it works but is very slow due to two extra SQL queries being executed for each record. What am I doing wrong?

Note: the inline view is meant to be read-only. Records are created exclusively via scripted imports.

These are my models:

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')

I've tried with and without the AlignedScan_Manager, I've tried using a plain-vanilla straight out of the box InlineModel:

class AlignedScan_Inline(admin.TabularInline):
    model = AlignedScan

and i've tried a custom one:

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

The final version must also show fields from the Scan table, but even when I don't, the problem persists.

I get two SQL queries for each record:

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

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

and

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.
3 Answers

Try adding the scan field to raw_id_fields.

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

This is useful because ModelForms do some inefficient things out of the box in regards to relationship fields. This obviously has been exacerbated by having many inlines on the admin. By removing the relationship field from the rendering process, you eliminate that inefficiency.

Additional answer:

This also fixed the problem:

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

In my initial attempt I had only pre-selected 'scan', not both 'scan' and 'alignment'. If I use this manager for my AlignedScan model, I can remove the get_queryset() override from AlignedScan_Inline

Try to select the related fields from get_queryset:

class AlignedScan_Inline(admin.TabularInline):
    ...
    def get_queryset(self, *args, **kwargs):
        return super().get_queryset(*args, **kwargs).select_related('scan', 'alignment')
Related