Opened 10 years ago

Last modified 10 years ago

#23440 closed Cleanup/optimization

select_related with multiple ForeignKey to same table generates sub-optimal SQL — at Version 1

Reported by: djbug Owned by: nobody
Component: Uncategorized Version: 1.6
Severity: Normal Keywords: select_related
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by djbug)

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 : [7125]

Change History (1)

comment:1 by djbug, 10 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top