#34597: Django ORM query SQL generation changed (and is apparently a lot slower)
-------------------------------------+-------------------------------------
Reporter: Lorand Varga | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* status: new => closed
* severity: Release blocker => Normal
* cc: Simon Charette (added)
* type: Bug => Cleanup/optimization
* version: 4.2 => 3.2
* resolution: => needsinfo
Comment:
More likely to be related to #32143
(8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1) which switched to using
`EXISTS` over `NOT IN` and was already part of Django 3.2.
AFAIK this is the only report in three years about this change making
things slower and the fact it jumped from a couple of seconds to hours
makes me think there might be something else at play here (lack of
analyze?). FWIW this change was advised [https://www.percona.com/blog/sql-
optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ by this
article] and examples in the wild of `NOT IN` performing poorly.
Without more details such as the Postgres execution plans (use `EXPLAIN`
on the query) it's very hard to provide any guidance here so I'll close as
''needsinfo'' for now as I cannot reproduce any form of slowdown with the
provided model. The `AND "blog"."id" = "blog"."id"` part seems definitely
fishy though so it'd be great to see if you get fast results without it as
that appear to be a bug.
In the mean time you can use
`~Q(id__in=Blog.objects.filter(translation=None, id=OuterRef("id")))`
instead of `~Q(translation=None)`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34597#comment:1>
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/0107018853e4646b-54496b3e-4852-4d0e-8bc8-92ee048ac4e2-000000%40eu-central-1.amazonses.com.
