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 84B4D329F87 for ; Mon, 27 Sep 2004 19:45: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 90775-06 for ; Mon, 27 Sep 2004 18:45:49 +0000 (GMT) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id EC8C3329F95 for ; Mon, 27 Sep 2004 19:45:43 +0100 (BST) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id i8RIjWQT020885; Mon, 27 Sep 2004 22:45:32 +0400 (MSD) Date: Mon, 27 Sep 2004 22:45:32 +0400 (MSD) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Kris Kiger Cc: pgsql-admin@postgresql.org Subject: Re: tsearch2 poor performance In-Reply-To: <41584E49.5040301@musicrebellion.com> Message-ID: References: <71E37EF6B7DCC1499CEA0316A2568328DC9DF0@loki.wc.globexplorer.net> <4158409E.8060109@musicrebellion.com> <41584E49.5040301@musicrebellion.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/254 X-Sequence-Number: 14957 On Mon, 27 Sep 2004, 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. Does this time (22 seconds) is still better than seq. scan (no index) or standard 'LIKE' ? > > 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 > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83