From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dan Kaplan <dkaplan@citizenhawk.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: t1.col like '%t2.col%'
Date: Fri, 29 Feb 2008 21:10:47 -0500
Message-ID: <21195.1204337447@sss.pgh.pa.us> (raw)
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>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dkaplan@citizenhawk.com
Subject: Re: t1.col like '%t2.col%'
In-Reply-To: <21195.1204337447@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox