Re: Help with LIKE

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(at)postgresql(dot)org
> Cc: pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org
>