Re: [GENERAL] 50 MB Table - Mailing list pgsql-general

I've been shuffling thru the 'LIKE' code in pg, but whilst perusing, it
occurred to me that I could use a function for this thing. Since i'm
only looking at the first part of the string I use...

SELECT * FROM info WHERE substring(street_name from 1 to 4) = 'MAIN'

...and the query completes in under 2 secs. Stupidly obvious now but I
thought I would share this blinding insight with the list ;)

Thanks again for all your help.

cheers
jb

Paul Condon wrote:
>
> The example you give, LIKE 'MAIN%', should be able to use the index, but I'm
> not that expert on the internals of PostgreSQL. I was speaking from a general
> knowledge of SQL. I was supposing you were trying to do something, such as,
> LIKE '%BLVD%'. In this latter case, an index will bring you no benefit.
>
> Perhaps it is working faster than it would have, had there been no index. Have
> you tried dropping the index and seeing if the performance gets worse? To make
> this test more powerful, choose a pattern that matches very few rows in your
> table.
>
> JB wrote:
>
> > 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
> >

--
If everything is coming your way then you're in the wrong lane.