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

Re: Optimizing 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: Optimizing t1.col like '%t2.col%'
Date: 2008-02-27 20:26:23
Message-ID: 7248.1204143983@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-02-27 20:36:04
Subject: Re: PG planning randomly ?
Previous:From: Tom LaneDate: 2008-02-27 20:02:46
Subject: Re: disabling an index without deleting it?

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