Re: Indexes not being used.

Humm,
try issuing a VACUUM ANALYSE after the creation of the index.

Think it will help, but i'm not very good at this. YET!

Daniel Åkerud

----- Original Message -----
From: "Sam Tregar" <sam@tregar.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, June 22, 2001 6:28 PM
Subject: [GENERAL] Indexes not being used.


> Hello all.  I'm having trouble convincing Postgres to use indexes created
> after loading data into my database.  Here's an example:
>
>   test=# create table test ( id integer, value text );
>   CREATE
>   test=# insert into test values ( 1, 'foo');
>   INSERT 14725127 1
>   test=# insert into test values ( 2, 'bar');
>   INSERT 14725128 1
>   test=# insert into test values ( 3, 'baz');
>   INSERT 14725129 1
>   test=# create index test_index on test (id);
>   CREATE
>   test=# explain select * from test where id = 1;
>   NOTICE:  QUERY PLAN:
>
>   Seq Scan on test  (cost=0.00..1.04 rows=1 width=16)
>
>   EXPLAIN
>
> Now, this isn't a problem with just 3 rows, but in my real tables with
> over a million records it's rendering my tables unusable.  I can get
> working indexes if I create them before loading data:
>
>   test=#  create table test2 ( id integer, value text );
>   CREATE
>   test=# create index test2_index on test2 (id);
>   CREATE
>   test=# insert into test2 values ( 1, 'foo');
>   INSERT 14725165 1
>   test=# insert into test2 values ( 2, 'foo');
>   INSERT 14725166 1
>   test=# insert into test2 values ( 3, 'foo');
>   INSERT 14725167 1
>   test=# explain select * from test2 where id = 1;
>   NOTICE:  QUERY PLAN:
>
>   Index Scan using test2_index on test2  (cost=0.00..8.14 rows=10
width=16)
>
>   EXPLAIN
>
> I'll use this as a work-around for now but I'd really like to be able to
> create new indexes after import.  I'm doing some experimental data-mining
> and it's not always possible to know upfront what indexes will be
> necessary.
>
> I'm using Postgres 7.1.2 on Linux 2.4.5, in case it matters.
>
> -sam
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>