X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ED9C6D1B433 for ; Wed, 26 Nov 2003 17:33:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 78416-08 for ; Wed, 26 Nov 2003 13:33:25 -0400 (AST) Received: from fuji.krosing.net (silmet.estpak.ee [194.126.97.78]) by svr1.postgresql.org (Postfix) with ESMTP id 7EE77D1B43A for ; Wed, 26 Nov 2003 13:33:18 -0400 (AST) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id hAQHXEhr003791; Wed, 26 Nov 2003 19:33:15 +0200 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id hAQHX8rm003789; Wed, 26 Nov 2003 19:33:08 +0200 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: why index scan not working when using 'like'? From: Hannu Krosing To: Tom Lane Cc: josh@agliodbs.com, LIANHE SHAO , pgsql-performance@postgresql.org 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> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1069867988.2749.26.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Wed, 26 Nov 2003 19:33:08 +0200 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/268 X-Sequence-Number: 4828 Tom Lane kirjutas T, 25.11.2003 kell 23:29: > Josh Berkus 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