Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id F3EBD9F9A37 for ; Tue, 11 Sep 2007 16:08:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 34418-10 for ; Tue, 11 Sep 2007 16:08:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from relay.icomedias.com (relay.icomedias.com [62.99.232.66]) by postgresql.org (Postfix) with ESMTP id 8BEF19F949A for ; Tue, 11 Sep 2007 16:08:37 -0300 (ADT) Received: from [10.192.17.128] ([10.192.17.128]) by relay.icomedias.com (8.13.7/8.13.7) with ESMTP id l8BIpPGO012522; Tue, 11 Sep 2007 20:51:26 +0200 Message-ID: <46E6E7B5.8000807@sime.com> Date: Tue, 11 Sep 2007 21:08:37 +0200 From: Mario Weilguni User-Agent: Thunderbird 1.5.0.13 (X11/20070824) MIME-Version: 1.0 To: Fernan Aguero CC: PostgreSQL Performance Subject: Re: efficient pattern queries (using LIKE, ~) References: <20070911185442.GA38153@iib.unsam.edu.ar> In-Reply-To: <20070911185442.GA38153@iib.unsam.edu.ar> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.57 on 10.192.64.104 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/191 X-Sequence-Number: 26736 Fernan Aguero schrieb: > Hi, > > I have a table containing some ~13 million rows. Queries on > indexed fields run fast, but unanchored pattern queries on a > text column are slooooow. Indexing the column doesn't help > (this is already mentioned in the manual). > http://www.postgresql.org/docs/8.2/interactive/indexes-types.html > > However, no alternative solution is mentioned for indexing > and/or optimizing queries based on unanchored patterns: > i.e. description LIKE '%kinase%'. Maybe trigram search might help you? Never tried it myself, but it seems to be able to handle substring searches.