Opened 9 years ago

Closed 7 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 9 years ago by hugo

  • Description modified (diff)

comment:2 Changed 9 years ago by hugo

  • Description modified (diff)

comment:3 Changed 8 years ago by SmileyChris

  • Triage Stage changed from Unreviewed to Design decision needed

comment:4 Changed 8 years ago by mtredinnick

  • Owner changed from adrian to mtredinnick
  • Triage Stage changed from Design decision needed to Accepted

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 7 years ago by ubernostrum

  • Keywords qs-rf added

comment:6 Changed 7 years ago by mtredinnick

  • Keywords qs-rf removed
  • Resolution set to fixed
  • Status changed from new to closed

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