"Dan Kaplan" <dkaplan@xxxxxxxxxxxxxxx> writes: > I learned a little about pg_trgm here: > http://www.sai.msu.su/~megera/postgres/gist/pg_trgm/README.pg_trgm There's also real documentation in the 8.3 release: http://www.postgresql.org/docs/8.3/static/pgtrgm.html AFAIK pg_trgm hasn't changed much lately, so you should be able to rely on that for recent earlier branches. > But this seems like it's for finding similarities, not substrings. How can > I use it to speed up t1.col like '%t2.col%'? The idea is to use it as a lossy index. You make a trigram index on t1.col and then do something like ... where t1.col % t2.col and t1.col like ('%'||t2.col||'%'); The index gets you the %-matches and then you filter for the exact matches with LIKE. The similarity threshold (set_limit()) has to be set low enough that you don't lose any desired matches, but not so low that you get everything in the table back. Not sure how delicate that will be. It might be unworkable, but surely it's worth a try. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
