Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A27D02E0055 for ; Wed, 27 Feb 2008 15:47:50 -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 86806-09 for ; Wed, 27 Feb 2008 15:47:46 -0400 (AST) X-Greylist: delayed 00:28:20.428146 by SQLgrey-1.7.5 Received: from smtp110.sat.emailsrvr.com (smtp110.sat.emailsrvr.com [66.216.121.110]) by postgresql.org (Postfix) with ESMTP id 472982E0057 for ; Wed, 27 Feb 2008 15:47:46 -0400 (AST) Received: from relay1.relay.sat.mlsrvr.com (localhost [127.0.0.1]) by relay1.relay.sat.mlsrvr.com (SMTP Server) with ESMTP id 2864A2046BB; Wed, 27 Feb 2008 14:47:45 -0500 (EST) Received: by relay1.relay.sat.mlsrvr.com (Authenticated sender: dkaplan-AT-typosquasher.com) with ESMTP id 21EC92042CA for ; Wed, 27 Feb 2008 14:47:43 -0500 (EST) From: "Dan Kaplan" To: Subject: Optimizing t1.col like '%t2.col%' Date: Wed, 27 Feb 2008 11:47:43 -0800 Organization: CitizenHawk, Inc. Message-ID: <00c801c87979$9f3da480$1d00a8c0@dan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C9_01C87936.911A6480" X-Mailer: Microsoft Office Outlook 11 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 Thread-Index: Ach5eZ6WdjIKOu7KQj6tCkupXnat7w== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/379 X-Sequence-Number: 28921 This is a multi-part message in MIME format. ------=_NextPart_000_00C9_01C87936.911A6480 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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%' 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. ------=_NextPart_000_00C9_01C87936.911A6480 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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%'

 

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.

 

------=_NextPart_000_00C9_01C87936.911A6480--