Re: planner question re index vs seqscan

Tom Lane wrote:
>
> 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?
>
> > 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.

I dropped the typecasting, but that had no visible effect. Adding the
additional predicate to the where clause as Tom suggested had the desired
effect of replacing one seqscan with an index scan. But I'm still
wondering why it is still doing a seq scan on the "ahrn.activity_id =
a.id" part when both of those integer columns are indexed??

EXPLAIN SELECT ahrn.hr_type_id AS "Resource Type",
SUM(ahrn.hr_count) AS "Planned Consulting Days"
FROM activity a, activity_hr_need ahrn
WHERE a.start_time >= '1-Jun-2000'
AND a.stop_time <= '1-Jul-2000'
AND a.start_time <= '1-Jul-2000'
AND ahrn.activity_id = a.id
GROUP BY "Resource Type";
QUERY PLAN:

Aggregate (cost=137.12..137.16 rows=1 width=16)
-> Group (cost=137.12..137.14 rows=7 width=16)
-> Sort (cost=137.12..137.12 rows=7 width=16)
-> Hash Join (cost=47.86..137.04 rows=7 width=16)
-> Seq Scan on activity_hr_need ahrn
(cost=0.00..53.58 rows=2358 width=12)
-> Hash (cost=47.82..47.82 rows=16 width=4)
-> Index Scan using activity_start_time on
activity a (cost=0.00..47.82 rows=16 width=4)

Regards,
Ed Loehr