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 68BC3329F03 for ; Mon, 27 Sep 2004 18:36:56 +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 67546-05 for ; Mon, 27 Sep 2004 17:36:50 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 7618A329E7D for ; Mon, 27 Sep 2004 18:36:49 +0100 (BST) Received: from [192.168.1.51] (dsl093-038-087.pdx1.dsl.speakeasy.net [66.93.38.87]) (authenticated) by hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i8RHaGg29255; Mon, 27 Sep 2004 10:36:16 -0700 Message-ID: <41584F91.6000109@commandprompt.com> Date: Mon, 27 Sep 2004 10:36:17 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Kris Kiger Cc: pgsql-admin@postgresql.org Subject: Re: tsearch2 poor performance References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net> <4158409E.8060109@musicrebellion.com> <41584E49.5040301@musicrebellion.com> In-Reply-To: <41584E49.5040301@musicrebellion.com> Content-Type: multipart/mixed; boundary="------------070501030808070004040900" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=RAZOR2_CHECK X-Spam-Level: X-Archive-Number: 200409/252 X-Sequence-Number: 14955 This is a multi-part message in MIME format. --------------070501030808070004040900 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, I might of missed this on a previous message, BUT what type of hardware are we dealing with here? Is it possible that we just don't have enough IO/RAM to push this? J Kris Kiger wrote: > 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 >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL --------------070501030808070004040900 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard --------------070501030808070004040900--