Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 91CBD2E0110 for ; Fri, 29 Feb 2008 19:52:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57890-04 for ; Fri, 29 Feb 2008 19:52:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp130.sat.emailsrvr.com (smtp130.sat.emailsrvr.com [66.216.121.130]) by postgresql.org (Postfix) with ESMTP id B813D2E010A for ; Fri, 29 Feb 2008 19:52:31 -0400 (AST) Received: from relay3.relay.sat.mlsrvr.com (localhost [127.0.0.1]) by relay3.relay.sat.mlsrvr.com (SMTP Server) with ESMTP id 169931D5F1C; Fri, 29 Feb 2008 18:52:30 -0500 (EST) Received: by relay3.relay.sat.mlsrvr.com (Authenticated sender: dkaplan-AT-typosquasher.com) with ESMTP id E573C1CD177 for ; Fri, 29 Feb 2008 18:52:29 -0500 (EST) From: "Dan Kaplan" Cc: References: <00c001c87975$a907e2e0$1d00a8c0@dan> Subject: Re: t1.col like '%t2.col%' Date: Fri, 29 Feb 2008 15:52:31 -0800 Organization: CitizenHawk, Inc. Message-ID: <001401c87b2e$26386000$1d00a8c0@dan> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 thread-index: Ach5zZhFf2uiG79tQKSHXaW8AfpnzQBYIOMg X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.791 tagged_above=0 required=5 tests=AWL=-0.790, MISSING_HEADERS=1.581 X-Spam-Level: X-Archive-Number: 200802/407 X-Sequence-Number: 28949 I learned a little about pg_trgm here: http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm But this seems like it's for finding similarities, not substrings. How can I use it to speed up t1.col like '%t2.col%'? Thanks, Dan -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Oleg Bartunov Sent: Wednesday, February 27, 2008 9:47 PM To: Dan Kaplan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] t1.col like '%t2.col%' On Wed, 27 Feb 2008, Dan Kaplan wrote: > I've got a lot of rows in one table and a lot of rows in another table. I > want to do a bunch of queries on their join column. One of these is like > this: t1.col like '%t2.col%' We have an idea how to speedup wildcard search at the expense of the size - we have to index all permutation of the original word. Then we could use GIN for quieries like a*b. > > > > I know that always sucks. I'm wondering how I can make it better. First, I > should let you know that I can likely hold both of these tables entirely in > ram. Since that's the case, would it be better to accomplish this with my > programming language? Also you should know that in most cases, t1.col and > t2.col is 2 words or less. I'm not sure if that matters, I mention it > because it may make tsearch2 perform badly. > contrib/pg_trgm should help you. > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match