Обсуждение: Search string without tsearch

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???

Thank you.


      Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/

On Jan 28, 2008 11:38 AM, A.Burbello <burbello3000@yahoo.com.br> wrote:
> 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???

Would pointing out to the PHBs that tsearch was integrated into pgsql
with v8.3 help convince them to stop being reactionary idiots?

On Mon, Jan 28, 2008 at 02:38:15PM -0300, A.Burbello wrote:
> 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???

No.  Also, rejecting the provided feature in the system for political
reasons is a poor technical choice.  I sympathise, but whoever the
politician is needs to get a clue, or you'll never be able to make the
system operate as it should.

A

2008/1/28, A.Burbello <burbello3000@yahoo.com.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