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 =FAnico sem limite de espa=E7o par=
a armazenamento!
http://br.mail.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Search string without tsearch

On Jan 28, 2008 11:38 AM, A.Burbello 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?

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Search string without tsearch

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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Search string without tsearch

2008/1/28, A.Burbello :
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq