Re: Indexes not being used. - Mailing list pgsql-general

Have you tried running 'vacuum analyze' on the table
in question after creating the index.  PostgreSQL
doesn't update the statistics for the index until
after the table has been analyzed.

If you are bulk dumping in large amounts of data
standard procedure is to drop the indexes, import the
data (in a transaction for speed), recreate the
indexes, and then vacuum analyze the table.

You will also need to vacuum tables that are highly
dynamic (lots of inserts and updates) fairly regularly
to remove expired tuples.

Jason

--- Sam Tregar <sam@tregar.com> wrote:
> 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


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/