There were fewer than a dozen rows OK, I've tested with ~1000 and indexes
are used.
Thanks.
Martijn van Oosterhout wrote:
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@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog@svana.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.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
JoeMurphy - AerSoft Limited
2 Northumberland Avenue, Dun Laoghaire, Co.
Dublin.
phone: +353-1-2301166 direct: +353-1-2145953 fax:
+353-1-2301167
mailto:joe@aersoft.com mobile: +353-86-8526181 http://www.aersoft.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
