X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 838EA9FA60C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Jul 2006 21:22:58 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 52960-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Jul 2006 21:22:49 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nz-out-0102.google.com (nz-out-0102.google.com
 [64.233.162.192])
	by postgresql.org (Postfix) with ESMTP id 2D6D29FA45F
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Jul 2006 21:22:48 -0300 (ADT)
Received: by nz-out-0102.google.com with SMTP id l1so1219315nzf
	for <pgsql-performance@postgresql.org>;
	Tue, 04 Jul 2006 17:22:47 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=YbC2iiRQnFKZ1Ms2viP4cLOSFXU7BZ2DbjXlwhRKbiuZKtPikNpBrU/Fzw3SLrECi0XNy4m7+JO9Yxd48GtrhzSZlLa+VYuLN1JrrNuH7VoGiJyFtHBqFpxUUulCYG3ir+rg28CstBkPjfwMR6F+M8UlIJOywgWGTMP3KxCIvBE=
Received: by 10.36.138.15 with SMTP id l15mr4588597nzd;
	Tue, 04 Jul 2006 17:22:47 -0700 (PDT)
Received: from ?10.1.1.20? ( [203.217.18.65])
	by mx.gmail.com with ESMTP id 15sm11781460nzp.2006.07.04.17.22.45;
	Tue, 04 Jul 2006 17:22:47 -0700 (PDT)
Message-ID: <44AB0652.2080606@gmail.com>
Date: Wed, 05 Jul 2006 10:22:42 +1000
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: genekhart@gmail.com
CC: pgsql-performance@postgresql.org
Subject: Re: optimizing LIKE '%2345' queries
References: <430d92a20607021450i1d228350j292d68e987ed58de@mail.gmail.com>	
	<Pine.LNX.4.64.0607041408450.20387@oynpxoyhr.vnfv.npgvirzrqvngrpu.pbz>
	<430d92a20607041327r4bcd6306ofec4adec6856c037@mail.gmail.com>
In-Reply-To: <430d92a20607041327r4bcd6306ofec4adec6856c037@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/21
X-Sequence-Number: 19881

Gene wrote:
> Thanks for the suggestion. Actually I went ahead and created a reverse
> function using plpgsql, created an index using reverse column and now
> my queries use "where reverse(column) like reverse('%2345') and it's
> using the index like i hoped it would! Now if I could figure out how
> to optimize like  '%2345%' queries. I don't want to create many
> indexes though the table is very write heavy.

You can't because that text can be anywhere inside the database field, 
so the whole field basically has to be checked to see if it's there.

You could check out full text indexing (tsearch2).

<shameless plug>
http://www.designmagick.com/article/27/PostgreSQL/Introduction-to-Full-Text-Indexing

-- 
Postgresql & php tutorials
http://www.designmagick.com/