pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLike search performance.
4+ messages / 4 participants
[nested] [flat]
* Like search performance.
@ 2003-04-30 16:34 Diehl, Jeffrey <jdiehl@sandia.gov>
2003-04-30 16:59 ` Re: Like search performance. SZUCS Gábor <surrano@mailbox.hu>
2003-04-30 17:33 ` Re: Like search performance. Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 4+ messages in thread
From: Diehl, Jeffrey @ 2003-04-30 16:34 UTC (permalink / raw)
To: pgsql-sql; pgsql-performance@postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Like search performance.
2003-04-30 16:34 Like search performance. Diehl, Jeffrey <jdiehl@sandia.gov>
@ 2003-04-30 16:59 ` SZUCS Gábor <surrano@mailbox.hu>
1 sibling, 0 replies; 4+ messages in thread
From: SZUCS Gábor @ 2003-04-30 16:59 UTC (permalink / raw)
To: pgsql-performance@postgresql.org
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Like search performance.
2003-04-30 16:34 Like search performance. Diehl, Jeffrey <jdiehl@sandia.gov>
@ 2003-04-30 17:33 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-04-30 17:33 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; pgsql-sql; pgsql-performance@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Like search performance.
@ 2003-04-30 17:08 Nikolaus Dilger <nikolaus@dilger.cc>
0 siblings, 0 replies; 4+ messages in thread
From: Nikolaus Dilger @ 2003-04-30 17:08 UTC (permalink / raw)
To: jdiehl@sandia.gov; +Cc: pgsql-sql; pgsql-performance@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-04-30 17:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-30 16:34 Like search performance. Diehl, Jeffrey <jdiehl@sandia.gov>
2003-04-30 16:59 ` SZUCS Gábor <surrano@mailbox.hu>
2003-04-30 17:33 ` Josh Berkus <josh@agliodbs.com>
2003-04-30 17:08 Re: Like search performance. Nikolaus Dilger <nikolaus@dilger.cc>
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