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
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 |