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

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"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