Re: Vacuum and indexes problem

Firstly, how many rows in the table? If it's less than a few dozen, a seq
scan is the right answer. Secondly, vacuum analyse is usually recommended
reasonably often.

HTH,

On Mon, Sep 09, 2002 at 03:04:30PM +0100, Joe Murphy wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.