Postgres backend using huge amounts of ram

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!

The two tables are:

create table reqt_dates
(
reqt_date_id serial,
reqt_id integer not null,
reqt_date date not null,
primary key (reqt_date_id)
) without oids;

and

create table booking_plan
(
booking_plan_id serial,
reqt_date_id integer not null,
booking_id integer not null,
booking_date date not null,
datetime_from timestamp not null,
datetime_to timestamp not null,
primary key (booking_plan_id)
) without oids;

and I was was trying to do:

alter table booking_plan add
foreign key
(
reqt_date_id
) references reqt_dates (
reqt_date_id
) on delete cascade;

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:

QUERY PLAN
Aggregate (cost=37.00..37.00 rows=1 width=0) (actual
time=123968.000..123968.000 rows=1 loops=1)
-> Hash Join (cost=15.50..36.50 rows=1000 width=0) (actual
time=10205.000..120683.000 rows=1657709 loops=1)
Hash Cond: ("outer".reqt_date_id = "inner".reqt_date_id)
-> Seq Scan on booking_plan (cost=0.00..15.00 rows=1000
width=4) (actual time=10.000..4264.000 rows=1657709 loops=1)
-> Hash (cost=15.00..15.00 rows=1000 width=4) (actual
time=10195.000..10195.000 rows=0 loops=1)
-> Seq Scan on reqt_dates (cost=0.00..15.00 rows=1000
width=4) (actual time=0.000..6607.000 rows=2142184 loops=1)
Total runtime: 124068.000 ms

I then analysed the database. Note, there are no indexes at this stage
except the primary keys.

the same query then gave:
QUERY PLAN
Aggregate (cost=107213.17..107213.17 rows=1 width=0) (actual
time=57002.000..57002.000 rows=1 loops=1)
-> Hash Join (cost=35887.01..106384.32 rows=1657709 width=0)
(actual time=9774.000..54046.000 rows=1657709 loops=1)
Hash Cond: ("outer".reqt_date_id = "inner".reqt_date_id)
-> Seq Scan on booking_plan (cost=0.00..22103.55 rows=1657709
width=4) (actual time=10.000..19648.000 rows=1657709 loops=1)
-> Hash (cost=24355.92..24355.92 rows=2142184 width=4)
(actual time=9674.000..9674.000 rows=0 loops=1)
-> Seq Scan on reqt_dates (cost=0.00..24355.92
rows=2142184 width=4) (actual time=0.000..4699.000 rows=2142184 loops=1)
Total runtime: 57002.000 ms

This is the same set of hash joins, BUT the backend only used 30M of
private RAM.

Platform is Windows XP, Postgres 8.0 beta 5

shared_buffers = 4000
work_mem = 8192

Any explanations?

Thanks,
Gary.

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

Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> writes:
> 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!

> Since I can't get an explain of what the alter table was doing I used this:

[ looks in code... ] The test query for an ALTER ADD FOREIGN KEY looks
like

SELECT fk.keycols FROM ONLY relname fk
LEFT OUTER JOIN ONLY pkrelname pk
ON (pk.pkkeycol1=fk.keycol1 [AND ...])
WHERE pk.pkkeycol1 IS NULL AND
(fk.keycol1 IS NOT NULL [AND ...])

It's also worth noting that work_mem is temporarily set to
maintenance_work_mem, which you didn't tell us the value of:

/*
* Temporarily increase work_mem so that the check query can be
* executed more efficiently. It seems okay to do this because the
* query is simple enough to not use a multiple of work_mem, and one
* typically would not have many large foreign-key validations
* happening concurrently. So this seems to meet the criteria for
* being considered a "maintenance" operation, and accordingly we use
* maintenance_work_mem.
*/

> I then analysed the database. ...
> This is the same set of hash joins, BUT the backend only used 30M of
> private RAM.

My recollection is that hash join chooses hash table partitions partly
on the basis of the estimated number of input rows. Since the estimate
was way off, the actual table size got out of hand a bit :-(

regards, tom lane

Tom Lane wrote:
>
> It's also worth noting that work_mem is temporarily set to
> maintenance_work_mem, which you didn't tell us the value of:
>
It's left at the default. (16384).

This would be OK if that is all it used for this type of thing.

>
>
> My recollection is that hash join chooses hash table partitions partly
> on the basis of the estimated number of input rows. Since the estimate
> was way off, the actual table size got out of hand a bit :-(

A bit!!

The really worrying bit is that a normal (ish) query also exhibited the
same behaviour. I'm a bit worried that if the stats get a bit out of
date so that the estimate is off, as in this case, a few backends trying
to get this much RAM will see the server grind to a halt.

Is this a fixable bug? It seems a fairly high priority, makes the server
go away, type bug to me.

If you need the test data, I could zip the two tables up and send them
somewhere....

Thanks,
Gary.