Re: planner question re index vs seqscan

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