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

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

From: "Dan Kaplan" <dkaplan(at)citizenhawk(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: t1.col like '%t2.col%'
Date: 2008-02-29 23:52:31
Message-ID: 001401c87b2e$26386000$1d00a8c0@dan (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I learned a little about pg_trgm here:

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


-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Oleg Bartunov
Sent: Wednesday, February 27, 2008 9:47 PM
To: Dan Kaplan
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] t1.col like '%t2.col%'

On Wed, 27 Feb 2008, Dan Kaplan wrote:

> 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%'

We have an idea how to speedup wildcard search at the expense of the size - 
we have to index all permutation of the original word. Then we could
use GIN for quieries like a*b.

> I know that always sucks.  I'm wondering how I can make it better.  First,
> should let you know that I can likely hold both of these tables entirely
> ram.  Since that's the case, would it be better to accomplish this with my
> programming language?  Also you should know that in most cases, t1.col and
> t2.col is 2 words or less.  I'm not sure if that matters, I mention it
> because it may make tsearch2 perform badly.

contrib/pg_trgm should help you.


Oleg Bartunov, Research Scientist, Head of AstroNet (,
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su,
phone: +007(495)939-16-83, +007(495)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

In response to


pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2008-02-29 23:56:42
Subject: Re: t1.col like '%t2.col%'
Previous:From: Mark KirkwoodDate: 2008-02-29 22:33:10
Subject: Re: 12 disks raid setup

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