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: [EMAIL PROTECTED], 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
