From: | Sam Tregar <sam(at)tregar(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Indexes not being used. |
Date: | 2001-06-22 16:28:13 |
Message-ID: | Pine.LNX.4.30.0106221221090.20051-100000@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Haberman | 2001-06-22 16:31:32 | Re: Adding Foreign Key constraints outside of table def. |
Previous Message | Richard Huxton | 2001-06-22 15:52:00 | Re: Re[4]: Postgres is too slow? |