[Django] #30897: Improved QuerySet.explain() for newer versions of MariaDB and MySQL.

Django

unread,

Oct 21, 2019, 1:04:29 PM10/21/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB and MySQL.
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope
Type: New | Status: assigned
feature |
Component: Database | Version: master
layer (models, ORM) | Keywords: explain, analyze,
Severity: Normal | mariadb, mysql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
- MySQL 8.0.16+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
plan EXPLAIN FORMAT=TREE].
- MySQL 8.0.18+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE].
- MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
/analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

--
Ticket URL: <https://code.djangoproject.com/ticket/30897>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 21, 2019, 1:10:41 PM10/21/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB and MySQL.
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/11955 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:1>

Django

unread,

Oct 22, 2019, 1:59:33 AM10/22/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB and MySQL.
-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: explain, analyze, | Triage Stage: Accepted
mariadb, mysql |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:2>

Django

unread,

Oct 22, 2019, 4:35:35 AM10/22/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

mariadb, mysql, postgresql |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* keywords: explain, analyze, mariadb, mysql => explain, analyze, mariadb,
mysql, postgresql

Old description:

> - MySQL 8.0.16+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
> plan EXPLAIN FORMAT=TREE].
> - MySQL 8.0.18+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
> EXPLAIN ANALYZE].
> - MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
> /analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

New description:

- MySQL 8.0.16+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
plan EXPLAIN FORMAT=TREE].
- MySQL 8.0.18+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE].
- MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
/analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

- PostgreSQL 12+ has support for a new [https://www.postgresql.org/docs/12
/sql-explain.html#SQL-EXPLAIN SETTINGS] option to `EXPLAIN`.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:3>

Django

unread,

Oct 23, 2019, 8:09:15 AM10/23/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* needs_better_patch: 0 => 1
* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:4>

Django

unread,

Oct 23, 2019, 8:09:24 AM10/23/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"6e9189c0807a8b4818e77c2d83618a788b2ba906" 6e9189c]:
{{{
#!CommitTicketReference repository=""
revision="6e9189c0807a8b4818e77c2d83618a788b2ba906"
Refs #30897 -- Added test for SETTINGS option to Queryset.explain() on
PostgreSQL 12+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:5>

Django

unread,

Oct 23, 2019, 10:28:54 AM10/23/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Nick Pope):

* needs_better_patch: 1 => 0
* needs_tests: 1 => 0

Comment:

Patch improved, tests added.

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:6>

Django

unread,

Oct 24, 2019, 8:32:02 AM10/24/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: explain, analyze, | Triage Stage: Ready for
mariadb, mysql, postgresql | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:7>

Django

unread,

Oct 24, 2019, 10:26:03 AM10/24/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"55df1750be3c88db89444335f77dca10681dcbe3" 55df1750]:
{{{
#!CommitTicketReference repository=""
revision="55df1750be3c88db89444335f77dca10681dcbe3"
Refs #30897 -- Added support for ANALYZE option to Queryset.explain() on
MariaDB and MySQL 8.0.18+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:9>

Django

unread,

Oct 24, 2019, 10:26:04 AM10/24/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"cabf1fe37e46b00c14ed025a176628e748837658" cabf1fe]:
{{{
#!CommitTicketReference repository=""
revision="cabf1fe37e46b00c14ed025a176628e748837658"
Refs #30897 -- Made cosmetic edits to QuerySet.explain() documentation.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:10>

Django

unread,

Oct 24, 2019, 10:26:04 AM10/24/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"742961332e1e2221e6fb9506c7254164e0c2cb5a" 74296133]:
{{{
#!CommitTicketReference repository=""
revision="742961332e1e2221e6fb9506c7254164e0c2cb5a"
Refs #30897 -- Added support for TREE format to Queryset.explain() on
MySQL 8.0.16+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:8>

Django

unread,

Oct 24, 2019, 12:02:06 PM10/24/19

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Type: New feature | Status: closed

Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: explain, analyze, | Triage Stage: Ready for
mariadb, mysql, postgresql | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* status: assigned => closed
* resolution: => fixed

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:11>

Django

unread,

Aug 20, 2020, 11:57:10 AM8/20/20

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

> - MySQL 8.0.16+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
> plan EXPLAIN FORMAT=TREE].
> - MySQL 8.0.18+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
> EXPLAIN ANALYZE].
> - MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
> /analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

> - PostgreSQL 12+ has support for a new
> [https://www.postgresql.org/docs/12/sql-explain.html#SQL-EXPLAIN
> SETTINGS] option to `EXPLAIN`.

New description:

- MySQL 8.0.16+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
plan EXPLAIN FORMAT=TREE].
- MySQL 8.0.18+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE].
- MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
/analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

- PostgreSQL 12+ has support for a new [https://www.postgresql.org/docs/12
/sql-explain.html#SQL-EXPLAIN SETTINGS] option to `EXPLAIN`.

Updated 2020-08-20:

- MySQL 8.0.21+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE FORMAT=TREE].
- PostgreSQL 13+ has support for a new [https://www.postgresql.org/docs/13
/sql-explain.html#SQL-EXPLAIN WAL] option to `EXPLAIN`.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:12>

Django

unread,

Aug 20, 2020, 12:00:15 PM8/20/20

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Nick Pope):

Another [https://github.com/django/django/pull/13333 PR] for some
additional minor updates.

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:13>

Django

unread,

Aug 24, 2020, 5:15:23 AM8/24/20

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Nick Pope:

Old description:

> - MySQL 8.0.16+ has support for

> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
> plan EXPLAIN FORMAT=TREE].
> - MySQL 8.0.18+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
> EXPLAIN ANALYZE].
> - MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
> /analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

> - PostgreSQL 12+ has support for a new
> [https://www.postgresql.org/docs/12/sql-explain.html#SQL-EXPLAIN
> SETTINGS] option to `EXPLAIN`.
>

> Updated 2020-08-20:
>
> - MySQL 8.0.21+ has support for
> [https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze

> EXPLAIN ANALYZE FORMAT=TREE].
> - PostgreSQL 13+ has support for a new

> [https://www.postgresql.org/docs/13/sql-explain.html#SQL-EXPLAIN WAL]
> option to `EXPLAIN`.

New description:

- MySQL 8.0.16+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-execution-
plan EXPLAIN FORMAT=TREE].
- MySQL 8.0.18+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE].
- MariaDB 10.1.0+ has support for [https://mariadb.com/kb/en/library
/analyze-statement/ ANALYZE]. (Was `EXPLAIN ANALYZE` before 10.1.0.)

- PostgreSQL 12+ has support for a new [https://www.postgresql.org/docs/12
/sql-explain.html#SQL-EXPLAIN SETTINGS] option to `EXPLAIN`.

**Updated 2020-08-24:**

- MySQL 8.0.21+ has support for
[https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze
EXPLAIN ANALYZE FORMAT=TREE].
- **Note:** We are not removing restriction on use of `ANALYZE` with
`FORMAT` until more than one format is supported with this combination of
options. (See
[https://github.com/django/django/pull/13333#pullrequestreview-473197627
review].)

- PostgreSQL 13+ has support for a new [https://www.postgresql.org/docs/13

/sql-explain.html#SQL-EXPLAIN WAL] option to `EXPLAIN`. ''(Note: Requires
`ANALYZE` option.)''

--

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:14>

Django

unread,

Aug 24, 2020, 5:47:56 AM8/24/20

to django-...@googlegroups.com

#30897: Improved QuerySet.explain() for newer versions of MariaDB, MySQL and
PostgreSQL.

-------------------------------------+-------------------------------------
Reporter: Nick Pope | Owner: Nick Pope

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"b3124215117541a86b0740314ef991e1d521da69" b3124215]:
{{{
#!CommitTicketReference repository=""
revision="b3124215117541a86b0740314ef991e1d521da69"
Refs #30897 -- Added test for WAL option to Queryset.explain() on
PostgreSQL 13+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/30897#comment:15>

Reply all

Reply to author

Forward