Postgres Pro
Downloads
Home   >   mailing lists

Thread: Indexes in PostgreSQL

Indexes in PostgreSQL

From
jackfitz@yahoo.com
Date:
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

Re: Indexes in PostgreSQL

From
Martijn van Oosterhout
Date:
On Mon, Apr 18, 2005 at 12:25:33PM -0700, jackfitz@yahoo.com 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.
Attachment

Re: Indexes in PostgreSQL

From
"jackfitz"
Date:
Yes. Thanks. I ran VACUUM ANALYZE and got the same results...

-Jack