[Django] #23440: select_related with multiple ForeignKey to same table generates sub-optimal SQL

58 views

Skip to first unread message

Django

unread,

Sep 6, 2014, 1:15:50 PM9/6/14

to django-...@googlegroups.com

#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.

Django

unread,

Sep 6, 2014, 1:22:45 PM9/6/14

to django-...@googlegroups.com

#23440: select_related with multiple ForeignKey to same table generates sub-optimal
SQL

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

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

unread,

Sep 8, 2014, 5:16:53 AM9/8/14

to django-...@googlegroups.com

#23440: select_related with multiple ForeignKey to same table generates sub-optimal
SQL

-------------------------------------+-------------------------------------
Reporter: djbug | Owner: nobody
Type: | Status: closed
Cleanup/optimization | Version: 1.6
Component: Uncategorized | Resolution: invalid

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 akaariai):

* status: new => closed
* resolution: => invalid

Comment:

I don't think that actually works. Assume the following on node table:
||id || name
||1 || node 1
||2 || node 2

And relation has one model with left_id = 1 and right_id = 2. Now, the
second query produces results
||id || left_id|| right_id|| node.id || name
||1 || 1 || 2 || 1 ||node 1
||1 || 1 || 2 || 2 ||node 2

Which is not the wanted result. Instead we want all the related objects in
a single row. Trying to collapse the two rows into a single object while
iterating leads to all sorts of really complex issues which we are not
going to tackle.