From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jeff Hoffmann <jeff(at)propertykey(dot)com> |
Cc: | Ed Loehr <eloehr(at)austin(dot)rr(dot)com>, pghackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: planner question re index vs seqscan |
Date: | 2000-06-16 18:42:03 |
Message-ID: | 8167.961180923@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-06-16 19:00:10 | Re: Big 7.1 open items |
Previous Message | Tom Lane | 2000-06-16 18:30:55 | Re: planner question re index vs seqscan |