[Django] #31965: MySQL fast-delete optimizations introduced in Django 3.1 don't properly deal with aggregation and cause performance regression.
Django
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.
Django
deal with aggregation and cause performance regression.
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/31965#comment:1>
Django
deal with aggregation and cause performance regression.
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
[https://github.com/django/django/pull/13368 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/31965#comment:2>
Django
deal with aggregation and cause performance regression.
-------------------------------------+-------------------------------------
| Charette
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"f6405c0b8ef7aff513b105c1da68407a881a3671" f6405c0]:
{{{
#!CommitTicketReference repository=""
revision="f6405c0b8ef7aff513b105c1da68407a881a3671"
Fixed #31965 -- Adjusted multi-table fast-deletion on MySQL/MariaDB.
The optimization introduced in 7acef095d73 did not properly handle
deletion involving filters against aggregate annotations.
It initially was surfaced by a MariaDB test failure but misattributed
to an undocumented change in behavior that resulted in the systemic
generation of poorly performing database queries in 5b83bae031.
Thanks Anton Plotkin for the report.
Refs #23576.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31965#comment:3>
Django
deal with aggregation and cause performance regression.
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"2986ec031d311891af8c59c845ce3b5e46d17a76" 2986ec03]:
{{{
#!CommitTicketReference repository=""
revision="2986ec031d311891af8c59c845ce3b5e46d17a76"
[3.1.x] Fixed #31965 -- Adjusted multi-table fast-deletion on
MySQL/MariaDB.
The optimization introduced in 7acef095d73 did not properly handle
deletion involving filters against aggregate annotations.
It initially was surfaced by a MariaDB test failure but misattributed
to an undocumented change in behavior that resulted in the systemic
generation of poorly performing database queries in 5b83bae031.
Thanks Anton Plotkin for the report.
Refs #23576.
Backport of f6405c0b8ef7aff513b105c1da68407a881a3671 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31965#comment:4>