Ed Loehr <eloehr@austin.rr.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";
At least part of the problem is that you have two separate one-sided
inequalities, neither one of which is very selective by itself ---
and of course the planner has no idea that there might be any semantic
connection between "start_time" and "stop_time". You could help it out
by providing something it can recognize as a range restriction on one
index or the other. For example:
WHERE a.start_time::date >= '1-Jun-2000'::date AND a.start_time::date <= '1-Jul-2000'::date AND a.stop_time::date <=
'1-Jul-2000'::date AND ahrn.activity_id = a.id
regards, tom lane