pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
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 14:25:34 -0500
Message-ID: <10676.1101497134@sss.pgh.pa.us> (raw)
In-Reply-To: <41A6420D.2030307@gpdnet.co.uk>
References: <41A6420D.2030307@gpdnet.co.uk>

Gary Doades <gpd@gpdnet.co.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



view thread (4+ messages)  latest in thread

Message-ID: <10676.1101497134@sss.pgh.pa.us>
Permalink:  ../10676.1101497134@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10676.1101497134@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, gpd@gpdnet.co.uk
  Subject: Re: Postgres backend using huge amounts of ram
  In-Reply-To: <10676.1101497134@sss.pgh.pa.us>

* 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