Re: or-ing to QuerySets turns INNER into LEFT OUTER joins?

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 <carsten.fuchs@cafu.de> 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: info@cafu.de

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.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit 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 django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.