X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E234A329D03 for ; Mon, 27 Sep 2004 18:30:55 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64274-09 for ; Mon, 27 Sep 2004 17:30:50 +0000 (GMT) Received: from mail.musicrebellion.com (dsl093-203-229.ind1.dsl.speakeasy.net [66.93.203.229]) by svr1.postgresql.org (Postfix) with ESMTP id 454D9329EF0 for ; Mon, 27 Sep 2004 18:30:50 +0100 (BST) Received: from [10.0.1.102] (helo=musicrebellion.com ident=kris) by mail.musicrebellion.com with asmtp (Exim 4.34 #1 (Debian)) id 1CBzKp-0000yg-R1 for ; Mon, 27 Sep 2004 12:30:47 -0500 Message-ID: <41584E49.5040301@musicrebellion.com> Date: Mon, 27 Sep 2004 12:30:49 -0500 From: Kris Kiger User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623 Debian/1.0.0-0.woody.1 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: tsearch2 poor performance References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net> <4158409E.8060109@musicrebellion.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/251 X-Sequence-Number: 14954 Oleg, Thanks for the help on this. The query I used to return the 508 number is: SELECT * FROM stat('SELECT vector FROM product') ORDER BY ndoc desc, word ; Testing says, the more words I use, the faster the query is. My original search word, 'oil', appears in 226,357 documents 233,266 times. As far as distinct words go, 'oil' is middle of the road for occurences. As it is set up now, the best search time I am getting on this single word is roughly 22 seconds. Kris Oleg Bartunov wrote: >Kris, > >do you actually have only 508 disctinct words ? Could you try >more complex queries, say 2-3 words. Does these queries run faster ? > > > Oleg >On Mon, 27 Sep 2004, Kris Kiger wrote: > > > >>Regardless of caching, the queries are still taking 19~20 seconds to run >>on the 3,000,000 rows. I've played with performance tuning and nothing >>seems to make much of a difference. If I am reading that list from stat >>correctly, then I am operating on 508 distinct words. Is this the >>performance I should expect from tsearch2? Or is something still awry? >> I'm inclined to think something else is wrong, after reading some >>other people's tsearch performance stats. Thanks! >> >>Kris >> >> >> > Regards, > Oleg > >