#28574 closed New feature (fixed)
Add a QuerySet.explain() method
Reported by: | Tom Forbes | Owned by: | Tom Forbes |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Tom Forbes | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
When diagnosing slow queries it would be handy to have an explain
method on the queryset. Currently if you have a queryset that is performing slowly and you want to use your databases EXPLAIN query to see why, you have to print the querysets query attribute, copy and paste the often verbose string into a database shell, add the appropriate explain syntax, fix any parameters that may be missing and execute it. This can be quite annoying.
Every database that is supported in core supports this, usually by adding EXPLAIN
before the query. Simply returning whatever the database gives you (which is vastly different per vendor and even version) could be a good improvement.
Change History (15)
comment:1 by , 7 years ago
comment:2 by , 7 years ago
Summary: | Add a Queryset.explain() method → Add a QuerySet.explain() method |
---|---|
Triage Stage: | Unreviewed → Accepted |
It looks reasonable at first glance.
comment:3 by , 7 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Status: | new → assigned |
comment:4 by , 7 years ago
PR: https://github.com/django/django/pull/9053
On any select queryset you can do print(qs.explain())
and it will print the databases EXPLAIN output. There is a verbose
and a format
flag for backends that support it, e.g on Mysql/Postgres print(qs.explain(verbose=True, format='json'))
comment:5 by , 7 years ago
Has patch: | set |
---|
I've updated the patch to include documentation. This works well on all databases except MySQL, which returns a table structure rather than a human-readable strings like PostgreSQL. Without the pseudo-table headers it's not very useful, but it could be a lot more effort to add support for this (we would have to deal with lining up the column headers with the values, add support for returning the column names from the execute_sql
function).
comment:6 by , 7 years ago
Patch needs improvement: | set |
---|
comment:7 by , 7 years ago
Patch needs improvement: | unset |
---|
comment:9 by , 7 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
The new tests fail on Oracle. Needs resolving. (Hopefully good to go then.)
comment:10 by , 7 years ago
Patch needs improvement: | unset |
---|
comment:11 by , 7 years ago
I don't see how we can add support for Oracle, the way explaining query plans works is significantly different from any other implementation, including the fact that you cannot use SQL parameterized queries.
I've modified the patch to simply disable this feature on Oracle for now.
comment:12 by , 7 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
This looks ready to go. Good work Tom!
+1 for this .