Code

Changes between Initial Version and Version 2 of Ticket #17276


Ignore:
Timestamp:
11/25/11 05:27:17 (2 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."