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 BE4823A4B02 for ; Fri, 26 Nov 2004 19:42:48 +0000 (GMT) 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 44182-09 for ; Fri, 26 Nov 2004 19:42:42 +0000 (GMT) Received: from server.gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id F27A63A4ACB for ; Fri, 26 Nov 2004 19:42:41 +0000 (GMT) Received: from [192.168.1.2] (unknown [192.168.1.2]) by server.gpdnet.co.uk (Postfix) with ESMTP id DC46FFBBB7; Fri, 26 Nov 2004 19:42:56 +0000 (GMT) Message-ID: <41A7873A.7000202@gpdnet.co.uk> Date: Fri, 26 Nov 2004 19:42:50 +0000 From: Gary Doades User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Postgres backend using huge amounts of ram References: <41A6420D.2030307@gpdnet.co.uk> <10676.1101497134@sss.pgh.pa.us> In-Reply-To: <10676.1101497134@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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: 200411/410 X-Sequence-Number: 9364 Tom Lane wrote: > > It's also worth noting that work_mem is temporarily set to > maintenance_work_mem, which you didn't tell us the value of: > It's left at the default. (16384). This would be OK if that is all it used for this type of thing. > > > My recollection is that hash join chooses hash table partitions partly > on the basis of the estimated number of input rows. Since the estimate > was way off, the actual table size got out of hand a bit :-( A bit!! The really worrying bit is that a normal (ish) query also exhibited the same behaviour. I'm a bit worried that if the stats get a bit out of date so that the estimate is off, as in this case, a few backends trying to get this much RAM will see the server grind to a halt. Is this a fixable bug? It seems a fairly high priority, makes the server go away, type bug to me. If you need the test data, I could zip the two tables up and send them somewhere.... Thanks, Gary.