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 514C43A44BC for ; Fri, 26 Nov 2004 09:12:26 +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 70108-02 for ; Fri, 26 Nov 2004 09:12:18 +0000 (GMT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 4CBA83A4336 for ; Fri, 26 Nov 2004 09:12:19 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 4.42) id 1CXc9L-000AY5-5T; Fri, 26 Nov 2004 09:12:20 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 5D695163F8; Fri, 26 Nov 2004 09:12:16 +0000 (GMT) Message-ID: <41A6F36F.80805@archonet.com> Date: Fri, 26 Nov 2004 09:12:15 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gary Doades Cc: pgsql-performance@postgresql.org Subject: Re: Postgres backend using huge amounts of ram References: <41A6420D.2030307@gpdnet.co.uk> In-Reply-To: <41A6420D.2030307@gpdnet.co.uk> 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/405 X-Sequence-Number: 9359 Gary Doades wrote: > How much RAM can a single postgres backend use? > > I've just loaded a moderately sized dataset into postgres and was > applying RI constraints to the tables (using pgadmin on windows). Part > way though I noticed the (single) postgres backend had shot up to using > 300+ MB of my RAM! Oops - guess that's why they call it a Beta. My first guess was a queue of pending foreign-key checks or triggers etc. but then you go on to say... > Since I can't get an explain of what the alter table was doing I used this: > > select count(*) from booking_plan,reqt_dates where > booking_plan.reqt_date_id = reqt_dates.reqt_date_id > > and sure enough this query caused the backend to use 300M RAM. The plan > for this was: [snip] > I then analysed the database. Note, there are no indexes at this stage > except the primary keys. > > the same query then gave: [snip] > This is the same set of hash joins, BUT the backend only used 30M of > private RAM. I'm guessing in the first case that the default estimate of 1000 rows in a table means PG chooses to do the join in RAM. Once it knows there are a lot of rows it can tell not to do so. However, I thought PG was supposed to spill to disk when the memory required exceeded config-file limits. If you could reproduce a simple test case I'm sure someone would be interested in squashing this bug. -- Richard Huxton Archonet Ltd