#28933: Optimize the queries for ModelAdmin.date_hierarchy
--------------------------------------+------------------------------------
     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:

> 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.

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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/063.7a03d7bf3f3327dbf4f76e9bb7f56b61%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to