Re: why index scan not working when using 'like'?

Tom Lane kirjutas T, 25.11.2003 kell 23:29:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > In regular text fields containing words, your problem is solvable with full
> > text indexing (FTI). Unfortunately, FTI is not designed for arbitrary
> > non-language strings. It could be adapted, but would require a lot of
> > hacking.
>
> I'm not sure why you say that FTI isn't a usable solution. As long as
> the gene symbols are separated by whitespace or some other non-letters
> (eg, "foo mif bar" not "foomifbar"), I'd think FTI would work.

If he wants to search on arbitrary substring, he could change tokeniser
in FTI to produce trigrams, so that "foomifbar" would be indexed as if
it were text "foo oom omi mif ifb fba bar" and search for things like
%mifb% should first do a FTI search for "mif" AND "ifb" and then simple
LIKE %mifb% to weed out something like "mififb".

There are ways to use trigrams for 1 and 2 letter matches as well.

-------------
Hannu