#27388: Filter chaining results in unnecessary joins (and degenerate
performance)
-------------------------------------+-------------------------------------
Reporter: Connor Osborn | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: queryset, join,
Severity: Normal | performance
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
I found that `filter(testA).filter(testB)` produced very different sql
than `filter(testA & testB)`.
For my application I did a small test to reproduce this. In the example
linked I print the sql formatted. The chained filters produce many
unnecessary joins.
https://gist.github.com/cdosborn/cb4bdfd0467feaf987476f4aefdf7ee5
The joins are created in the first place because of the foreign key
`tags__name` requires a join with a Tags table.
The popular ''django rest framework'' library provides a search mechanism
that uses chained filters in django. Our application used that library and
experienced an incredible slowdown. The many unnecessary joins resulted in
a massive table (more than 1 million rows) when the original Application
table had only 1000 rows.
I think the correct way forward is to make those two queries that are
logically equivalent produce the same sql. I have done some digging in the
code base and found a patch that fixes the sql but is **not** a proper fix
(printed below). I'm willing to take up the work to fix this properly, but
I will probably need some assistance.
There is only one use case for `filter_is_sticky` (located in model code)
and it looks like a hack. If you look at the commit that introduced it, it
was actually trying to solve my problem: unnecessary joins. So I'm
guessing a proper fix would remove that.
{{{
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index f82eca3..b54a8e5 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -314,10 +314,7 @@ class Query(object):
obj.extra_tables = self.extra_tables
obj.extra_order_by = self.extra_order_by
obj.deferred_loading = copy.copy(self.deferred_loading[0]),
self.deferred_loading[1]
- if self.filter_is_sticky and self.used_aliases:
- obj.used_aliases = self.used_aliases.copy()
- else:
- obj.used_aliases = set()
+ obj.used_aliases = self.used_aliases.copy()
obj.filter_is_sticky = False
if 'alias_prefix' in self.__dict__:
obj.alias_prefix = self.alias_prefix
}}}
I tested code against 1.9 (because our app uses 1.9). It's possible this
is fixed in master, though a quick glance at master made me think
otherwise.
--
Ticket URL: <https://code.djangoproject.com/ticket/27388>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.986e89afa4a14d9fc1cf779885ecc8a3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
