Django ORM Need help speeding up query, connected to additional tables

Question

Running Django 1.6.5 (very old i know but can't upgrade at the moment since this is production).

I'm working on a view where I need to perform a query and get data from a couple other tables which have the same field on it (though on the other tables the ord_num key may exist multiple times, they are not foreign keys).

When I attempt to render this queryset into the view, it takes a very long time.

Any idea how i can speed this up?

Edit: The slowdown seems to be from the pickconn lookup but i can't speed it up. The Oracle DB itself doesn't have foreign keys on the Pickconn table but i figured it could speed things up in Django...

view queryset:

qs = Outordhdr.objects.filter(
    status__in=[10, 81],
    ti_type='@'
).exclude(
    ord_num__in=Shipclosewq.objects.values('ord_num')
).filter(
    ord_num__in=Pickconhdr.objects.values_list('ord_num', flat=True)
).order_by(
    'sch_shp_dt', 'wave_num', 'shp_dock_num'
)

Models file:

class Outordhdr(models.Model):
    ord_num = models.CharField(max_length=13, primary_key=True)

    def get_conts_loaded(self):
        return self.pickcons.filter(cont_dvrt_flg__in=['C', 'R']).aggregate(
            conts_loaded=models.Count('ord_num'),
            last_conts_loaded=models.Max('cont_scan_dt')
        )

    @property
    def conts_left(self):
        return self.pickcons.exclude(cont_dvrt_flg__in=['C', 'R']).aggregate(
            conts_left=models.Count('ord_num')).values()[0]

    @property
    def last_conts_loaded(self):
        return self.get_conts_loaded().get('last_conts_loaded', 0)

    @property
    def conts_loaded(self):
        return self.get_conts_loaded().get('conts_loaded', 0)

    @property
    def tot_conts(self):
        return self.conts_loaded + self.conts_left

    @property
    def minutes_since_last_load(self):
        if self.last_conts_loaded:
            return round((get_db_current_datetime() - self.last_conts_loaded).total_seconds() / 60)

    class Meta:
        db_table = u'outordhdr'

class Pickconhdr(models.Model):
    ord_num = models.ForeignKey(Outordhdr, db_column='ord_num', max_length=13, related_name='pickcons')
    cont_num = models.CharField(max_length=20, primary_key=True)

    class Meta:
        db_table = u'pickconhdr'

question from:https://stackoverflow.com/questions/65844892/django-orm-need-help-speeding-up-query-connected-to-additional-tables

Answer 1

From reading this query and looking at the documentation it seems like the best way to optomise would be to add indexes onto non unique fields, in this case i would recommend to index:

order_num, ti_type, status, sch_shp_td, wave_num and shp_dock_num

doing this will increase lookup speed for all of these fields which should in turn allow the queryset to run faster.