#23440: select_related with multiple ForeignKey to same table generates
sub-optimal
SQL
-------------------------------------+-------------------------------------
Reporter: djbug | Owner: nobody
Type: | Status: new
Cleanup/optimization | Version: 1.6
Component: Uncategorized | Resolution:
Severity: Normal | Triage Stage:
Keywords: select_related | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by djbug):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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
New description:
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)
}}}
{{{#!sql
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:
{{{
#!sql
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 : [7125]
--
--
Ticket URL: <https://code.djangoproject.com/ticket/23440#comment:1>
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/063.f7b90b7093fb2a5f549517b64b67dd76%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
