From: Richard Huxton <dev@archonet.com>
To: Andy <frum@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: LIKE search and performance
Date: Wed, 23 May 2007 16:52:12 +0100
Message-ID: <4654632C.4020005@archonet.com> (raw)
In-Reply-To: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, frum@ar-sd.net
Subject: Re: LIKE search and performance
In-Reply-To: <4654632C.4020005@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox