Vacuum and indexes problem

From: Joe Murphy <joe(dot)murphy(at)aersoft(dot)ie>
To: pgsql-general(at)postgresql(dot)org
Subject: Vacuum and indexes problem
Date: 2002-09-09 14:04:30
Message-ID: 3D7CAA6E.1426BFE3@aersoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm running a simple query on a simple table (see create syntax below).

before running vacuum on the table explain tells me that the index
"mytable_id_name_idx" is being used
after running vacuum on the table explain tells me that a sequential scan is
being used.
If I run reindex, I'm back to the index being used.

Any ideas why this is happening?

PG 7.2.1 on Solaris 2.6 (and HP-UX 11.00)

Output of the commands below

CREATE TABLE mytable (
id INT NOT NULL,
name TEXT NOT NULL,
num INT NOT NULL,
answer INT NOT NULL,
field1 INT,
field2 INT,
field3 TEXT,
field4 TEXT
);

CREATE INDEX mytable_id_name_idx ON mytable (id,name);
CREATE INDEX mytable_num_idx ON mytable (num);
CREATE UNIQUE INDEX mytable_id_num_idx ON mytable (id,num);

aw_db_joe_1=# explain select * from mytable where id = 1 and name = 'john';
NOTICE: QUERY PLAN:

Index Scan using mytable_id_name_idx on mytable (cost=0.00..4.83 rows=1
width=116)

EXPLAIN

aw_db_joe_1=# vacuum mytable;
VACUUM

aw_db_joe_1=# explain select * from mytable where id = 1 and name = 'john';
NOTICE: QUERY PLAN:

Seq Scan on mytable (cost=0.00..0.00 rows=1 width=116)

EXPLAIN

aw_db_joe_1=# reindex table mytable;
REINDEX

aw_db_joe_1=# explain select * from mytable where id = 1 and name = 'john';
NOTICE: QUERY PLAN:

Index Scan using mytable_id_name_idx on mytable (cost=0.00..4.83 rows=1
width=116)

EXPLAIN

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Ploski 2002-09-09 14:22:36 Re: Creating tons of tables to support a query
Previous Message Tom Lane 2002-09-09 13:50:45 Re: Using the right tool