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