Indexes not being used.
От | Sam Tregar |
---|---|
Тема | Indexes not being used. |
Дата | |
Msg-id | Pine.LNX.4.30.0106221221090.20051-100000@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: Indexes not being used.
Re: Indexes not being used. |
Список | 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
В списке pgsql-general по дате отправления: