Opened 11 years ago

Closed 9 years ago

#1456 closed defect (fixed)

date-ranges can be made more efficient

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

Description (last modified by hugo)

Currently there are some date ranges that are done by using extract('someting' from timevalue). This results in the following SQL (time is a timestamp with an active db_index):

select date_trunc('day', time)
from logviewer_message
where extract('month' from time) = 3
and extract('year' from time) = 2006
and channel_id = 2
group by 1 order by 1 asc;

Which under PostgreSQL has the following query plan:

 Group  (cost=2095.87..2095.88 rows=2 width=8)
   ->  Sort  (cost=2095.87..2095.87 rows=2 width=8)
         Sort Key: date_trunc('day'::text, "time")
         ->  Seq Scan on logviewer_message  (cost=0.00..2095.86 rows=2 width=8)
               Filter: ((date_part('month'::text, "time") = 3::double precision) AND (date_part('year'::text, "time") = 2006::double precision) AND (channel_id = 2))
(5 rows)

This is a rather sad result - it's far too expensive. Let's reformulate it with simple relative date expressions, so that the time index can kick in:

select date_trunc('day', time)
from logviewer_message
where time >= '1.3.2006'::date
and time < '1.4.2006'::date
and channel_id = 2
group by 1 order by 1 asc;

This has the following query plan, which is much faster:

 Group  (cost=26.96..29.09 rows=284 width=8)
   ->  Sort  (cost=26.96..27.67 rows=284 width=8)
         Sort Key: date_trunc('day'::text, "time")
         ->  Index Scan using logviewer_messages_time on logviewer_message  (cost=0.00..15.38 rows=284 width=8)
               Index Cond: (("time" >= ('2006-03-01'::date)::timestamp with time zone) AND ("time" < ('2006-04-01'::date)::timestamp with time zone))
               Filter: (channel_id = 2)
(6 rows)

This would be useful knowledge for programmers (so it might be a good idea to put it into the documentation), but it might even be a good idea to change the timefieldyearXXX queries from the current EXTRACT form to a daterange form, even if that adds more actual where conditions, because if an index is available, this would be much faster (at least the year query could be made a daterange instead of an extraction).

Change History (6)

comment:1 Changed 11 years ago by hugo

Description: modified (diff)

comment:2 Changed 11 years ago by hugo

Description: modified (diff)

comment:3 Changed 10 years ago by Chris Beaven

Triage Stage: UnreviewedDesign decision needed

comment:4 Changed 10 years ago by Malcolm Tredinnick

Owner: changed from Adrian Holovaty to Malcolm Tredinnick
Triage Stage: Design decision neededAccepted

Pending QuerySet changes will change the way we want to implement this and it might still be too fiddly to make work correctly, but it's worth trying to do. Hugo's analysis seems sound.

comment:5 Changed 9 years ago by James Bennett

Keywords: qs-rf added

comment:6 Changed 9 years ago by Malcolm Tredinnick

Keywords: qs-rf removed
Resolution: fixed
Status: newclosed

We're already doing this sort of query in trunk and that hasn't changed in queryset-refactor.

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