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 800BBD1B439 for ; Wed, 26 Nov 2003 20:03:33 +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 03910-07 for ; Wed, 26 Nov 2003 16:03:09 -0400 (AST) Received: from jhuml1.jhmi.edu (jhuml1.jhmi.edu [162.129.234.20]) by svr1.postgresql.org (Postfix) with ESMTP id 1FAF7D1B446 for ; Wed, 26 Nov 2003 16:03:02 -0400 (AST) Received: from jhuml1.jhmi.edu (jhuml1.jhmi.edu [162.129.234.20]) by jhuml1.jhmi.edu (PMDF V6.2-X17 #30839) with SMTP id <0HOZ00IUP69ZN1@jhuml1.jhmi.edu> for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 15:02:53 -0500 (EST) Received: from jhuml1.jhmi.edu ([162.129.234.20]) by jhuml1.jhmi.edu (SAVSMTP 3.1.0.29) with SMTP id M2003112615025324392 for ; Wed, 26 Nov 2003 15:02:53 -0500 Received: from jhmimail.jhmi.edu (jhem2.jhmi.edu [162.129.8.23]) by jhuml1.jhmi.edu (PMDF V6.2-X17 #30839) with ESMTP id <0HOZ00IQ26CSN1@jhuml1.jhmi.edu> for pgsql-performance@postgresql.org; Wed, 26 Nov 2003 15:02:52 -0500 (EST) Received: from [162.129.178.60] by jhmimail.jhmi.edu (mshttpd); Wed, 26 Nov 2003 20:06:02 +0000 (GMT) Date: Wed, 26 Nov 2003 20:06:02 +0000 (GMT) From: LIANHE SHAO Subject: For full text indexing, which is better, tsearch2 or fulltextindex To: pgsql-performance@postgresql.org Message-id: <4c0ccf4c27ff.4c27ff4c0ccf@jhmimail.jhmi.edu> MIME-version: 1.0 X-Mailer: iPlanet Messenger Express 5.2 HotFix 1.17 (built Jun 23 2003) Content-type: text/plain; charset=us-ascii Content-language: en Content-transfer-encoding: 7bit Content-disposition: inline X-Accept-Language: en X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/275 X-Sequence-Number: 4835 Hi all, Which one is better (performance/easier to use), tsearch2 or fulltextindex? there is an example how to use fulltextindex in the techdocs, but I checked the contrib/fulltextindex package, there is a WARNING that fulltextindex is much slower than tsearch2. but tsearch2 seems complex to use, and I can not find a good example. Which one I should use? Any suggestions? thanks and Regards, William ----- Original Message ----- From: Hannu Krosing Date: Wednesday, November 26, 2003 5:33 pm Subject: Re: [PERFORM] why index scan not working when using 'like'? > 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 > tokeniserin 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 > simpleLIKE %mifb% to weed out something like "mififb". > > There are ways to use trigrams for 1 and 2 letter matches as well. > > ------------- > Hannu > > > ---------------------------(end of broadcast)----------------------- > ---- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your message can get through to the mailing list cleanly >