#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 django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.175c897621ebc155f89ac316c04923c9%40djangoproject.com.
