pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)  latest in thread

Message-ID: <41A6F36F.80805@archonet.com>
Permalink:  ../41A6F36F.80805@archonet.com/
Also on:    postgresql.org/message-id/41A6F36F.80805@archonet.com

 · 

reply

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