Changes between Version 2 and Version 5 of Ticket #25464


Ignore:
Timestamp:
Sep 27, 2015, 10:34:57 AM (9 years ago)
Author:
Shai Berger
Comment:

Two notes come to mind:

1) While this may be less "natural" to think of, it seems the query would be more natural and efficient as 

    Item.objects.filter(category__type=5).select_related('category')

Of course, this would require restructuring the code that handles the items and categories.

2) Not sure if this is as easy, but I think a better and more general alternative would be to expose the "joining-in-python" mechanism for general use. I'm thinking along the lines of 

    cats = Category.objects.filter(type=5)
    items = Item.Item.objects.filter(category__type=5)
    cats.use_prefetched('items', items)

where 'items' is the name of the reverse relation, of course, and items could be replaced with any iterable returning Item instances.

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #25464

    • Property Cc Simon Charette Shai Berger added

  • Ticket #25464 – Description

    v2 v5  
    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:
    66{{{
    7     Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__item=5)))
     7    Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__type=5)))
    88}}}
    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:
    1010{{{
    11     Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__item=5), filter_on_instances=False))
     11    Category.objects.filter(type=5).prefetch_related(Prefetch('items', queryset=Item.objects.filter(category__type=5), filter_on_instances=False))
    1212}}}
    1313In my tests, this speeds up prefetch_related() by 20x-50x on large querysets.
Back to Top