date-ranges can be made more efficient

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

[Malcolm Tredinnick]

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.

[Malcolm Tredinnick]

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