Optimizing t1.col like '%t2.col%'

From: "Dan Kaplan" <dkaplan(at)citizenhawk(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Optimizing t1.col like '%t2.col%'
Date: 2008-02-27 19:47:43
Message-ID: 00c801c87979$9f3da480$1d00a8c0@dan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-02-27 20:02:46 Re: disabling an index without deleting it?
Previous Message Bill Moran 2008-02-27 19:34:49 Re: questions about CLUSTER