From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Dan Harris <fbsd@drivefaster.net>
Subject: Re: Query tuning help
Date: Sun, 8 May 2005 17:48:18 -0700
User-Agent: KMail/1.8
Cc: pgsql-performance@postgresql.org
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
In-Reply-To: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200505081748.18362.josh@agliodbs.com>

Dan,

> =A0=A0=A0=A0=A0=A0=A0=A0and ( =A0recordtext like '%RED%' or recordtext li=
ke '%CORVETTE%' =A0) =A0

It is simply not possible to use B-tree indexes on these kind of text queri=
es. =20
B-trees require you to start at the "left" side of the field, because B-tre=
es=20
locate records via <> tests.  "Anywhere in the field" text search requires =
a=20
=46ull Text Index.

> The reason for the redundant LIKE clause is that first, I only want
> those "incidentid"s that contain the words 'RED' and 'CORVETTE'. =A0BUT,
> those two words may exist across multiple records with the same
> incidentid. =A0Then, I only want to actually work with the rows that
> contain one of the words. =A0This query will repeat the same logic for
> however many keywords are entered by the user. =A0I have investigated
> text searching options and have not found them to be congruous with my
> application.

Sounds like you either need to restructure your application, restructure yo=
ur=20
database (so that you're not doing "anywhere in field" searches), or buy 32=
GB=20
of ram so that you can cache the whole table.

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