JB,
The 20 seconds definitely sounds excessive. Have you done the following?
1) Run "vacuum analyze info"? If you have not, Postgres will not make use
of any indices.
2) Run an explain plan on your query to see what Postgres thinks it's going
to do?
In another post, Howie suggested more RAM. If speed is a primary concern, I
second his recommendation. Bump up your RAM and crank up the number of
shared memory buffers. Here is an example of the command I use to start up
Postgres:
postmaster -i -B 12000 -d 2 -o "-F -S 4096 -s" >&! server.log &
The "-B 12000" tells Postgres to set aside 12,000 8k buffers. With the size
of your table, you should easily be able to fit the whole thing into memory.
Keep in mind that your OS must have it's shared memory segment defined large
enough to handle all the buffers... in this case, about 100 Meg.
Phil Culberson
DAT Services
-----Original Message-----
From: JB [mailto:jimbag@kw.igs.net]
Sent: Monday, March 06, 2000 5:52 PM
To: Paul Condon
Cc: pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] 50 MB Table
Thanks for taking the time to reply. I think that I wasn't as clear as I
could be. This table is normalized and as far as I understand, what I'm
doing with it is not extraordinary. The schema is basically...
CREATE TABLE info (
lastname char(50),
street_name char(50),
street_number char(5),
... (a bunch of other stuff that works fine with '=')
);
CREATE INDEX nx_info1 ON info (lastname);
CREATE INDEX nx_info2 ON info (street_name);
The select is as simple as this in most cases...
SELECT * FROM info WHERE street_name LIKE 'MAIN%';
.,,the table about 50MB worth, about 70,000 records. I have an index on
'lastname' and 'street_name' and I need to search on each of these with
'LIKE'. So I was wondering about ways to speed this up. It's very slow.
It takes about 20 seconds for the above query. I even uppercased all the
names, hoping tht would help. I wondered if I'd used the wrong index
type (btree), or if there were some flags that would help. Is there a
way to bust the indexes out alpha on the first letter say, or some other
such scheme. BTW the machine is RH6.1 with 128mb ram, 27 GB, P350, no X
and no users (except me ;)
Paul Condon wrote:
>
> JB wrote:
>
> > I have a 50 MB +- table in postgres. The data is normalized so there's
> > not much I can do about the size. The tuples are about 512 bytes so
> > there's a pile of 'em. I need searching on of several fields, a couple
> > in particular are text fields that needs 'LIKE'. The problem is, the
> > thing is way too slow. So, I was wondering, before I go hunting for some
> > other solution, could anyone here point me to some ways to (hand)
> > optimize the searching in postgres? Different indexes, hashing and LIKE?
> > I'm not sure where to go with this.
> >
> > The basic criteria are:
> > - sizes of indexes, etc, is not an issue. There's lot's of room on the
> > box.
> > - the data is basically static so a read-only (if such a thing) is
> > fine.
> > - it needs to be FAST
> >
> > cheers
> > jb
> >
> > ************
>
> It sounds as if you have several different kinds of information encoded in
> a single column using special words or letter combinations. This is a
> violation of the ideal that data items should be "atomic." You should make
> a catalog of all the things that you want to be able to say about each
> tuple, and design a relational schema in which atomic assertion is given
> its own column (attribute). Then you will be able to create indices on
> each, and you won't have to use LIKE in your WHERE clauses.
>
> Paul
--
I'm in direct contact with many advanced fun CONCEPTS.
Paul Condon wrote:
>
> JB wrote:
>
> > I have a 50 MB +- table in postgres. The data is normalized so there's
> > not much I can do about the size. The tuples are about 512 bytes so
> > there's a pile of 'em. I need searching on of several fields, a couple
> > in particular are text fields that needs 'LIKE'. The problem is, the
> > thing is way too slow. So, I was wondering, before I go hunting for some
> > other solution, could anyone here point me to some ways to (hand)
> > optimize the searching in postgres? Different indexes, hashing and LIKE?
> > I'm not sure where to go with this.
> >
> > The basic criteria are:
> > - sizes of indexes, etc, is not an issue. There's lot's of room on the
> > box.
> > - the data is basically static so a read-only (if such a thing) is
> > fine.
> > - it needs to be FAST
> >
> > cheers
> > jb
> >
> > ************
>
> It sounds as if you have several different kinds of information encoded in
> a single column using special words or letter combinations. This is a
> violation of the ideal that data items should be "atomic." You should make
> a catalog of all the things that you want to be able to say about each
> tuple, and design a relational schema in which atomic assertion is given
> its own column (attribute). Then you will be able to create indices on
> each, and you won't have to use LIKE in your WHERE clauses.
>
> Paul
>
> ************
--
I'm in direct contact with many advanced fun CONCEPTS.
************
