Skip site navigation (1) Skip section navigation (2)

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

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: josh(at)agliodbs(dot)com, LIANHE SHAO <lshao2(at)jhmi(dot)edu>,pgsql-performance(at)postgresql(dot)org
Subject: Re: why index scan not working when using 'like'?
Date: 2003-11-26 17:33:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Richard HuxtonDate: 2003-11-26 17:52:30
Subject: Re: expression (functional) index use in joins
Previous:From: Greg StarkDate: 2003-11-26 17:11:34
Subject: Re: Impossibly slow DELETEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group