Indexes not being used.

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)

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

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

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/

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