I have changed the name field to a char(32) NOT NULL, and I still get a
sequential scan. I have added the 2500 records and I did "vacuumdb
database" from the command-line. Unfortunately, "vacuum analyze" from the
psql prompt returns a pqReadData() error, loses the connection to the
backend and returns me to the shell. After reconnecting to the database,
explain still returns a sequential scan when trying something like:
explain select * from manufacturer where name='3COM';
Thanks anyways for the tip, I've been using varchar() all over the place,
I think I'll change a few to char(). What are the advantages of using
char() instead of varchar(). For a sequential scan, explain returned a
cost of 105.44 for a char() field as opposed to 95.44 for a varchar().
Thanks again,
Marc
> --- admin <admin@wtbwts.com> wrote:
> > > I'm trying to use an index on a varchar(32) field, but explain
> > keeps
> > > retuning a sequential scan. This is my table and index:
> > >
> > > CREATE TABLE manufacturer (
> > > id int2,
> > > name varchar(32)
> > > );
> > >
> > > CREATE INDEX manu_name_idx ON "manufacturer" using btree ( "name"
> > > "text_ops" );
>
> Do you really need a varchar ? I've got similar queries on a char
> column which use the index. Maybe it's a problem about text_ops,
> it may not be compatible with varchar.
>
> Alain
> __________________________________________________
> Do You Yahoo!?
> Talk to your friends online with Yahoo! Messenger.
> http://im.yahoo.com
>
