Nifty Django Feature: Use Exists with Nested QuerySets

This feature is centered on filtering with a nested query. I’m going to make the case that when you are doing a nested query and that nested query is complicated, you should use the Exists expression.

When needing to do an lookup with a nested query, you always start with the __in lookup.

from my_app.models import Pet, Species

complicated_species_filter = Species.objects.filter(id__lt=500)
Pet.objects.filter(species__in=complicated_species_filter).values("id")

We’ll see it’s using an IN operation in the WHERE clause in the generated SQL.

SELECT "myapp_pet"."id" AS "id"
  FROM "myapp_pet"
 WHERE "myapp_pet"."species_id" IN (
        SELECT U0."id"
          FROM "myapp_species" U0
         WHERE U0."id" < 500
       )

Instead, if we swap this with an Exists expression do to the lookup, we can potentially get better performance.

from my_app.models import Pet, Species

complicated_species_filter = Species.objects.filter(id__lt=500)
Pet.objects.filter(
    Exists(complicated_species_filter.filter(id=OuterRef("species_id")))
).values("id")

The SQL that this generates shows that it uses an EXISTS operation instead.

SELECT "myapp_pet"."id" AS "id"
  FROM "myapp_pet"
 WHERE EXISTS(
        SELECT 1 AS "a"
          FROM "myapp_species" U0
         WHERE (U0."id" < 500 AND U0."id" = ("myapp_pet"."species_id"))
         LIMIT 1
       )

I’ve found that this can improve performance when the nested query (in this case, complicated_species_filter) is complicated. What determines “complicated”? You’ll know it when you see it. When you look at it, you’ll remark, “wow, that’s complicated!”

A little more tangibly though, the scenarios where I saw performance improvements where there were several joins being made with a few additional where clause lookups on tables that were in the 100k-10M rows range.

If you have a slow query that’s using __in, consider swapping it out with Exists, but please measure before and after using explain analyze. In simple cases, using Exists will degrade performance, so if you’re not sure if it’s improving things, it’s possible you’re making them worse.

If you have thoughts, comments, or questions, please let me know. You can set up a meeting with me, or find me on the Fediverse, Django Discord server or use email.