<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
There were fewer than a dozen rows OK, I've tested with ~1000 and indexes
are used.
<br>Thanks.
<p>Martijn van Oosterhout wrote:
<blockquote TYPE=CITE>Firstly, how many rows in the table? If it's less
than a few dozen, a seq
<br>scan is the right answer. Secondly, vacuum analyse is usually recommended
<br>reasonably often.
<p>HTH,
<p>On Mon, Sep 09, 2002 at 03:04:30PM +0100, Joe Murphy wrote:
<br>> I'm running a simple query on a simple table (see create syntax below).
<br>>
<br>> before running vacuum on the table explain tells me that the index
<br>> "mytable_id_name_idx" is being used
<br>> after running vacuum on the table explain tells me that a sequential
scan is
<br>> being used.
<br>> If I run reindex, I'm back to the index being used.
<br>>
<br>> Any ideas why this is happening?
<br>>
<br>> PG 7.2.1 on Solaris 2.6 (and HP-UX 11.00)
<br>>
<br>> Output of the commands below
<br>>
<br>>
<br>> CREATE TABLE mytable (
<br>> id
INT NOT NULL,
<br>> name
TEXT NOT NULL,
<br>> num
INT NOT NULL,
<br>> answer
INT NOT NULL,
<br>> field1
INT,
<br>> field2
INT,
<br>> field3
TEXT,
<br>> field4
TEXT
<br>> );
<br>>
<br>> CREATE INDEX mytable_id_name_idx ON mytable (id,name);
<br>> CREATE INDEX mytable_num_idx ON mytable (num);
<br>> CREATE UNIQUE INDEX mytable_id_num_idx ON mytable (id,num);
<br>>
<br>>
<br>> aw_db_joe_1=# explain select * from mytable where id = 1 and name
= 'john';
<br>> NOTICE: QUERY PLAN:
<br>>
<br>> Index Scan using mytable_id_name_idx on mytable (cost=0.00..4.83
rows=1
<br>> width=116)
<br>>
<br>> EXPLAIN
<br>>
<br>> aw_db_joe_1=# vacuum mytable;
<br>> VACUUM
<br>>
<br>> aw_db_joe_1=# explain select * from mytable where id = 1 and name
= 'john';
<br>> NOTICE: QUERY PLAN:
<br>>
<br>> Seq Scan on mytable (cost=0.00..0.00 rows=1 width=116)
<br>>
<br>> EXPLAIN
<br>>
<br>> aw_db_joe_1=# reindex table mytable;
<br>> REINDEX
<br>>
<br>> aw_db_joe_1=# explain select * from mytable where id = 1 and name
= 'john';
<br>> NOTICE: QUERY PLAN:
<br>>
<br>> Index Scan using mytable_id_name_idx on mytable (cost=0.00..4.83
rows=1
<br>> width=116)
<br>>
<br>> EXPLAIN
<br>>
<br>>
<br>> ---------------------------(end of broadcast)---------------------------
<br>> TIP 3: if posting/reading through Usenet, please send an appropriate
<br>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
<br>> message can get through to the mailing list cleanly
<p>--
<br>Martijn van Oosterhout <kleptog(at)svana(dot)org>
<a href="http://svana.org/kleptog/">http://svana.org/kleptog/</a>
<br>> There are 10 kinds of people in the world, those that can do binary
<br>> arithmetic and those that can't.</blockquote>
<pre>--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joe Murphy - AerSoft Limited
2 Northumberland Avenue, Dun Laoghaire, Co. Dublin.
phone: +353-1-2301166 direct: +353-1-2145953 fax: +353-1-2301167
<A HREF="mailto:joe(at)aersoft(dot)com">mailto:joe(at)aersoft(dot)com</A> mobile: +353-86-8526181 <A HREF="http://www.aersoft.com">http://www.aersoft.com</A>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~</pre>
</html>