Opened 6 years ago

Last modified 6 years ago

#28933 closed Cleanup/optimization

Implement a range-based filter for Django Admin date_hierarchy — at Initial Version

Reported by: hakib Owned by: nobody
Component: contrib.admin Version: 2.0
Severity: Normal Keywords: date_hierarchy
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

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.

Change History (0)

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