X-Original-To: pgsql-performance@postgresql.org
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP
	id 24DB8476355; Thu, 20 Mar 2003 16:55:37 -0500 (EST)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2912779; Thu, 20 Mar 2003 13:55:27 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "David Olbersen" <DOlbersen@stbernard.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Help with LIKE
Date: Thu, 20 Mar 2003 13:55:32 -0800
User-Agent: KMail/1.4.3
Cc: <pgsql-performance@postgresql.org>
References: <E7E213858379814A9AE48CA6754F5ECB0D6C2D@mail01.stbernard.com>
In-Reply-To: <E7E213858379814A9AE48CA6754F5ECB0D6C2D@mail01.stbernard.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303201355.32718.josh@agliodbs.com>
X-Archive-Number: 200303/142
X-Sequence-Number: 1440

David,

> I have a table with 8,628,633 rows that I'd LIKE to search (ha ha).
>=20
> I have a very simple query:
>   SELECT * FROM tableA WHERE column1 LIKE '%something%';

That's what's called an "unanchored text search".   That kind of query cann=
ot=20
be indexed using a regular index.

What you need is called "Full Text Indexing" or "Full Text Search".   Check=
=20
out two resources:

1) contrib/tsearch in your PostgreSQL source code;
2) OpenFTS (www.openfts.org).

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco