Opened 13 months ago

Closed 13 months ago

Last modified 13 months 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 Changed 13 months ago by Simon Charette

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

comment:2 Changed 13 months ago by Mariusz Felisiak

Triage Stage: UnreviewedAccepted

comment:3 Changed 13 months ago by Mariusz Felisiak <felisiak.mariusz@…>

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 Changed 13 months ago by Mariusz Felisiak <felisiak.mariusz@…>

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