Re: Search string without tsearch

2008/1/28, A.Burbello <burbello3000(at)yahoo(dot)com(dot)br>:
> Hi people,
>
> I would like to some suggestion to search person name
> in a table that has more than 150 milions of rows ...
>
> e.g ... name like 'JOHN%SMITH';
>
> I know there is contrib Tsearch, but I can't for
> political reasons.
> Is there any way to do with good performance???

For LIKE 'John%Smith' you can create one index on the column and one
on reverse(column), and use LIKE 'John%' AND LIKE reverse('%Smith').
The first condition should use the forward index, the second one the
reverse index.

The reverse function needs to be implemented in C for speed, I'm sure
someone else on the list can help out with that.

Markus