From: Richard Huxton <dev@archonet.com>
To: Gary Doades <gpd@gpdnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres backend using huge amounts of ram
Date: Fri, 26 Nov 2004 09:12:15 +0000
Message-ID: <41A6F36F.80805@archonet.com> (raw)
In-Reply-To: <41A6420D.2030307@gpdnet.co.uk>
References: <41A6420D.2030307@gpdnet.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, gpd@gpdnet.co.uk
Subject: Re: Postgres backend using huge amounts of ram
In-Reply-To: <41A6F36F.80805@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox