Re: [GENERAL] Indexes in PostgreSQL

[jackfitz]

Yes. Thanks. I ran VACUUM ANALYZE and got the same results...

-Jack


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Indexes in PostgreSQL

[jackfitz]

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.allDa­yFlag
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.allDa­yFlag
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

Re: [GENERAL] Indexes in PostgreSQL

[Martijn van Oosterhout]

On Mon, Apr 18, 2005 at 12:25:33PM -0700, [EMAIL PROTECTED] wrote:
 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

snip

Your row estimates seem way off. Have you run ANALYZE recently?

 EXPLAIN ANALYZE select e.title,e.startDate,e.allDa­yFlag
 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);
 */

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
 tool for doing 5% of the work and then sitting around waiting for someone
 else to do the other 95% so you can sue them.


pgpwCeZEiwJEg.pgp
Description: PGP signature