#31965 closed Bug (fixed)
MySQL fast-delete optimizations introduced in Django 3.1 don't properly deal with aggregation and cause performance regression.
Reported by: | Simon Charette | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.1 |
Severity: | Release blocker | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
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
- Always use the subquery approach when aggregation is involved
- Materialize the subquery at the database level when the backend doesn't have the
update_can_self_select
feature - Favor the proprietary
DELETE FROM
syntax otherwise to work around MySQL poor handling of subqueries and avoid the generation of slow queries.
Change History (4)
comment:1 by , 4 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:2 by , 4 years ago
Triage Stage: | Unreviewed → Accepted |
---|
PR