Today I was looking for the culprit of the performance issue that was affecting a Django Admin page that wasn’t loading. I know from experience that most of the time this is a problem of unoptimized queries, and the best weapon I know to have a look into those in a Django app is to use the Django Debug Toolbar.

When you can use that tool inside the app and reproduce the problem, if the problem is not so bad that the page fails to load, you can quickly spot what is the problematic query and typically the problem will be self-evident.

The problem I had is:

• I didn’t have much data locally, the problem only happened in preprod/prod
• preprod/prod don’t have Django Debug Toolbar

However, I was able to inspect the queries generated by Django ORM with the tool on my local computer, and I could run the EXPLAIN queries for it on preprod to help me find the problem.

So I looked into the query plan of the biggest query, and it had no sequential scans, but it had a Bitmap Heap Scan. I had no idea what it was, but it didn’t seem so bad.

I dug a bit to have a better idea of what it was, and it’s a rather smart algorithm, a bit of an in-between between index scans and sequential scans.

There is a nice explanation on the Postgresql mailing list.

Three main points I learned:

• Bitmap Heap Scan is something in-between an index scan and a sequential scan
• It is backend by a Bitmap index data structure
• when it has a “Recheck Condition”, it means that the bitmap index got too large and it will not be able to track each tuple, and thus will have to “re-run” the condition checks later (lots of mini sequential scans)
• it tends to win (against index scans and sequential scans) when you’re fetching a big number of rows, but not a large percentage of the table.

As Postgresql developer Tom Lane says on the mailing list message linked above:

A rule of thumb is that plain indexscan wins for fetching a small number of tuples, bitmap scan wins for a somewhat larger number of tuples, and seqscan wins if you’re fetching a large percentage of the whole table.

Fixing the performance issue

Now, as to my performance problem: in my case, as it was a Django admin page, we only display a rather small amount of rows, so the Bitmap Heap Scan is a problem – not much different than a sequential scan.

The fix was to optimize that query until it only used index scans.

The code to change was like this:

queryset = queryset.exclude(id__in=...subquery here...)

The above results in a SQL like this:

SELECT
    field1, field2
FROM
    thing_table
WHERE
    ...
    AND id NOT IN (AnotherModel.objects.values_list("thing_pk", flat=True))

Note that NOT IN (...subquery...) condition <- this was what was triggering the Bitmap Heap Scan.

I replaced it by:

queryset = (
    queryset.annotate(
        temp_column=Exists(AnotherModel.objects.filter(thing_pk=OuterRef("pk"))
    )
    .filter(temp_column=False)
)

… which in SQL will be like:

SELECT
    field1, field2, ...
    EXISTS(...subquery here...) AS temp_column
FROM
    thing_table
WHERE
    ...
HAVING
    temp_column = FALSE

… which avoided the scan.