Skip site navigation (1) Skip section navigation (2)

Re: Help with LIKE

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "David Olbersen" <DOlbersen(at)stbernard(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help with LIKE
Date: 2003-03-21 01:27:21
Message-ID: 200303201727.21401.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
David,

> 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.*$';

That search still requires a seq scan, since it has "gaps" in the seqence of 
characters.  That is,

url ~ '^http://www.something.*' could use an index, but your search above 
cannot.

You may be right that the standard OpenFTS indexing won't help you in this 
case, since you're really searching for fragments of a continuous text 
string.

One thing I might suggest is that you look for ways that you might be able to 
break out the text you're searching for with a function.  For example, if you 
were searching strictly on the domain SLD name, then you could create an 
"immutable"  function called if_split_sld(TEXT) and index on that.

If you are really searching for "floating" text within the string, I believe 
that there are some options in tseach to help you, but they may not end up 
improving performance much.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-03-21 02:06:21
Subject: Re: Help with LIKE
Previous:From: David OlbersenDate: 2003-03-20 23:35:39
Subject: Re: Help with LIKE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group