Changes between Initial Version and Version 2 of Ticket #17276


Ignore:
Timestamp:
11/25/2011 07:27:17 AM (4 years ago)
Author:
ramiro
Comment:

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #17276

    • Property Patch needs improvement unset
    • Property Triage Stage changed from Unreviewed to Accepted
    • Property Needs tests unset
    • Property Needs documentation unset
  • Ticket #17276 – Description

    initial v2  
    66Basically, in a many-to-many mappings between models Student and Course, if I want to find all instances of Students that aren't registered for classes, I would issue the following Django query:
    77
     8{{{
    89Student.objects.filter(course__isnull=True)
     10}}}
    911
    1012Django translates this into the following query:
    11 
     13{{{
    1214SELECT "student"."id", "student"."name"
    1315   FROM "student"
     
    1719         ON ("course_students"."course_id" = "course"."id")
    1820   WHERE "course"."id" IS NULL
    19 
     21}}}
    2022The problem is that the way the WHERE clause is generated is very inefficient (at least when used with Postgres). Changing WHERE to "course_students"."student_id" IS NOT NULL yields orders of magnitude improved query plan.  Here's the difference I'm seeing on real data:
    2123
    22 Django way: (cost=1072479.36..6256437.79 rows=1 width=145)
    23 Hand-crafted: (cost=1518.71..1533.35 rows=1 width=145)
     24* Django way: (cost=1072479.36..6256437.79 rows=1 width=145)
     25* Hand-crafted: (cost=1518.71..1533.35 rows=1 width=145)
    2426
    2527I'm attaching a sample project with the model already set up.  To see the generated SQL query, simply run "python manage.py anti-join."
Back to Top