To: Mark Lewis <mark.lewis@mir3.com>
Cc: Brian Choate <brianc@nimblefish.com>,
	pgsql-performance@postgresql.org
Subject: Re: Poor performance of delete by primary key 
In-reply-to: <1126026278.3310.76.camel@archimedes> 
References: 
 <2F9239F5E316C54688FDA9814FF045D6EADACC@EXCHANGE.sf.nimblefish.com>
	<18162.1126020720@sss.pgh.pa.us>
	<1126026278.3310.76.camel@archimedes>
Comments: In-reply-to Mark Lewis <mark.lewis@mir3.com>
	message dated "Tue, 06 Sep 2005 10:04:38 -0700"
Date: Tue, 06 Sep 2005 13:42:59 -0400
Message-ID: <19191.1126028579@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Lewis <mark.lewis@mir3.com> writes:
> I had a similar problem, so I downloaded 8.1 from CVS, ran it on a
> relatively gnarly dev workstation, imported a dump of my 8.0 database,
> and ran my troublesome queries with the new EXPLAIN ANALYZE.

> This process took about an hour and worked great, provided that you've
> actually named your foreign key constraints.  Otherwise, you'll find out
> that there's a trigger for a constraint called $3 that's taking up all
> of your time, but you won't know what table that constraint is on.

But at least you've got something you can work with.  Once you know the
name of the problem trigger you can look in pg_trigger to see which
other table it's connected to.  Try something like

	select tgname, tgconstrrelid::regclass, tgargs from pg_trigger
	where tgrelid = 'mytable'::regclass;

			regards, tom lane