Filtering by forkeign keys add unnecessary table joins

[TwelveSixty]

Using a filter on a foreign key, even if you do not use any fields on the foreign model results in an unnecessary join in the SQL query, resulting in lower than optimal performance.

In the example below I perform a simple filter on a model to filter all rows which do not have the foreign key (names have been simplified for clarity). Yet even though I do not use the foreign key model, it still has a LEFT OUTER JOIN in the SQL query.

MyModel.objects.filter(foreignKey__isnull=True).count()

SELECT COUNT(*)
FROM "mymodel"
LEFT OUTER JOIN "foreignmodel" ON ("mymodel"."foreignKey_id" = "foreignmodel"."id")
WHERE "foreignmodel"."id" IS NULL

I have resorted to using an extra where clause manually forcing it to negate the extra JOIN.

MyModel.objects.extra(where=['"foreignKey_id" IS NULL']).count()

SELECT COUNT(*)
FROM "mymodel"
WHERE "foreignKey_id" IS NULL

As you can see this removes the LEFT OUTER JOIN and as a result performs much better (especially if this is used to filter on many foreign keys)

Hopefully I am just incorrectly using the queryset, but I would much rather stray away from using manual SQL clauses if possible.

Thanks.

[TwelveSixty]

The second I posted this I tried the following and it works perfectly :)

MyModel.objects.exclude(foreignKey__isnull=False).count()

Obviously I need to get used to using excludes as well as filters.

Is this the recommended way?

[TwelveSixty]

On further investigation on my previous post, this performs a double NOT in the SQL query and severely reduces performance as well :(

[Anssi Kääriäinen]

The double NOT is what you get in the current ORM implementation. I assume you get WHERE NOT (fk_id IS NOT NULL). Correcting this is not trivial, but I think there is some interest in the community to fix this.

As I haven't actually tested anything, and I think this is a known problem, I will leave this as closed.