Thread: can't seem to use index
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
I believe that vacuuming the database will help the system determine whether a sequential scan or an index scan is more efficient. It's much faster for the db to scan sequentially when only a few records exist in the db. Copy in a chunk of data, vacuum and then rerun your explain. Sometimes I've had to \q and reconnect to the db to get it to use the index on an explain, but that could just be coincidence. -------------------------------------------------------------------------- Aaron Holtz ComNet Inc. UNIX Systems Administration/Network Operations "It's not broken, it just lacks duct tape." -------------------------------------------------------------------------- On Jan 9, admin molded the electrons to say.... >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 > > > >************ >
On Sun, Jan 09, 2000 at 10:37:43PM +0000, admin wrote:
...
> Both my table and index have been created successfully, and the database
> has been vacuumed. Then I run the following query from the psql
^^^^^^^^
Maybe it needs to be "vacuum analyze"..
Cheers,
Patrick
On Sun, Jan 09, 2000 at 10:37:43PM +0000, admin wrote:
...
> Both my table and index have been created successfully, and the database
> has been vacuumed. Then I run the following query from the psql
^^^^^^^^
Maybe it needs to be "vacuum analyze"..
Cheers,
Patrick
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
>
>
>
> 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. But did you try vacuum analyze or just vacuum?
Yes, I did try vacuum analyze, but my search query still uses 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. > > But did you try vacuum analyze or just vacuum? >
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@risca.com
--- Forwarded message ----
Date: Thu, 7 Oct 1999 00:42:58 -0400
From: "Gene Selkov, Jr." <selkovjr@mcs.anl.gov>
To: Frank Mandarino <fam@risca.com>, pgsql-general@postgreSQL.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
************
> > 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: > > 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. > Thanks, it worked. After reading your previous message, I guess I will omit the operator class altogether considering postgresql will most likely make a better decision than me anyways. Furthermore, since you seem to be quite familiar with this project, are you aware of any documentation for fine tuning postgresql? For instance, how can I make an educated decition whether to use char(32) or varchar(32)? From the documentation, under /docs/user/datatype960.htm, all four character types are shown in a table but there's no explanation as to which would be preferable in certain situations. If using explain is the way to go, it returned a higher cost for a sequential scan on a char() field compared to a varchar() field. Unfortunately, I suspect using char() does have some advantages I don't know about. Maybe these are newbie preoccupations, but I suspect there are a few of us out there. For the moment, the best tips I've received came from this mailing list and maybe this is the way to go. Thanks again for the help, Marc
On Tue, 11 Jan 2000, admin 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: > > > > 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. > > > Thanks, it worked. After reading your previous message, I guess I will > omit the operator class altogether considering postgresql will most likely > make a better decision than me anyways. > > Furthermore, since you seem to be quite familiar with this project, are > you aware of any documentation for fine tuning postgresql? For instance, > how can I make an educated decition whether to use char(32) or > varchar(32)? From the documentation, under /docs/user/datatype960.htm, all > four character types are shown in a table but there's no explanation as to > which would be preferable in certain situations. If using explain is the > way to go, it returned a higher cost for a sequential scan on a char() > field compared to a varchar() field. Unfortunately, I suspect using char() > does have some advantages I don't know about. > > Maybe these are newbie preoccupations, but I suspect there are a few of us > out there. For the moment, the best tips I've received came from this > mailing list and maybe this is the way to go. > > Thanks again for the help, > Marc > Marc, I am happy to hear that the index is working, but I really can't take any credit. I was only passing on information that I gained from these mailing lists because I was in a similar situation once and I greatly appreciated the help provided by Herouth Maoz and Gene Selkov, Jr. Also, I am not that familiar with this project, so I don't have any fine tuning suggestions for you. If you do find out any information about the advantages of each of the character types, I'm sure it would be useful to many subscribers of this list, including myself. Regards, ../fam -- Frank A. Mandarino fam@risca.com
I would like to allow any user which has an unix account in our system to be able to connect a DB. Can be postgres configured that an unix user become automatically also a postgres user ? I can figure out some ways to do it yet I'm looking for the most transparent one. Thanks a lot. Tomas Winkler The System Group CSE HUJI
Tomas B. Winkler writes: > I would like to allow any user which has an unix account in our system to > be able to connect a DB. Can be postgres configured that an unix user > become automatically also a postgres user ? I can figure out some ways > to do it yet I'm looking for the most transparent one. > Thanks a lot. Use ident authentication with a map name of "sameuser". See the documentation in the file pg_hba.conf. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Tue, 19 Sep 2000, Tomas B. Winkler wrote: > > I would like to allow any user which has an unix account in our system to > be able to connect a DB. Can be postgres configured that an unix user > become automatically also a postgres user ? I can figure out some ways > to do it yet I'm looking for the most transparent one. Back in my Oracle days on IBM RS6000 I seem to remember we had to manually add the user as an Oarcle user. This included sometimes two accounts. The normal user and an OPS$usename account so they could login automagically to an Oracle instance. Basically I think you'll need to add them as part of the adduser/useradd process and/or run a script against the passwd file to create them. rod -- Roderick A. Anderson raanders@altoplanos.net Altoplanos Information Systems, Inc. Voice: 208.765.6149 212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
I haven't found any documentation about pg_ident.conf file format. Tomas Winkler The System Group CSE HUJI On Tue, 19 Sep 2000, Peter Eisentraut wrote: > Tomas B. Winkler writes: > > > I would like to allow any user which has an unix account in our system to > > be able to connect a DB. Can be postgres configured that an unix user > > become automatically also a postgres user ? I can figure out some ways > > to do it yet I'm looking for the most transparent one. > > Thanks a lot. > > Use ident authentication with a map name of "sameuser". See the > documentation in the file pg_hba.conf. > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > >
Tomas B. Winkler writes: > I haven't found any documentation about pg_ident.conf file format. It contains lines of the form mapname unixname postgres-name but if you use the map "sameuser" you don't need it. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/