Josh Berkus <josh@agliodbs.com> writes:
>> 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.
Actually, it *can* use an index ... but the index condition will only
use the characters before the ".*", ie, "http://". Which is just about
useless if you're searching a column of URLs :-(
I agree that tsearch or OpenFTS are the tools to be looking at.
regards, tom lane