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