#27260: Performance Issue because of LEFT OUTER JOIN instead the better INNER 
JOIN
----------------------------------------------+--------------------
     Reporter:  Sven R. Kunze                 |      Owner:  nobody
         Type:  Cleanup/optimization          |     Status:  new
    Component:  Database layer (models, ORM)  |    Version:  1.8
     Severity:  Normal                        |   Keywords:
 Triage Stage:  Unreviewed                    |  Has patch:  0
Easy pickings:  0                             |      UI/UX:  0
----------------------------------------------+--------------------
 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/27260>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/050.5cee37182468062c6c596b55965c30c3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to