Thread: 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
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 >
You need to VACUUM ANALYZE your tables after you've finished with the
bulk load step.
And no, behavior on toy tables is not a guide to what the planner will
choose to do with large tables.
regards, tom lane
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/
On Fri, 22 Jun 2001, Tom Lane wrote: > You need to VACUUM ANALYZE your tables after you've finished with the > bulk load step. Thanks. This information seems to be missing from the manual! It should probably go in "11.3.3. Remove Indices". Currently the advice given produces tables that don't use their indexes. Better yet, Postgres could be modified to automatically VACUUM ANALYZE after a CREATE INDEX. Isn't it a pretty good bet that the user will want to use the index after they've created it? > And no, behavior on toy tables is not a guide to what the planner will > choose to do with large tables. Another pearl of wisdom that should probably go in the manual. -sam
Sam Tregar <sam@tregar.com> writes:
> Better yet, Postgres could be modified to automatically VACUUM ANALYZE
> after a CREATE INDEX.
Doesn't seem like a good idea --- what if you need to create several
indexes?
I agree that the "Populating a Database" tips probably should mention
vacuuming.
regards, tom lane
On Fri, 22 Jun 2001, Tom Lane wrote: > Sam Tregar <sam@tregar.com> writes: > > Better yet, Postgres could be modified to automatically VACUUM ANALYZE > > after a CREATE INDEX. > > Doesn't seem like a good idea --- what if you need to create several > indexes? A NOVACUUM option? It seems like the general case - creating an index and then wanting to use it - should be automatic. I've never used an RDBMs that required me to do a special dance to an index I just created! Grumble, grumble. -sam