#23242: Add an option to avoid slow date_hierarchy queries on a big tables
-------------------------------------+-------------------------------------
Reporter: Alexander Klimenko | Owner: nobody
Type: New feature | Status: new
Component: contrib.admin | Version: master
Severity: Normal | Resolution:
Keywords: date_hierarchy | Triage Stage: Accepted
datetimes dates |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
While it's still possible that getting rid of the the `DISTINCT` generated
by `dates()` might help I wonder, by looking at the provided PostgreSQL
plan in comment:3, if the actual culprit of the slow query was the
`EXTRACT('month')` all along as related in #28933.
I also wonder if `LIMIT`'ing the results to `12` in in the case of
`year&month` might help the database engine engine figure out that it can
stop the `DISTINCT` processing once it retrieved 12 results.
Another option would to stop using `dates` (or allow passing a range to
it) for `year&month` and `year&month&day` filtering and use a combination
of `generate_series` and `EXISTS` to speed up the query.
e.g. for the `year&month` case (on a `DateField` for simplicitiy but
nothing prevent us from using the same mechanism on `DateTimeField` with a
few adjustments)
{{{#!sql
SELECT EXTRACT('month' from date)
FROM generate_series('2017-01-01'::date, '2017-12-01'::date, '1 month') AS
serie(data)
WHERE EXISTS (
SELECT 1
FROM event
WHERE event.datetime >= month AND event.datetime < (month + interval
'1' month)
)
}}}
This seems to perform very well on a large ''events'' table with an
indexed `datetime` column containing 17M rows.
--
Ticket URL: <https://code.djangoproject.com/ticket/23242#comment:10>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.40c2dd5378a63952d27dfd1f495464a1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
