#34845: Slow query when using exclude
-------------------------------------+-------------------------------------
Reporter: Amin Aminian | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: exclude,filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
To add to the above, assuming you are using
`annotate(payment=FilteredRelation("payments",
Q(payments__payable_type=payable_type)))` or an equivalent, the reason why
the ORM cannot simply use a `LEFT OUTER JOIN` instead of `NOT EXISTS` when
doing `exclude(payment__isnull=False)` is that it lacks introspection
capabilities into `FilteredRelation.condition` to be sure that the
condition added to the `JOIN` wouldn't interfere.
In a sense
{{{#!python
INNER JOIN "payment" ON ("order"."id" = "payment"."payable_id"
AND
("payment"."payable_type_id" = 30))
WHERE "payment"."id" IS NOT NULL
}}}
is a ''tautology''. The `WHERE "payment"."id" IS NOT NULL` part is
unnecessary given the usage of an `INNER JOIN` already ensures that
`"payment"."id" IS NOT NULL` since primary keys cannot be null.
Normally the ORM would be able to ''know'' that `"payment"."id"` cannot be
null as it's a primary key but I suspect the usage of a `FilteredRelation`
prevents the ORM from knowing.
What #34597 did is switch the query from using `"order"."id" NOT IN
(subquery using LEFT JOIN)` to `NOT EXISTS(subquery using LEFT JOIN
referring to "order"."id" )` so if you can confirm that your query is
faster with `NOT IN` than with `EXISTS` it would be great to have another
data point added to #34597.
--
Ticket URL: <https://code.djangoproject.com/ticket/34845#comment:8>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018aa8ed1cfc-383fd807-95cd-47aa-a6ea-3c64e465c95e-000000%40eu-central-1.amazonses.com.
