From dkaplan@citizenhawk.com Wed Feb 27 19:47:50 2008 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-- From tgl@sss.pgh.pa.us Wed Feb 27 20:26:46 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 505302E004F for ; Wed, 27 Feb 2008 16:26:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 44770-03-5 for ; Wed, 27 Feb 2008 16:26:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C2CAD2E0051 for ; Wed, 27 Feb 2008 16:26:36 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1RKQNCV007249; Wed, 27 Feb 2008 15:26:23 -0500 (EST) To: "Dan Kaplan" cc: pgsql-performance@postgresql.org Subject: Re: Optimizing t1.col like '%t2.col%' In-reply-to: <00c801c87979$9f3da480$1d00a8c0@dan> References: <00c801c87979$9f3da480$1d00a8c0@dan> Comments: In-reply-to "Dan Kaplan" message dated "Wed, 27 Feb 2008 11:47:43 -0800" Date: Wed, 27 Feb 2008 15:26:23 -0500 Message-ID: <7248.1204143983@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/382 X-Sequence-Number: 28924 "Dan Kaplan" writes: > 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. tsearch or pg_trgm could probably help. Are you really after exact substring-match semantics, or is this actually a poor man's substitute for full text search? If you just want substrings then see pg_trgm, if you want text search see tsearch. regards, tom lane