id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
1456	date-ranges can be made more efficient	hugo	nobody	"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 timefield__year__XXX 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).
"	defect	closed	contrib.admin		normal	fixed			Accepted	0	0	0	0	0	0