Re: planner question re index vs seqscan

Jeff Hoffmann <jeff(at)propertykey(dot)com> writes:
>> QUESTION: Why doesn't the planner, just after 'vacuum analyze', use the
>> provided indices for this query? How can I tweak it to use the indices?
>>
>> sdb=# EXPLAIN SELECT ahrn.hr_type_id AS "Resource Type",
>> sdb-# SUM(ahrn.hr_count) AS "Planned Consulting Days"
>> sdb-# FROM activity a, activity_hr_need ahrn
>> sdb-# WHERE a.start_time::date >= '1-Jun-2000'::date
>> sdb-# AND a.stop_time::date <= '1-Jul-2000'::date
>> sdb-# AND ahrn.activity_id = a.id
>> sdb-# GROUP BY "Resource Type";

> dump the typecasting in the query and try again. not sure if it'll
> work, but it's worth a try. typecasting has an annoying effect of
> disabling index scans in some cases even when you'd swear logically that
> they should be used.

Oh, that's a good point --- if the start_time and stop_time columns are
not of type date then the above is guaranteed not to be indexscanable,
because what you've really written is

WHERE date(a.start_time) >= '1-Jun-2000'::date
AND date(a.stop_time) <= '1-Jul-2000'::date

It might be able to use a functional index on date(start_time) or
date(stop_time), but not a straight index on the timestamp columns.

A good rule of thumb is not to use casts unless you have no choice...

regards, tom lane