[Django] #28933: Implement a range-based filter for Django Admin date_hierarchy
Django
-----------------------------------------+--------------------------------
Reporter: hakib | Owner: nobody
Type: New feature | Status: new
Component: contrib.admin | Version: 2.0
Severity: Normal | Keywords: date_hierarchy
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+--------------------------------
The predicate generated by date_hierarchy makes it very difficult for
databases to optimize the query.
The following date hierarchy:
{{{
/admin/app/model?created__year=2017&created__month=12&created__day=16
}}}
Will generate the following where clause (PostgreSql):
{{{
WHERE created between '2017-01-01' and '2017-31-12' and EXTRACT('month',
created) = 12 and EXTRACT('day', created) = 16
}}}
The query above will not be able to utilize range based indexes on the
date hierarchy column - on big tables this has a significant performance
impact.
The current implementation of date hierarchy is relying on the "default"
filtering mechinizem used by Django Admin. **I propose implementing custom
filtering for Django Admin that will better utilize it's hierarchical
nature and make it more database "friendly".**
Instead of the query above the date hierarchy would generate the following
predicates for different levels of the heirarchy:
{{{
/admin/app/model?created__year=2017&created__month=12&created__day=16
WHERE created >= '2017-12-16' and created < '2017-12-17'
}}}
{{{
/admin/app/model?created__year=2017&created__month=12
WHERE created >= '2017-12-01' and created < '2018-01-01'
}}}
{{{
/admin/app/model?created__year=2017
WHERE created >= '2017-01-01' and created < '2018-01-01'
}}}
I already [wrote about this issue](https://codeburst.io/django-admin-
range-based-date-hierarchy-37955b12ea4e) and [published a
package](https://github.com/hakib/django-admin-lightweight-date-
hierarchy/blob/master/django_admin_lightweight_date_hierarchy/admin.py)
that implement the above as a custom SimpleListFilter.
I already have a PR ready - I'm following proper protocol here so please
let me know if this is acceptable.
--
Ticket URL: <https://code.djangoproject.com/ticket/28933>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: hakib | Owner: nobody
Type: New feature | Status: new
Component: contrib.admin | Version: 2.0
Keywords: date_hierarchy | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* has_patch: 0 => 1
* stage: Unreviewed => Accepted
Comment:
[https://github.com/django/django/pull/9469 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28933#comment:1>
Django
Reporter: hakib | Owner: nobody
Type: New feature | Status: new
Component: contrib.admin | Version: 2.0
Keywords: date_hierarchy | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Carlton Gibson):
* stage: Accepted => Ready for checkin
Comment:
Patch looks good!
--
Ticket URL: <https://code.djangoproject.com/ticket/28933#comment:2>
Django
--------------------------------------+------------------------------------
Reporter: hakib | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: contrib.admin | Version: 2.0
Severity: Normal | Resolution:
Keywords: date_hierarchy | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
* stage: Ready for checkin => Accepted
* type: New feature => Cleanup/optimization
Old description:
New description:
The predicate generated by `ModelAdmin.date_hierarchy` makes it very
difficult for databases to optimize the query.
The following date hierarchy:
`/admin/app/model?created__year=2017&created__month=12&created__day=16`
generates the following WHERE clause (PostgreSQL):
`WHERE created between '2017-01-01' and '2017-31-12' and EXTRACT('month',
created) = 12 and EXTRACT('day', created) = 16`
The query above will not be able to utilize range based indexes on the
date hierarchy column - on big tables this has a significant performance
impact.
The current implementation of date hierarchy is relying on the "default"
filtering mechanism used by the admin. I propose implementing custom
filtering for that will better utilize it's hierarchical nature and make
it more database friendly.
Instead of the query above the date hierarchy would generate the following
predicates for different levels of the hierarchy:
{{{
/admin/app/model?created__year=2017&created__month=12&created__day=16
WHERE created >= '2017-12-16' and created < '2017-12-17'
}}}
{{{
/admin/app/model?created__year=2017&created__month=12
WHERE created >= '2017-12-01' and created < '2018-01-01'
}}}
{{{
/admin/app/model?created__year=2017
WHERE created >= '2017-01-01' and created < '2018-01-01'
}}}
I already [https://codeburst.io/django-admin-range-based-date-hierarchy-
37955b12ea4e wrote about this issue] and [https://github.com/hakib/django-
admin-lightweight-date-
hierarchy/blob/master/django_admin_lightweight_date_hierarchy/admin.py
published a package] that implements the above as a custom
`SimpleListFilter`.
I already have a PR ready - I'm following proper protocol here so please
let me know if this is acceptable.
--
Comment:
I left some comments for improvement.
--
Ticket URL: <https://code.djangoproject.com/ticket/28933#comment:3>
Django
--------------------------------------+------------------------------------
Type: Cleanup/optimization | Status: closed
Component: contrib.admin | Version: 2.0
Severity: Normal | Resolution: fixed
Keywords: date_hierarchy | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: new => closed
* resolution: => fixed
Comment:
In [changeset:"ff5517988adec04d364521fdaf4a36a3f88942ef" ff55179]:
{{{
#!CommitTicketReference repository=""
revision="ff5517988adec04d364521fdaf4a36a3f88942ef"
Fixed #28933 -- Improved the efficiency of ModelAdmin.date_hierarchy
queries.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28933#comment:4>