Opened 19 years ago
Last modified 17 years ago
#1456 closed defect
date-ranges can be made more efficient — at Initial Version
Reported by: | hugo | Owned by: | Adrian Holovaty |
---|---|---|---|
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: | no | UI/UX: | no |
Description
Currently there are some date ranges that are done by using extract('someting' from timevalue). This results in the following SQL:
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)
Note:
See TracTickets
for help on using tickets.