Skip site navigation (1) Skip section navigation (2)

Re: Search string without tsearch

From: "Markus Bertheau" <mbertheau(dot)pg(at)googlemail(dot)com>
To: A(dot)Burbello <burbello3000(at)yahoo(dot)com(dot)br>
Cc: "Postgres Adm List" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Search string without tsearch
Date: 2008-01-30 04:34:02
Message-ID: 684362e10801292034g1a077655i4b67e314eb92f37a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: yogesh m chaudhariDate: 2008-01-30 04:42:23
Subject: how to create cluster(loadbanacing) of postgresql server
Previous:From: Guido BarosioDate: 2008-01-30 04:27:59
Subject: Re: Backup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group