Re: planner question re index vs seqscan

Ed Loehr wrote:

> 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";
> NOTICE: QUERY PLAN:

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. if that doesn't help, it's possible that it just
shouldn't be using the indexes based on cost estimates. try shutting
off the sequential scan with "set enable_seqscan=off" before the query
to check if that's the case.

--

Jeff Hoffmann
PropertyKey.com