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:
I then analysed the database. Note, there are no indexes at this stage
except the primary keys.

the same query then gave:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to