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 9C9503A4ABB for ; Fri, 26 Nov 2004 19:25:41 +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 20523-06 for ; Fri, 26 Nov 2004 19:25:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8D0F63A4ADB for ; Fri, 26 Nov 2004 19:25:39 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAQJPZc2010677; Fri, 26 Nov 2004 14:25:35 -0500 (EST) To: Gary Doades Cc: pgsql-performance@postgresql.org Subject: Re: Postgres backend using huge amounts of ram In-reply-to: <41A6420D.2030307@gpdnet.co.uk> References: <41A6420D.2030307@gpdnet.co.uk> Comments: In-reply-to Gary Doades message dated "Thu, 25 Nov 2004 20:35:25 +0000" Date: Fri, 26 Nov 2004 14:25:34 -0500 Message-ID: <10676.1101497134@sss.pgh.pa.us> From: Tom Lane 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/409 X-Sequence-Number: 9363 Gary Doades 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