id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
17389	Filtering by forkeign keys add unnecessary table joins	TwelveSixty	nobody	"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."	Bug	closed	Database layer (models, ORM)	1.3	Normal	invalid			Unreviewed	0	0	0	0	0	0