From: Dan Kaplan <dkaplan@citizenhawk.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: t1.col like '%t2.col%'
Date: Fri, 29 Feb 2008 15:52:31 -0800
Message-ID: <001401c87b2e$26386000$1d00a8c0@dan> (raw)
In-Reply-To: <Pine.LNX.4.64.0802280833040.31180@sn.sai.msu.ru>
References: <00c001c87975$a907e2e0$1d00a8c0@dan>
<Pine.LNX.4.64.0802280833040.31180@sn.sai.msu.ru>
I learned a little about pg_trgm here:
http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm
But this seems like it's for finding similarities, not substrings. How can
I use it to speed up t1.col like '%t2.col%'?
Thanks,
Dan
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Oleg Bartunov
Sent: Wednesday, February 27, 2008 9:47 PM
To: Dan Kaplan
Cc: pgsql-performance@postgresql.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,
I
> should let you know that I can likely hold both of these tables entirely
in
> 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.
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
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
match
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: dkaplan@citizenhawk.com
Subject: Re: t1.col like '%t2.col%'
In-Reply-To: <001401c87b2e$26386000$1d00a8c0@dan>
* 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