Django: Maybe disable PostgreSQL’s JIT to speed up many-joined queries

Here’s a write-up of an optimization I made in my client Silvr’s project. I ended up disabling a PostgreSQL feature called the JIT (Just-In-Time) compiler which was taking a long time for little benefit.

This behaviour was observed on PostgreSQL 14, so things may have improved since. Although the version 15 and 16 release notes don’t seem to contain any relevant-looking changes.

Okay, story time…

I was looking at the project’s slowest tests and found one that took five seconds despite containing few lines of code. Profiling the test revealed a single slow query, which was also slow in production. I grabbed the query plan and fed it to pgMustard using the technique I previously blogged about):

pgMustard reported that JIT compilation of the query was taking 99.7% of the 3217ms runtime. The five-star rating for this finding meant that it was ripe for optimization. But why was JIT compilation taking so long?

The query used InheritanceManager from django-model-utils, which extends Django’s multi-table inheritance to return heterogenous subclasses of a base model:

>>> Place.objects.select_subclasses()
[<Library: Downtown Books>, <BookShop: Giovanni’s>]

The generated query joins all subclasses’ tables. With many subclasses, that query has many joins. But each join only adds a few rows to the results since each object is of only one leaf subclass.

However, PostgreSQL estimated that the large number of joins would mean many results, making it decide to JIT compile the query. JIT compiling a query takes a while, which is typically offset by the time savings. But with few results, this query took three seconds to JIT compile but milliseconds to execute.

pgMustard recommended either improving PostgreSQL’s configuration to reduce cost estimates to prevent JIT compiling, or disabling the JIT altogether. I found the number of cost estimation options bamboozling, so I gave up on that and tried disabling the JIT. I at least found a PostgreSQL mailing list post reporting similar JIT misbehaviour with low result counts, which was some reassurance that disabling the JIT wasn’t such a bad idea.

To disable the JIT, I extended the DATABASES setting’s OPTIONS with psycopg’s options to disable PostgreSQL’s jit setting, with a hefty explanatory query:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        # ...
        "OPTIONS": {
            # Disable PostgreSQL JIT
            # https://www.postgresql.org/docs/current/jit.html
            # django-model-utils’ select_subclasses() method joins a bunch of
            # tables, which PostgreSQL sees as high cost and JIT’s. But each
            # table will only match zero or one row per base class row, so this
            # cost estimate is vastly wrong, and JIT compiling costs a lot more
            # than it’s worth (e.g. 3.5 seconds with JIT vs 10 milliseconds
            # without). We’re not the only ones to encounter such a problem:
            # https://www.postgresql.org/message-id/70ec49f7-e9c4-bea7-c689-a4dfbdd66f78%40jurr.org
            # Using libq 'options' param, via psycopg:
            # https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-OPTIONS
            "options": "-c jit=off",
        },
    },
}

This option could also be set in the server configuration, but putting it in Django’s settings is fast and guaranteed to apply to all environments.

The change sped up the individual test to take milliseconds. Overall, the test suite went from 106 seconds to 94 seconds, an 11% improvement.

There was a risk of performance regressions in production, so we monitored when deploying. Thankfully, things only improved :) It seems the JIT wasn’t providing much value for this project.

Learn how to make your tests run quickly in my book Speed Up Your Django Tests.

One summary email a week, no spam, I pinky promise.

Related posts:

• How to optimize PostgreSQL queries from Django using pgMustard

Tags: django, postgresql