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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Dan Kaplan" <dkaplan(at)citizenhawk(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizing t1.col like '%t2.col%'
Date: 2008-02-27 20:26:23
Message-ID: 7248.1204143983@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

"Dan Kaplan" <dkaplan(at)citizenhawk(dot)com> 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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-02-27 20:36:04 Re: PG planning randomly ?
Previous Message Tom Lane 2008-02-27 20:02:46 Re: disabling an index without deleting it?