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
27260	Performance Issue because of LEFT OUTER JOIN instead the better INNER JOIN	Sven R. Kunze	nobody	"
Original Query:

{{{
BigTable.objects.filter(
    Q(tablea__item_id__in=my_items) | Q(tableb__item_id__in=my_items)
)
}}}

resulting in:

{{{
SELECT *
FROM ""big_table""
  LEFT OUTER JOIN ""tablea"" ON (""big_table"".""id"" = ""tablea"".""big_table_id"")
  LEFT OUTER JOIN ""tableb"" ON (""big_table"".""id"" = ""tableb"".""big_table_id"")
WHERE (""tablea"".""item_id"" IN (<handful of items>) OR
       ""tableb"".""item_id"" IN (<handful of items>))
}}}

The {{{LEFT OUTER JOIN}}} combined with a growing number of rows in big_table results in very slow execution of the query.
We temporarily rewrote the query by using two separate queries and merging the sets via Python:


{{{
BigTable.objects.filter(
    Q(tablea__item_id__in=my_items)
)
}}}
{{{
SELECT *
FROM ""big_table""
  INNER JOIN ""tablea"" ON (""big_table"".""id"" = ""tablea"".""big_table_id"")
WHERE ""tablea"".""item_id"" IN (<handful of items>)
}}}

and

{{{
BigTable.objects.filter(
    Q(tableb__item_id__in=my_items)
)
}}}
{{{
SELECT *
FROM ""big_table""
  INNER JOIN ""tableb"" ON (""big_table"".""id"" = ""tableb"".""big_table_id"")
WHERE ""tableb"".""item_id"" IN (<handful of items>)
}}}

Ideally, we would like to use the original query because it is clearer and less code to maintain. If you want to see the models, I can post them as well. Ideally again, we don't actually expect models to influence the type of the joins.

We want BigTable to be filtered, so, if there's no appropriate match on the TableA or TableB, the BigTable item simply does not appear in the result set. However, {{{LEFT OUTER JOIN}}} would result in such a row which then needs to be removed afterwards laboriously (at least PostgreSQL struggles to do so). If the original query had used {{{INNER JOIN}}}, the query would have been fast."	Cleanup/optimization	closed	Database layer (models, ORM)	1.8	Normal	invalid			Unreviewed	0	0	0	0	0	0