Re: String searching

Jonathan Vanasco wrote:
> On Nov 17, 2014, at 12:55 PM, Robert DiFalco wrote:
>> SELECT * FROM MyTable WHERE upper(FullName) LIKE upper('%John%');
>>
>> That said, which would be the best extension module to use? A "gist" index on the uppercased column?
>> Or something else? Thanks!
>
> Performance wise, I think a function index would probably be the best:
>
> CREATE INDEX mytable_lower_fullname_idx ON mytable(lower(fullname));
>
> SELECT * FROM mytable WHERE lower(fullname) LIKE lower('%john%');

That index wouldn't help with the query at all.

If you really need a full substring search (i.e., you want to find
"howardjohnson"), the only thing that could help are trigram indexes.

But maybe you can lower the requirements to a prefix search (i.e.,
you want to find "john" and "johnson"), in which case a full text search
with an appropriate index would do the trick (if you use a prefix search pattern).

Yours,
Laurenz Albe