Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#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

  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.

Change History (4)

comment:1 by Simon Charette, 4 years ago

Has patch: set
Owner: changed from nobody to Simon Charette
Status: newassigned

comment:2 by Mariusz Felisiak, 4 years ago

Triage Stage: UnreviewedAccepted

comment:3 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In f6405c0:

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.

comment:4 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

In 2986ec03:

[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

Note: See TracTickets for help on using tickets.
Back to Top