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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> 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 

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 

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


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-2017 The PostgreSQL Global Development Group