id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27388	Filter chaining results in unnecessary joins (and degenerate performance)	Connor Osborn	nobody	"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."	Uncategorized	closed	Database layer (models, ORM)	1.9	Normal	invalid	queryset, join, performance		Unreviewed	0	0	0	0	0	0