Message-ID: <4654632C.4020005@archonet.com>
Date: Wed, 23 May 2007 16:52:12 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Andy <frum@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE search and performance
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
In-Reply-To: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andy wrote:
> SELECT * FROM table
>                              WHERE name like '%john%' or street like '%srt%'
>  
> Anyway, the query planner always does seq scan on the whole table and that
> takes some time. How can this be optimized or made in another way to be
> faster?
>  
> I tried to make indexes on the columns but no success. 

None of the normal indexes will work for finding text in the middle of a 
string. If you do think of a simple way of solving this, drop a short 
letter explaining your idea to your local patent office followed by the 
Nobel prize committee.

However, one of the contrib packages is "tsearch2" which is designed to 
do keyword searches on text for you. It'll also handle stemming (e.g. 
"search" will match "searching" etc.) with the right choice of 
dictionary. Loads of other clever stuff in it too.

It's one of the optional packages with most Linux packaging systems and 
on the Windows one too. If you install from source see the contrib/ 
directory for details.

-- 
   Richard Huxton
   Archonet Ltd