X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5CC5C52A5A for ; Thu, 31 Mar 2005 21:46:00 +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 70890-01 for ; Thu, 31 Mar 2005 20:45:56 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 81EFB529E2 for ; Thu, 31 Mar 2005 21:45:55 +0100 (BST) X-EthosMedia-Virus-Scanned: no infections found Received: from [67.103.45.218] (account josh@agliodbs.com HELO [192.168.1.122]) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7186925; Thu, 31 Mar 2005 12:47:50 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Yudie Pg Subject: Re: How to speed up word count in tsearch2? Date: Thu, 31 Mar 2005 12:50:45 -0800 User-Agent: KMail/1.7.1 Cc: pgsql-performance@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503311250.45932.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/690 X-Sequence-Number: 11395 Yudie, > (It is the 2nd posting, maybe the 1st one didn't goes thru) > I've tested several keyword count from 2 millions record book > description table that indexed with tseach2 indexing. > The result is always slow for first query attempt. Yes, this is because your tsearch2 index is getting pushed out of RAM. When the index is cached it's very, very fast but takes a long time to get loaded from disk. You need to look at what else is using RAM on that machine. And maybe buy more. -- --Josh Josh Berkus Aglio Database Solutions San Francisco