Changes between Initial Version and Version 2 of Ticket #25464


Ignore:
Timestamp:
Sep 25, 2015, 6:51:46 AM (9 years ago)
Author:
Tim Graham
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #25464

    • Property Needs documentation set
    • Property Needs tests set
    • Property Triage Stage Unreviewed → Accepted

  • Ticket #25464 – Description

    initial v2  
    11When using prefetch_related() on a large queryset, the prefetch query SQL can be inefficient. Consider this:
    2 
     2{{{
    33    Category.objects.filter(type=5).prefetch_related('items')
    4 
     4}}}
    55If 100.000 categories have type=5, then an IN clause with 100.000 Category IDs is generated to get the Item objects. Even with a custom queryset using a Prefetch() object, the IN clause is generated, even though it is A) redundant, B) sends a potentially multi-megabyte SQL statement over the wire for the database to process, C) may confuse the query planner to generate an inefficient execution plan, and D) doesn't scale:
    6 
     6{{{
    77    Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__item=5)))
    8 
     8}}}
    99Pull request https://github.com/django/django/pull/5356 adds the possibility to skip the IN clause in cases where we are sure that a better queryset will get (at least) the same items as the IN clause would:
    10 
     10{{{
    1111    Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__item=5), filter_on_instances=False))
    12 
     12}}}
    1313In my tests, this speeds up prefetch_related() by 20x-50x on large querysets.
Back to Top