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
34845	Slow query when using exclude	Amin Aminian	nobody	"Hi!

I have a code snippet like this:


{{{
qs = Order.objects.exclude(
    payment__isnull=True
)
}}}

Which creates the following SQL:

{{{
SELECT ""order"".""id"",
       ...
FROM ""order""
WHERE NOT (EXISTS
             (SELECT 1 AS ""a""
              FROM ""order"" U0
              LEFT OUTER JOIN ""payment"" U1 ON (U0.""id"" = U1.""payable_id""
                                                                  AND (U1.""payable_type_id"" = 30))
              WHERE (U1.""id"" IS NULL
                     AND U0.""id"" = (""order"".""id""))
              LIMIT 1))
LIMIT 10
}}}

(This is part of original query)

In my large DB, this section increases response time about 10s.

By changing `exclude` to `filter`, we will not have subquery anymore:

{{{
qs = Order.objects.filter(
    payment__isnull=False
)
}}}

And the SQL:

{{{
SELECT ""order"".""id"",
    ...
FROM ""order""
INNER JOIN ""payment"" ON (""order"".""id"" = ""payment"".""payable_id""
                                            AND (""payment"".""payable_type_id"" = 30))
WHERE ""payment"".""id"" IS NOT NULL
LIMIT 10
}}}

And with this, I don't face high response time anymore and everything is fine.

Why Django uses subquery when using `exclude` ?

I just wanted to make sure it was intended.
"	Cleanup/optimization	closed	Database layer (models, ORM)	4.2	Normal	duplicate	exclude,filter		Unreviewed	0	0	0	0	0	0