Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 188AF9FB7EC for ; Tue, 24 Apr 2007 00:39:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 72453-03 for ; Tue, 24 Apr 2007 00:39:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 2C67E9FB77A for ; Tue, 24 Apr 2007 00:39:42 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id l3O3dgbY008252 for ; Mon, 23 Apr 2007 23:39:42 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id l3O3dgXr008248 for ; Mon, 23 Apr 2007 23:39:42 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Mon, 23 Apr 2007 23:39:42 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: pgsql-performance@postgresql.org Subject: Re: TPC-H Scaling Factors X PostgreSQL Cluster Command In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/400 X-Sequence-Number: 24277 On Sat, 21 Apr 2007, Nelson Kotowski wrote: > I identified that the cluster command over the lineitem table (cluster > idx_lineitem on lineitem) is the responsible. I got to this conclusion > because when i run it in the 1GB and 2GB database i am able to complete > this script in 10 and 30 minutes each. But when i run this command over > the 5GB database, it simply seems as it won't end. Have you looked in the database log files for messages? Unless you changed some other parameters from the defaults that you didn't mention, I'd expect you've got a constant series of "checkpoint occuring too frequently" errors in there, which would be a huge slowdown on your index rebuild. Slowdowns from checkpoints would get worse with an increase of shared_buffers, as you report. The default setting for checkpoint_segments of 3 is extremely low for even a 1GB database. Try increasing that to 30, restart the server, and rebuild the index to see how much the 1GB case speeds up. If it's significantly faster (it should be), try the 5GB one again. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD