Opened 7 years ago

Closed 7 years ago

#28933 closed Cleanup/optimization (fixed)

Optimize the queries for ModelAdmin.date_hierarchy

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 (last modified by Tim Graham)

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 wrote about this issue and 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.

Change History (4)

comment:1 by Simon Charette, 7 years ago

Has patch: set
Triage Stage: UnreviewedAccepted

comment:2 by Carlton Gibson, 7 years ago

Triage Stage: AcceptedReady for checkin

Patch looks good!

comment:3 by Tim Graham, 7 years ago

Description: modified (diff)
Patch needs improvement: set
Summary: Implement a range-based filter for Django Admin date_hierarchyOptimize the queries for ModelAdmin.date_hierarchy
Triage Stage: Ready for checkinAccepted
Type: New featureCleanup/optimization

I left some comments for improvement.

comment:4 by Tim Graham <timograham@…>, 7 years ago

Resolution: fixed
Status: newclosed

In ff55179:

Fixed #28933 -- Improved the efficiency of ModelAdmin.date_hierarchy queries.

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