X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 33E879DCC73 for ; Wed, 22 Mar 2006 08:25:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32212-02 for ; Wed, 22 Mar 2006 08:25:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id E3DAE9DCC5B for ; Wed, 22 Mar 2006 08:25:42 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 55A2C56423; Wed, 22 Mar 2006 06:25:46 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Wed, 22 Mar 2006 06:25:45 -0600 Date: Wed, 22 Mar 2006 06:25:45 -0600 From: "Jim C. Nasby" To: "Craig A. James" Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Poor performance o Message-ID: <20060322122545.GZ15742@pervasive.com> References: <43F38867.6010701@gpdnet.co.uk> <19510.1140036968@sss.pgh.pa.us> <19779.1140038874@sss.pgh.pa.us> <43F39E53.1020009@gpdnet.co.uk> <20781.1140046109@sss.pgh.pa.us> <19570.1142971720@sss.pgh.pa.us> <442080D1.5090803@modgraph-usa.com> <6629.1142983995@sss.pgh.pa.us> <4420A290.2050908@modgraph-usa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4420A290.2050908@modgraph-usa.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060322:cjames@modgraph-usa.com::Pit/mN+/ykgzZxWw:00000000000000 00000000000000000000000036o+ X-Hashcash: 1:20:060322:tgl@sss.pgh.pa.us::jySSTqPbeJktjay/:00000000000000000000 000000000000000000000000190m X-Hashcash: 1:20:060322:pgsql-performance@postgresql.org::6MDqtPq70QDYimP/:00000 0000000000000000000000000KOB X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.107 required=5 tests=[AWL=0.107] X-Spam-Score: 0.107 X-Spam-Level: X-Archive-Number: 200603/446 X-Sequence-Number: 17883 On Tue, Mar 21, 2006 at 05:04:16PM -0800, Craig A. James wrote: > Tom Lane wrote: > >"Craig A. James" writes: > >>It looks to me like the problem is the use of nested loops when a hash > >>join should be used, but I'm no expert at query planning. > > > >Given the sizes of the tables involved, you'd likely have to boost up > >work_mem before the planner would consider a hash join. What nondefault > >configuration settings do you have, anyway? > > shared_buffers = 20000 > work_mem = 32768 > effective_cache_size = 300000 > > This is on a 4GB machine. Is there a guideline for work_mem that's related > to table size? Something like, "allow 2 MB per million rows"? No. The general guide is "set it as large as possible without making the machine start swapping." In some cases, you'll want to bump it up much higher for certain queries, especially if you know those queries will only run one at a time. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461