Optimize DB query with Index

axeljacobs · February 24, 2022, 6:53pm

Hi everyone,

How can I optimize the DB query ? Creating an index ?

I have three models Certificate, Company and Address.

class Address(models.Model)
    street = models.CharField(max_length=200)
    number = models.IntegerField()
    ...

class Company(models.Model):
    name = models.CharField(max_length=100)
    ...

class Certificate(models.Model):
    name =  models.CharField(max_length=100)
    company = models.ForeignKey('address_book.Company', on_delete=models.PROTECT)
    ...

I override the __str__ of the Certificate model and retrieve informations from the Company and Address models

class Certificate(models.Model):
    name =  models.CharField(max_length=100)
    company = models.ForeignKey('address_book.Company', on_delete=models.PROTECT)
    ...

    def __str__(self):
        return f"{self.name} - {self.company.name} - {self.company.address.street}"

I populated the DB with a 1000 certificates, some companies and addresses.

In the admin, I can see that loading the ‘certificate list’ is slow and a lot of requests are done to return the certificates string representation.

How can I optimize this? creating an Index ? How create such an Index ?

Do someone has a link to a good tutorial about when creating an Index ?

Thanks in advance…

KenWhitesell · February 24, 2022, 11:28pm

The way to fix this is to not rely upon the admin building the representation of the model instances. You don’t show where and how you’re using this, so we can’t offer specific recommendations as to how to improve this, but you’ll likely want to write a custom function that builds the representation of those models.

axeljacobs · February 25, 2022, 9:15am

Sorry I’ll be more explicit…

Here is the are my models (I removed some code to spare space):

class Code(models.Model):
    name = models.CharField(max_length=15, unique=True)

    ....


class SubCode(models.Model):
    name = models.CharField(max_length=15, unique=True)
    code = models.ForeignKey(Code, on_delete=models.PROTECT)
    description = models.TextField(default=None, null=True, blank=True)

    ...


class Certificate(models.Model):
    ACTIVE = 1
    PENDING = 2
    SUSPENDED = 3
    REVOKED = 4
    STATUS = (
        (ACTIVE, 'active'),
        (PENDING, 'pending'),
        (SUSPENDED, 'suspended'),
        (REVOKED, 'revoked'),
    )
    uuid = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    company = models.ForeignKey('address_book.Company', on_delete=models.PROTECT)
    contact_email = models.EmailField(default=None, blank=True, null=True)
    sub_code = models.ForeignKey(SubCode, on_delete=models.PROTECT)
    status = models.PositiveSmallIntegerField(choices=STATUS, default=PENDING)
    published = models.BooleanField(default=False)
    custom_url = models.URLField(default=None, null=True, blank=True)
    creation_date = models.DateField(default=now)
    pdf_file = models.FileField(upload_to='uploads/certificates/', default=None, null=True, blank=True)
    internal_id = models.CharField(max_length=25, default=None, null=True, blank=True)

    ...

class Address(models.Model):
    zip = models.CharField(max_length=35, default=None, blank=True, null=True)
    city = models.CharField(max_length=100, default=None, blank=True, null=True)
    country = models.CharField(max_length=100, default=None, blank=True, null=True)
    country_code = models.CharField(max_length=10, default=None, blank=True, null=True)

    ...

    def __str__(self):
        return f"{self.city.upper()} - {self.country_code.upper()} {self.zip.upper()} - {self.country.upper()}"

    ...


class Company(models.Model):
    name = models.CharField(max_length=100)
    nickname = models.CharField(max_length=25, unique=True, help_text="Only alpha [a-z], will be transformed in lowercase, CANNOT be changed when set !")
    address = models.ForeignKey(Address, on_delete=models.PROTECT, default=None, null=True, blank=True)
    phone = models.CharField(max_length=50, default=None, blank=True, null=True)
    email = models.EmailField(default=None, blank=True, null=True)
    internal_id = models.CharField(max_length=35, default=None, null=True, blank=True)

    timestamp_updated = models.DateTimeField(auto_now=True)
    timestamp_created = models.DateField(auto_now_add=True)
    comment = models.TextField(default=None, blank=True, null=True)
    note = models.TextField(default=None, blank=True, null=True)

    def __str__(self):
        return f'{self.name.upper()} | {self.address}'

    def clean(self):
        # Check nickname is lower and only alpha
        self.nickname = str(self.nickname.lower())
        if [x for x in self.nickname if not x.isalpha()]:
            raise ValidationError({'nickname': _('nickname should only contain letter [a-z] !')})

    def save(self, *args, **kwargs):
        if self.pk:
            # make sure that we cannot change nickname
            obj = Company.objects.values('nickname').get(pk=self.pk)
            if obj.get('nickname') != self.nickname:
                self.nickname = obj.get('nickname')
        else:
            # Set nickname to None (will raise DB "nickname" violates not-null constraint) is not only alpha
            if not self.nickname:
                self.nickname = None
            elif [x for x in self.nickname if not x.isalpha()]:
                self.nickname = None
        super().save(*args, **kwargs)

    @property
    def count_published_certificate(self):
        return self.certificate_set.filter(published=True).count()

    class Meta:
        verbose_name_plural = 'Companies'
        ordering = ('name',)


class Ncf(models.Model):
    
    # SEVERITY CHOICES
    SEVERITY_A = 'A'
    SEVERITY_B = 'B'

    SEVERITY_CODE_CHOICES = [
        (SEVERITY_A, 'A'),
        (SEVERITY_B, 'B'),
    ]

    # STATUSES CHOICES

    OPEN = 0
    CLOSED = 1
    CANCELED = 2
    CERTIFICATION_COMMITTEE = 3

    STATUSES_CHOICES = [
        (OPEN, _('Open')),
        (CLOSED, _('Closed')),
        (CANCELED, _('Canceled')),
        (CERTIFICATION_COMMITTEE, _('Certification Committee')),
    ]

    certificate = models.ForeignKey('certificates.Certificate', on_delete=models.PROTECT)
    producer_representative = models.ForeignKey('address_book.Person', on_delete=models.PROTECT)
    severity = models.CharField(max_length=1, default=SEVERITY_B, choices=SEVERITY_CODE_CHOICES)
    status = models.IntegerField(default=OPEN, choices=STATUSES_CHOICES)

    # Auto or computed data

    inspector = models.ForeignKey('accounts.CustomUser', on_delete=models.PROTECT, blank=True)
    date = models.DateField(auto_now_add=True, editable=False)
    report_number = models.CharField(max_length=10, blank=True, editable=False)
    parent = models.ForeignKey('self', on_delete=models.PROTECT, default=None, null=True, blank=True, editable=False,
                               related_name='parent_ncf')
    child = models.ForeignKey('self', on_delete=models.PROTECT, default=None, null=True, blank=True, editable=False,
                              related_name='child_ncf')

In the Admin:

Admin List View of the certificate is working fine as I override the queryset with optimization

@admin.register(Certificate)
class CertificateAdmin(admin.ModelAdmin):
    model = Certificate
    ordering = ('company',)

    # Override the queryset to optimize the DB queries
    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        queryset = queryset.select_related('sub_code', 'sub_code__code', 'company', 'company__address')
        return queryset

    exclude = ('note', 'urn', 'uri',)
    list_display = ('_company_name', 'sub_code', 'internal_id', '_company_address', 'status', 'published', 'uuid', 'creation_date', '_pdf_file', 'id')
    list_filter = ('status', 'published', 'sub_code__code__name', 'sub_code__name',)
    search_fields = ('company__name', 'internal_id', 'sub_code__name', 'sub_code__code__name',)

    @staticmethod
    def _company_name(obj):
        return obj.company.name

    @staticmethod
    def _company_address(obj):
        return obj.company.address

    @staticmethod
    def _pdf_file(obj):
        if obj.pdf_file:
            return format_html('<img src="/static/admin/img/icon-yes.svg" alt="True">')
        return format_html('<img src="/static/admin/img/icon-no.svg" alt="False">')

NCF Admin list view

@admin.register(Ncf)
class NCFAdmin(admin.ModelAdmin):

    # When creating, set the current logged user as the inspector
    def save_form(self, request, form, change):
        obj = super().save_form(request, form, change)
        if not change:
            obj.inspector = request.user
        return obj

Admin add new NCF

The loading time is very long : about 30 sec.

there has been 894 queries including 888 similar and 692 duplicates
Total CPU time 28375.218 msec

Questions:

How optimize the Queryset that gets the list of certificates in the NCF add/update admin view ?
Override the queryset and use select_related and / or prefetch_related methods ?
As I’m not experienced with Django and certainly not with DB’s, I’m trying to learn about Indexes and WHEN to use them. Could my problem above be resolved by creating an Index in the DB ?

Thanks in advance for your time highly appreciated…

KenWhitesell · February 25, 2022, 1:51pm

Your second question is a quicker answer than the first.

An index does not reduce the number of queries being issued by Django. Database indexes are transparent to the applications using the database. They’re an internal data structure to the database to allow it to perform searches faster.

Now to get back to your original question, I think the first thing you should try is using the list_select_related attribute in your ModelAdmin class.

axeljacobs · February 25, 2022, 2:52pm

Thanks Ken,

Clear for the Index !

When I read the doc it seems to me that list_select_related will have a ‘effect’ on the admin change list page. But not on the “add” and “change” pages.

Maybe I need to create a custom NCFAdminForm with the ‘optimized’ queryset to retrieve the certificate object ? With something like this …

forms.py

class NCFAdminFrom(forms.Form):
    ...
    certificate = forms.ModelChoiceField(queryset=Certfificate.objects.select_related('.....'), ....)
    ...

admin.py

@admin.register(NCF)
class NCFAdmin(admin.ModelAdmin):
   form = NCFAdminFrom
   ...

KenWhitesell · February 25, 2022, 2:53pm

The add page shouldn’t be performing any queries since you’re adding a new entry, and change should only be retrieving the entity you’re changing - you might be generating 3 queries there, which is negligible in the grand scheme of things.

Oh wait, I see, the issue is with the select drop down - not in the page presentation. Yes, you’re right - you’ll want to optimize that select field.

axeljacobs · February 25, 2022, 3:31pm

For anyone interested in the solution:

I did create a custom NcfAdminForm and override the queryset of the certificate field:

forms.py

class NcfAdminForm(forms.ModelForm):
    model = Ncf
    fields = '__all__'

    def __init__(self, *args, **kwargs):
        super(NcfAdminForm, self).__init__(*args, **kwargs)
        self.fields['certificate'].queryset = Certificate.objects.select_related(
            'sub_code',
            'sub_code__code',
            'company',
            'company__address'
        ).all()

admin.py I used this custom NcfAdminForm

@admin.register(Ncf)
class NCFAdmin(admin.ModelAdmin):
    form = NcfAdminForm

The optimized queryset for this select field is optimized drastically reducing the number of queries !!

Thanks to Ken (as always… ) for the help…

plopidou · March 8, 2022, 3:26pm

Hello, a bit late but: for the add/edit page in the django admin, you might want to make use of autocomplete fields: The Django admin site | Django documentation | Django

I have found them (or third-party modules such as dal) to be immensely helpful for ForeignKey model fields – instead of loading pulling down the whole table, just your currenctly assigned ID will be defined – and the queryset is then lazyly loaded on demand.

End result is a massively reduced load time for model edit pages that are linked to foreign keys that:
. point to tables taht have many may rows (the more rows, the more obvious this becomes)
. point to objects whose str representation itself pulls data from yet other foreign key related objects (thus yet increasing the number of queries).

axeljacobs · March 8, 2022, 4:38pm

I ended in using raw_id_fields.

I will test your proposal…

Thanks sharing.

Topic		Replies	Views
SQL Queries optimization for a model method Using Django	2	573	April 27, 2021
Optimizing the DB Query with Indexes and includes ORM	2	476	May 11, 2023
Need Help to optimize the query that returns the list of products Using the ORM	3	33	July 26, 2024
Query performance question Using Django	15	2887	September 15, 2021
Question about db_index=False and composite indices Using the ORM	10	1585	August 9, 2022

Optimize DB query with Index

Related topics