On Saturday, July 5, 2014 12:42:39 AM UTC+3, Jon Dufresne wrote:
>
> Suppose I have the following models:
>
> ---
> class Container(models.Model):
> pass
>
> class Item(models.Model):
> container = models.ForeignKey(Container)
> previous = models.ForeignKey('self', null=True)
> current = models.BooleanField()
> flag = models.BooleanField()
> ---
>
> Item represents a chain of items, all grouped by a container (like a
> linked list). The field "current" represents the most recent item
> (front of the list). The field "flag" is simply something to query on.
>
> Suppose I perform the following query:
>
> ---
> Item.objects.filter(current=True,
> container__item__previous__isnull=True, container__item__flag=True)
> ---
>
> That is, I'm looking for all current items such that the first item in
> the chain has flag = true. Django 1.6 produces the following SQL for
> this query:
>
> ---
> SELECT ...
> FROM "myapp_item"
> INNER JOIN "myapp_container"
> ON ( "myapp_item"."container_id" = "myapp_container"."id" )
> LEFT OUTER JOIN "myapp_item" T3
> ON ( "myapp_container"."id" = T3."container_id" )
> WHERE ("myapp_item"."current" = True AND T3."previous_id" IS NULL AND
> T3."flag" = True )
> ---
>
> The OUTER JOIN is the problem. Why is this not a more efficient INNER
> JOIN? This query is very inefficient as the the database gets larger.
> This causes slow downs on pages.
>
This have been fixed in 1.7. Pre-1.7 there was no join "demotion". That is,
when the ORM generated a LEFT JOIN expression it was never changed back to
INNER JOIN. Here the container__item__previous__isnull=True generates LEFT
JOIN for the item join.
In 1.7 there exists some new code to do join demotion. In this case it
works because container__item__flag=True can produce results only when the
item join produces results => the query never produces results with LEFT
JOIN of item => the existing LEFT JOIN for alias T3 can be "demoted" to
INNER JOIN.
Interestingly, if I try this on 1.7, this query is apparently not even
> supported. I get the following error:
>
I wasn't able to reproduce this - are you sure the query is exactly the
same both on 1.6 and 1.7? Are models are set up correctly before generating
the query? If after double-checking you still get the error, could you post
a sample project somewhere?
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" 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-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/d9253e4a-df9c-4e75-bc5a-3f883a62db0f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
