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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Oleg Bartunov Sent: Wednesday, February 27, 2008 9:47 PM To: Dan Kaplan Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxx, 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
