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

"Dan Kaplan" <dkaplan@citizenhawk.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