Indexes in PostgreSQL

From: jackfitz(at)yahoo(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Indexes in PostgreSQL
Date: 2005-04-18 19:25:33
Message-ID: 1113852333.765825.12550@l41g2000cwc.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 = 100000000093115
/*
"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 = 100000000093115::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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2005-04-18 19:37:04 Re: Pgsql config file
Previous Message Harald Fuchs 2005-04-18 19:24:21 Re: Help with a plperl function