Re: [GENERAL] Re: can't seem to use index

On Mon, 10 Jan 2000, admin wrote:

> Following a few suggestions, I have entered 2500 records in the
> manufacturer table. Unfortunately, searching for name in the manufacturer
> table still returned a sequential scan.
>
> I then tried changing the btree index to a hash talbe and went through the
> same procedure of vacumming and restarting a psql session. Yet again, the
> index wasn't being used.
>
> Finally, I decided to create an index for id as follows:
> CREATE INDEX manu_id_idx ON "manufacturer" using btree ("id" "int2_ops");
>
> Then, when trying a similar search on the id field, it used the index.
> Unfortunately, I still can't seem to make postgresql use the index for
> searching the name field.
>
> Any other suggestions would be appreciated,
> Marc
>
> > 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" );
> >
> > Both my table and index have been created successfully, and the database
> > has been vacuumed. Then I run the following query from the psql
> > command-line:
> > explain select * from manufacturer where name='3COM';
> >
> > ... and I get a sequential scan! What gives?
> >
> > Any suggestions would be greatly appreciated,
> > Marc
> >
> >
> >
>
>
> ************
>

Marc,

I had a similar problem last year when trying to use an index on a
char(8) field. Two solutions worked for me: 1) use "bpchar_ops", and
2) leave out the operator class altogether.

I have attached the response from Gene Selkov, Jr. which suggested that
I let PostgreSQL pick the operator class.

Perhaps leaving out the "text_ops" will help.

Regards,
../fam
--
Frank A. Mandarino
fam(at)risca(dot)com

--- Forwarded message ----

Date: Thu, 7 Oct 1999 00:42:58 -0400
From: "Gene Selkov, Jr." <selkovjr(at)mcs(dot)anl(dot)gov>
To: Frank Mandarino <fam(at)risca(dot)com>, pgsql-general(at)postgreSQL(dot)org
Subject: Re: [GENERAL] btree index on a char(8) field (fwd)

> I am unable to find any reference to bpchar_ops in the the documentation
> or the General and SQL mailing list archives. Can you tell me where I
> could find out more about what "_ops" are available and what they all
> mean?

The direct answer:

$ pwd
/usr/src/postgresql-6.5/doc/src/sgml
$ find -name "*sgml" -exec grep -il "_ops" {} \;
./ref/create_index.sgml
./arch-dev.sgml -- irrelevant: co-incidental with a processing directive, \label{simple_set_ops})
./bki.sgml
./gist.sgml
./xindex.sgml

My comment:

The deficiency of the docs in regards to operator classes probably
results from the fact that no one is asking about those. The opclass
parameter in CREATE INDEX is no longer required (Herouth has been
around long enough to recall the times when it was).

As you have just witnessed, in a standard situation, you are better
off without knowing about it -- postgres will pick the right opclass
for you. That will not happen, however, when the values you want to
index are of a custom type, or when a built-in type does not have an
opclass of its own (as is the case with the point type). Also, you
need this option to override the default opclass for those types that
can work with multiple opclasses (which is what you attempted to
achieve).

Will anyone with a solid knowledge of the type system want to augment
the existing docs?

--Gene

************