How we reduced a postgreSQL query response time by 99% in Django

Django makes it very easy to create great products quickly, but sometimes fixing performance issues requires diving a little deeper.

For example, here’s a simple function to return the most recently active matching item:

def get_most_recently_updated_item(self, title):
   return Item.objects.filter(Q(title__iexact=title)).order_by('-last_updated').first()

In this example we’re making a case-insensitive match on title, and then ordering by when they were updated. This looks pretty innocuous, yet we noticed SQL queries were taking 400ms+ in some cases

We already have indexes on both title and last_updated , so there has to be a deeper problem

Let’s take a closer look. You can grab the underlying SQL query here either through Django-debug-toolbar or Silk. Once you have the query, you can boot up psql and run the query there. By adding EXPLAIN to the start, we can see what postgres is attempting to do.

explain SELECT *
FROM "core_item"
WHERE UPPER("core_item"."title"::text) = UPPER('textToMatch'')
ORDER BY "core_item"."last_updated" DESC           
LIMIT 1;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..9.93 rows=1 width=431)
   ->  Index Scan Backward using core_user_last_updated_0521b8_idx on core_item  (cost=0.42..5239.66 rows=551 width=431)
         Filter: (upper((title)::text) = 'TEXTTOMATCH'::text)
(3 rows)

This is at least using our last_updated index, but is still slow

The answer is to add an index for this particular use-case. We need an index that works with both our ordering field AND the case-insensitive title. In psql we can run

CREATE INDEX core_item_upper_last_updated_idx ON core_item (upper(title), last_updated DESC);

Running the query again now gives

EXPLAIN SELECT *
FROM "core_item"
WHERE UPPER("core_item"."title"::text) = UPPER('textToMatch')
ORDER BY "core_item"."last_updated" DESC
LIMIT 1;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..3.87 rows=1 width=431)
   ->  Index Scan using core_item_upper_last_updated_idx on core_item  (cost=0.42..1902.06 rows=551 width=431)
         Index Cond: (upper((title)::text) = 'TEXTTOMATCH'::text)
(3 rows)
Time: 10.162 ms

This performs much better! We can in fact go one better than this. Looking at last_updated in prod suggests about 50% of last_updated is null — a hangover from before adding this feature.

We can improve the index further by using

CREATE INDEX core_item_upper_last_updated_idx ON core_item (upper(title), last_updated DESC NULLS LAST);

In Django:

def get_most_recently_updated_item(self, title):
   return Item.objects.filter(Q(title__iexact=title)).order_by(F('last_updated').desc(nulls_last=True)).first()

The final step is to blend this index into a migration. Django 3.2 actually has support for adding special indexes like DESC and on the model. Failing that, we can add the SQL directly to the migration, like this:

operations = [
    migrations.RunSQL(
      [('CREATE INDEX core_item_upper_last_updated_idx ON core_item (upper(title), last_updated DESC NULLS LAST);')],
      [('DROP INDEX core_item_upper_last_updated_idx;')]
    )
]

Making this change brings our query time down from 400ms+ down to 4ms!