I was wondering if anyone could explain how to get the Query Analyzer
to use an Index that is defined for the table instead of doing a table
scan? I have a table with some indexes on it that seem NOT to get used
when I think they should. :-) Not that I KNOW more than the Query
Analyzer - but a Table Scan seems expensive - especially if an Index
exists to help.
Here is some of what I mean
EXPLAIN ANALYZE select e.title,e.startDate,e.allDayFlag
from Events e where ownerid = 1093115
/*
Seq Scan on Events e (cost=0.00..10770.20 rows=8616 width=34)
(actual time=0.035..1489.340 rows=10005 loops=1)
Filter: (ownerid = 1093115::bigint)
Total runtime: 1500.861 ms
INDEX ownerid_IX
ON Events
USING btree
(ownerid);
*/
EXPLAIN ANALYZE select e.title,e.startDate,e.allDayFlag
from Events e where e.startDate '20050930' and e.endDate
'20051101'
/*
Seq Scan on Events e (cost=0.00..11706.64 rows=31739 width=34)
(actual time=0.148..1171.191 rows=819 loops=1)
Filter: ((startDate '2005-09-30 00:00:00'::timestamp without
time zone) AND (endDate '2005-11-01 00:00:00'::timestamp without
time zone))
Total runtime: 1173.067 ms
INDEX dates_IX
ON Events
USING btree
(startDate, endDate);
*/
These commands show the 'OUTPUT' that is in quotes in the comment - I
also included the definition of the Index that I think it should use.
Any help would be greatly appreciated.
TIA...
Jack
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings