Thread: Like search performance.

Hi all,

I have a fairly large table with a char(20) field in it which I search on
quite a bit.  The problem is that I tend to do a lot of
"...where field like '%-d%'" type searches on this field.

Is there any to speed up this type of search?

TIA,

Mike Diehl.

Jeffrey,

The best thing you can do is to have the wildcard % as
late as possible in your search condition.
So do like 'd%' instead of like '%d%' if you can.

Regards,
Nikolaus


On Wed, 30 Apr 2003 10:34:40 -0600, "Diehl, Jeffrey"
wrote:

>
> Hi all,
>
> I have a fairly large table with a char(20) field in
it
> which I search on
> quite a bit.  The problem is that I tend to do a lot
of
> "...where field like '%-d%'" type searches on this
> field.
>
> Is there any to speed up this type of search?
>
> TIA,
>
> Mike Diehl.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

I'm not an expert, but AFAIK locale and collation heavily affects LIKE, and
thus, IIRC there is no index search for like, maybe except the simplest
locales (maybe C and/or en_US?)

But if you mean it... there is a nasty trick in the archives:

http://archives.postgresql.org/pgsql-general/2002-08/msg00819.php

Really, really nasty, but really nice at the same time.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Diehl, Jeffrey" <jdiehl@sandia.gov>
To: <pgsql-sql@postgresql.org>; <pgsql-performance@postgresql.org>
Sent: Wednesday, April 30, 2003 6:34 PM
Subject: [PERFORM] Like search performance.


> Hi all,
>
> I have a fairly large table with a char(20) field in it which I search on
> quite a bit.  The problem is that I tend to do a lot of
> "...where field like '%-d%'" type searches on this field.
>
> Is there any to speed up this type of search?
>
> TIA,
>
> Mike Diehl.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Mike,

> I have a fairly large table with a char(20) field in it which I search on
> quite a bit.  The problem is that I tend to do a lot of
> "...where field like '%-d%'" type searches on this field.
>
> Is there any to speed up this type of search?

Yes.  See the tsearch module in /contrib in your postgresql source.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco