#23440: select_related with multiple ForeignKey to same table generates
sub-optimal
SQL
--------------------------------------+----------------------------
Reporter: djbug | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Uncategorized | Version: 1.6
Severity: Normal | Keywords: select_related
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------------+----------------------------
I have this model where Relation has 2 foreign keys, both to Node.
{{{
class Node(models.Model):
name = models.CharField(max_length=25)
class Relation(models.Model):
left = models.ForeignKey(Node)
right = models.ForeignKey(Node, related_name='right')
}}}
I've shown a sample query along with the SQL that's generated by Django:
{{{
Relation.objects.select_related().filter(id=1)
SELECT "relation"."id", "relation"."left_id", "relation"."right_id",
"left"."id", "left"."name", T3."id", T3."name",
FROM "relation" INNER JOIN "left" ON ( "relation"."left_id" = "left"."id"
) INNER JOIN "right" T3 ON ("relation"."right_id" = T3."id" )
WHERE "relation"."id" = 1
}}}
There are 2 INNER JOIN happening above. However we can do with just 1
INNER JOIN, since left & right are pointing to the same table. See below:
{{{
SELECT "relation"."id", "relation"."left_id", "relation"."right_id",
"left"."id", "left"."name",
FROM "relation" INNER JOIN "left" ON ( "relation"."left_id" = "left"."id"
OR "relation"."right_id" = "left"."id")
WHERE "relation"."id" = 1
}}}
P.S. Not sure if this is related :
https://code.djangoproject.com/ticket/7125
--
Ticket URL: <https://code.djangoproject.com/ticket/23440>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.96ecc792cf5792a6152c08108b60a820%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
