Skip site navigation (1) Skip section navigation (2)

Re: 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: t1.col like '%t2.col%'
Date: 2008-03-01 02:10:47
Message-ID: 21195.1204337447@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Dan Kaplan" <dkaplan(at)citizenhawk(dot)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

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2008-03-01 04:56:54
Subject: Re: 12 disks raid setup
Previous:From: Joshua D. DrakeDate: 2008-03-01 01:39:10
Subject: Re: t1.col like '%t2.col%'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group