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
