I too would be interested to know if there is a solution. A LEFT OUTER JOIN can really hurt performance when an INNER JOIN are more appropriate,.
On Mon, Jul 15, 2013 at 8:41 AM, Carsten Fuchs <[email protected]>wrote: > Hi all, > > we have two queries/QuerySets Q_a and Q_b, each of which use INNER joins > in the generated SQL when evaluated individually. > > When I use a Q object to "OR" these two QuerySets, the INNER joins turn > into LEFT OUTER joins -- which in turn cause a huge performance drop > (several hundred times slower than with INNER joins). > > Why is this, and can I do anything against it at the Django ORM level? > > > Details: > > My environment: > Django 1.5.1 on Ubuntu 10.04 (Python 2.6.5) with Oracle database 10g > > > FirstDay = date(2013, 5, 1) > LastDay = date(2013, 5, 31) > SomeDep = ... # the department with ID == 1 > > > Q_a = Staff.objects. \ > filter(bereiche=SomeDep).**distinct() > > Q_b = Staff.objects. \ > filter(erfasst__datum__gte=**FirstDay, > erfasst__datum__lte=LastDay, > erfasst__bereich=SomeDep).**distinct() > > Q_a_or_b = Staff.objects. \ > filter(Q(bereiche=SomeDep) | > Q(erfasst__datum__gte=**FirstDay, > erfasst__datum__lte=LastDay, > erfasst__bereich=SomeDep)).**distinct() > > > In the following output, to improve readability I used the "*" to manually > shorten the list of selected fields: > > > print Q_a.query > > SELECT DISTINCT "STAFF".* FROM "STAFF" > INNER JOIN "STAFF_BEREICHE" ON ("STAFF"."ID" = "STAFF_BEREICHE"."STAFF_ID") > WHERE "STAFF_BEREICHE"."BEREICH_ID" = 1 > > > print Q_b.query > > SELECT DISTINCT "STAFF".* FROM "STAFF" > INNER JOIN "ERFASST" ON ("STAFF"."KEY" = "ERFASST"."KEY") > WHERE ("ERFASST"."DATUM" <= 2013-05-31 AND "ERFASST"."DATUM" >= > 2013-05-01 AND "ERFASST"."BEREICH_ID" = 1 ) > > > print Q_a_or_b.query > > SELECT DISTINCT "STAFF".* FROM "STAFF" > LEFT OUTER JOIN "STAFF_BEREICHE" ON ("STAFF"."ID" = > "STAFF_BEREICHE"."STAFF_ID") > LEFT OUTER JOIN "ERFASST" ON ("STAFF"."KEY" = "ERFASST"."KEY") > WHERE ("STAFF_BEREICHE"."BEREICH_ID" = 1 OR ("ERFASST"."DATUM" <= > 2013-05-31 AND "ERFASST"."DATUM" >= 2013-05-01 AND "ERFASST"."BEREICH_ID" > = 1 )) > > > In the last SQL statement, when I replace "LEFT OUTER JOIN" with "INNER > JOIN" and manually run it, the result seems to be the same, but the query > completes one hundred to several hundred times faster. > > So, from the above observations, I was wondering if the use of "LEFT OUTER > JOIN" is what is supposed to happen for Q_a_or_b in the first place? Is it > the expected behavior? > And if so, can it be changed via the Django ORM? > > Many thanks for your help! > > Best regards, > Carsten > > > > -- > Dipl.-Inf. Carsten Fuchs > > Carsten Fuchs Software > Industriegebiet 3, c/o Rofu, 55768 Hoppstädten-Weiersbach, Germany > Internet: http://www.cafu.de | E-Mail: [email protected] > > Cafu - the open-source game and graphics engine for multiplayer 3D action > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to > django-users+unsubscribe@**googlegroups.com<django-users%[email protected]> > . > To post to this group, send email to [email protected]. > Visit this group at > http://groups.google.com/**group/django-users<http://groups.google.com/group/django-users> > . > For more options, visit > https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> > . > > > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-users. For more options, visit https://groups.google.com/groups/opt_out.
