Big insert/delete memory problems

From: Kurt Overberg <kurt(at)hotdogrecords(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Big insert/delete memory problems
Date: 2003-03-18 18:17:35
Message-ID: 3E7762BF.9090308@hotdogrecords.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all, I have a kinda weird postgres sql question for everyone. When
running the following SQL commands:

delete from xrefmembergroup where membergroupid = 2 ; select 1 as true;

insert into xrefmembergroup (membergroupid, memberid) select 2 as
membergroupid, member.id as memberid from member where ((extract(year
from age(birthdate))) >= '17' ); select 1 as true;

...my memory usage goes nuts- allocates 20-30 Mb per time I run this.
After it finishes these commands, the memory usage does not go back
down, so after awhile, memory usage becomes a problem. I would
estimate that the xrefmembergroup table has about 20,000-30,000 rows in
it, and I'm deleting/inserting about 5000 rows at a time with these
commands, which run VERY fast.

Here's the table definition:

Column | Type |
Modifiers
---------------+--------------------------+------------------------------------------------------------
id | integer | not null default
nextval('"xrefmembergroup_id_seq"'::text)
membergroupid | integer | not null default 0
memberid | integer | not null default 0
timestamp | timestamp with time zone | default
"timestamp"('now'::text)
Indexes: xrefmembergroup_pkey primary key btree (id),
membergroupid_xrefmembergroup_key btree (membergroupid),
memberid_xrefmembergroup_key btree (memberid)

Is this an excessive delete/insert? Am I breaking 'good form' here?
Thanks for any help!

/kurt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message scott.marlowe 2003-03-18 18:20:10 Re: The folding of unquoted names to lower case in PostgreSQL
Previous Message Tom Lane 2003-03-18 17:19:32 Re: Can I write Large Objects in parallel transactions?