#31965: MySQL fast-delete optimizations introduced in Django 3.1 don't properly
deal with aggregation and cause performance regression.
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: nobody
Charette |
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
[https://github.com/django/django/pull/11996#issuecomment-682349899 As
reported on Github] the fast-delete optimization on MySQL to use the
proprietary `DELETE FROM` syntax introduced in #23576 and the follow up
commit to disable it on MariaDB 10.3.1+
5b83bae031a9771d86933bcc808d4bf2e21216a2 are causing performance
regression due it's poor query planing of `DELETE` involving subqueries.
[https://github.com/django/django/pull/11931#issuecomment-547862119 While
investigating the initially reported failure on MariaDB] I noticed that
the MySQL's `SQLDeleteCompiler` implementation was also not properly
dealing while filters against aggregation. It's normally not an issue
because aggregation is done against against many-to-many relationships
that involve reverse `ForeignKey` that usually have an `on_delete` that
require in-memory fetching from `deletion.Collector` (e.g. `CASCADE`,
`SET_NULL`) and thus disable fast-delete. However when only fast-delete
`on_delete` handlers are involved (e.g. `DO_NOTHING` or future database
level `on_delete` support #21961) this can be an issue that results in a
`Invalid use of group function` crash due to an attempt at using aggregate
functions in the `WHERE` clause.
In order to address both issues I suggest we
1. Always use the subquery approach when aggregation is involved
2. Materialize the subquery at the database level when the backend doesn't
have the `update_can_self_select` feature
3. Favor the proprietary `DELETE FROM` syntax otherwise to work around
MySQL poor handling of subqueries and avoid the generation of slow
queries.
--
Ticket URL: <https://code.djangoproject.com/ticket/31965>
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/052.175c897621ebc155f89ac316c04923c9%40djangoproject.com.
