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 3CFB8538AA for ; Thu, 21 Apr 2005 09:36:30 -0300 (ADT) 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 65782-01 for ; Thu, 21 Apr 2005 12:36:25 +0000 (GMT) Received: from is.rice.edu (is.rice.edu [128.42.42.24]) by svr1.postgresql.org (Postfix) with ESMTP id B26ED5388B for ; Thu, 21 Apr 2005 09:36:25 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by is.rice.edu (Postfix) with ESMTP id A92C84182B; Thu, 21 Apr 2005 07:36:28 -0500 (CDT) Received: from is.rice.edu ([127.0.0.1]) by localhost (it.is.rice.edu [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 11211-01-33; Thu, 21 Apr 2005 07:36:24 -0500 (CDT) Received: by is.rice.edu (Postfix, from userid 18612) id 085E441AF6; Thu, 21 Apr 2005 07:36:23 -0500 (CDT) Date: Thu, 21 Apr 2005 07:36:23 -0500 From: Kenneth Marshall To: Jeff Cc: "Shoaib Burq (VPAC)" , pgsql-performance@postgresql.org Subject: Re: two queries and dual cpu (perplexed) Message-ID: <20050421123623.GA9371@it.is.rice.edu> References: <8c459b7b96e6c310d641cebdfa8dcab6@torgo.978.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8c459b7b96e6c310d641cebdfa8dcab6@torgo.978.org> User-Agent: Mutt/1.4.2i X-Virus-Scanned: by amavis-2.2.1 at is.rice.edu X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/639 X-Sequence-Number: 12036 On Thu, Apr 21, 2005 at 08:24:15AM -0400, Jeff wrote: > > On Apr 21, 2005, at 7:49 AM, Shoaib Burq (VPAC) wrote: > > >Now I have not touch the $PGDATA/postgresql.conf (As I know very little > >about memory tuning) Have run VACCUM & ANALYZE. > > > You should really, really bump up shared_buffers and given you have 8GB > of ram this query would likely benefit from more work_mem. > > >and the time taken is *twice* that for the original. The modification > >was > >minor. The queries do make use of both CPUs: > > > Is this an IO intensive query? If running both in parellel results in > 2x the run time and you have sufficient cpus it would (to me) indicate > you don't have enough IO bandwidth to satisfy the query. > I would add to Jeff's comments, that the default configuration parameters are fairly-to-very conservative which tends to produce plans with more I/O. Bumping your shared_buffers, work_mem, and effective_cache_size should allow the planner to favor plans that utilize more memory but require less I/O. Also, with small amounts of work_mem, hash joins cannot be used and the planner will resort to nested loops. Ken