My mistake, things don't get much better.
I'm selecting URLs out of a database like this:
SELECT * FROM table WHERE url ~ '^http://.*something.*$';
This still uses a sequential scan but cuts the time down to 76,351 from 212,651 using
WHERE url LIKE '%something%';
The full text indexing doesn't look quite right as there are no spaces in this data.
Also, using something like:
WHERE position( 'something', url ) > 0
is a bit worse, giving 84,259.
--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
> -----Original Message-----
> From: David Olbersen
> Sent: Thursday, March 20, 2003 3:19 PM
> To: pgsql-sql@postgresql.org
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Help with LIKE
>
>
> Josh,
>
> > That's what's called an "unanchored text search". That kind
> > of query cannot be indexed using a regular index.
>
> Duh, should have tried the anchors to get what I wanted...
>
> > What you need is called "Full Text Indexing" or "Full Text
> > Search". Check
> > out two resources:
>
> This isn't actually what I was looking for, the anchor works
> better (only 5.87 now!)
>
> Thanks for the reminder!
>
> --------------------------
> David Olbersen
> iGuard Engineer
> 11415 West Bernardo Court
> San Diego, CA 92127
> 1-858-676-2277 x2152
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>