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

Поиск
Список
Период
Сортировка
От Hannu Krosing
Тема Re: why index scan not working when using 'like'?
Дата
Msg-id 1069867988.2749.26.camel@fuji.krosing.net
обсуждение исходный текст
Ответ на Re: why index scan not working when using 'like'?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Tom Lane kirjutas T, 25.11.2003 kell 23:29:
> Josh Berkus <josh@agliodbs.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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: Impossibly slow DELETEs
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Maximum Possible Insert Performance?