From: Hannu Krosing <hannu@tm.ee>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com, LIANHE SHAO <lshao2@jhmi.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: why index scan not working when using 'like'?
Date: Wed, 26 Nov 2003 19:33:08 +0200
Message-ID: <1069867988.2749.26.camel@fuji.krosing.net> (raw)
In-Reply-To: <8754.1069795744@sss.pgh.pa.us>
References: <3d83693d9fe0.3d9fe03d8369@jhmimail.jhmi.edu>
<200311251151.40781.josh@agliodbs.com>
<8754.1069795744@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hannu@tm.ee, tgl@sss.pgh.pa.us, lshao2@jhmi.edu
Subject: Re: why index scan not working when using 'like'?
In-Reply-To: <1069867988.2749.26.camel@fuji.krosing.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox