Filtering by forkeign keys add unnecessary table joins
|Reported by:||TwelveSixty||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.3|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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.
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.