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

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.