Re: Indexes not being used.
От | Jason Earl |
---|---|
Тема | Re: Indexes not being used. |
Дата | |
Msg-id | 20010622173633.50719.qmail@web10006.mail.yahoo.com обсуждение исходный текст |
Ответ на | Indexes not being used. (Sam Tregar <sam@tregar.com>) |
Список | 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/
В списке pgsql-general по дате отправления: