* Listmail <[EMAIL PROTECTED]> [20070420 11:25]: > You want trigram based search. > ie. > > postgresql -> 'pos', 'ost', 'stg', 'tgr', 'gre', 'res', > 'esq', 'sql' > > searching for 'gresq' is searching for 'gre' and 'res' and > 'esq' which is good friends with bitmap scan. Then a little LIKE > '%gresq%' to filter the results.
I'm not sure how that would fit in with tsearch2 to do full text search so that I can do queries like select * from content where plainto_tsquery(:q) @@ to_tsvector(body) If the possible substrings were already indexed like Oleg suggested in his reply through writing a custom C dictionary, a query like above with q='foo' would find rows from the table content where body contains 'foobar' for instance. However I've seen the example to create a trigram index on a unique word list to provide alternative spelling suggestions to the user which looked very useful. > PS : indexing all substring means for long words you get huge > number of lexems... I'm aware of that and in my case I don't think it will be a problem. It is for a type-ahead search web interface so actually it only requires indexing all possible substrings starting from char 1, ie. p, po, pos, post, postg, postgr, postgre, postgres, postgresq, postgresql. Til ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
