X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D7F5D3A4790 for ; Thu, 25 Nov 2004 20:35:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41256-06 for ; Thu, 25 Nov 2004 20:35:14 +0000 (GMT) Received: from server.gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id 9B2503A470D for ; Thu, 25 Nov 2004 20:35:15 +0000 (GMT) Received: from [192.168.1.2] (unknown [192.168.1.2]) by server.gpdnet.co.uk (Postfix) with ESMTP id 0CDEBFBBB7 for ; Thu, 25 Nov 2004 20:35:30 +0000 (GMT) Message-ID: <41A6420D.2030307@gpdnet.co.uk> Date: Thu, 25 Nov 2004 20:35:25 +0000 From: Gary Doades User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Postgres backend using huge amounts of ram Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/401 X-Sequence-Number: 9355 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.