Discussion on: Django: Why you might want to set up extra DB level indexes for your ManyToMany relationships

[SHxKM]

Very interesting. I think I might be hitting the same issue as you had with a join between two fairly large tables. I think that nothing else explains the speed issues I'm facing, and why PostgreSQL's planner is ignoring the indices I do have. Care to elaborate a bit more on this point?

The way of putting this key manually on the SQL level varies by developer, I'll leave it to you. I did it with putting custom logic into my apps.py on the module that contains the models.py, which will execute on Django checks (and local runserver).

[László Károlyi]

Oh, I almost forgot to reply to you.

In my apps.py under the django module that contains the models.py with the model, I added the following functions, which is a result of me experimenting around until I found out the right way of doing it. It is basically putting together the SQL for creating the index via MariaDB:

from django.apps import AppConfig
from django.conf import settings
from django.core.checks import Info, Tags, register
from django.db import connection
from django.db.models.signals import post_migrate
from django.db.utils import OperationalError
from django.utils.translation import ugettext_lazy as _


def add_applieddiscount_sr_index() -> list:
    """
    As discussed in the link below, add an index to `seatreservation_id`
    on the `ManyToMany` `AppliedDiscount` relation.
    https://dev.to/karolyi/django-why-you-might-want-to-set-up-extra-db-level-indexes-for-your-manytomany-relationships-1ecg
    """
    from .models import SeatReservation
    errors = []
    m2m_model = SeatReservation.applied_discounts.through
    db_table = m2m_model._meta.db_table
    field_name = m2m_model._meta.get_field('seatreservation').column
    cursor = connection.cursor()
    try:
        cursor.execute(
            f'ALTER TABLE `{db_table}` ADD INDEX sr_id_index (`{field_name}`)')
    except OperationalError as exc:
        if exc.args[0] == settings.MARIADB_INDEX_ALREADY_EXISTS_CODE:
            # Index already exists
            return errors
        raise
    errors.append(Info(msg='Added index on SeatReservation.applied_discounts'))
    return errors


def post_migrate_ensure_defaults(**kwargs):
    """
    Run the default models installer when it is our application's
    `post_migrate` signal.
    """
    if not isinstance(kwargs['sender'], BookingConfig):
        # Running from migration post-signal, but not from our app's
        # signal
        return
    return ensure_defaults()


def ensure_defaults(**kwargs):
    'Run the default setter and maintenance functions.'
    errors = []
    errors.extend(add_applieddiscount_sr_index())
    return errors


class BookingConfig(AppConfig):
    name = f'project.booking'
    verbose_name = _('Project: Booking')
    label = f'project_booking'

    def ready(self):
        'Fix database, run maintenance scripts, etc ...'
        post_migrate.connect(
            post_migrate_ensure_defaults,
            dispatch_uid=f'{self.label}.ensure_defaults'
        )
        register(ensure_defaults, Tags.models)

Also, you'll have to modify the init.py next to the apps.py to contain the following lines:

from django.conf import settings

default_app_config = 'project.booking.apps.BookingConfig'

Where the path in default_app_config is the python import path to your apps.py.