Re: Postgres backend using huge amounts of ram

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