To: "Dan Kaplan" <dkaplan@citizenhawk.com>
cc: pgsql-performance@postgresql.org
Subject: Re: t1.col like '%t2.col%' 
In-reply-to: <001401c87b2e$26386000$1d00a8c0@dan> 
References: <00c001c87975$a907e2e0$1d00a8c0@dan>
	<Pine.LNX.4.64.0802280833040.31180@sn.sai.msu.ru>
	<001401c87b2e$26386000$1d00a8c0@dan>
Comments: In-reply-to "Dan Kaplan" <dkaplan@citizenhawk.com>
	message dated "Fri, 29 Feb 2008 15:52:31 -0800"
Date: Fri, 29 Feb 2008 21:10:47 -0500
Message-ID: <21195.1204337447@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Dan Kaplan" <dkaplan@citizenhawk.com> writes:
> I learned a little about pg_trgm here:
> http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm

There's also real documentation in the 8.3 release:
http://www.postgresql.org/docs/8.3/static/pgtrgm.html
AFAIK pg_trgm hasn't changed much lately, so you should be able to
rely on that for recent earlier branches.

> But this seems like it's for finding similarities, not substrings.  How can
> I use it to speed up t1.col like '%t2.col%'?

The idea is to use it as a lossy index.  You make a trigram index on
t1.col and then do something like

	... where t1.col % t2.col and t1.col like ('%'||t2.col||'%');

The index gets you the %-matches and then you filter for the exact
matches with LIKE.

The similarity threshold (set_limit()) has to be set low enough that you
don't lose any desired matches, but not so low that you get everything
in the table back.  Not sure how delicate that will be.  It might be
unworkable, but surely it's worth a try.

			regards, tom lane