id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28933	Optimize the queries for ModelAdmin.date_hierarchy	hakib	nobody	"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."	Cleanup/optimization	closed	contrib.admin	2.0	Normal	fixed	date_hierarchy		Accepted	1	0	0	1	0	0