To: postgres@leangen.net, pgsql-performance@postgresql.org
Subject: Re: Delete operation VERY slow...
References: <1150441126.4812.23.camel@sonoda.bioscene.co.jp>
	<674d1f8a0606160022p1b042676gba28553bac5ab5ea@mail.gmail.com>
	<1150445542.4812.34.camel@sonoda.bioscene.co.jp>
Message-ID: <op.ta8kk1aqcigqcu@apollo13>
Date: Fri, 16 Jun 2006 13:23:15 +0200
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <1150445542.4812.34.camel@sonoda.bioscene.co.jp>
User-Agent: Opera M2/8.54 (Linux, build 1745)


> Wow! That was almost instantaneous. I can't believe the difference.
>
> The only inconvenience is that I need to remove all the foreign key
> constraints before truncating, then put them back after. But I suppose
> it is a small price to pay for this incredible optimization.

	In that case, your DELETE might have been slowed down by foreign key  
checks.

	Suppose you have tables A and B, and table A has a column "b_id  
REFERENCES B(id)"
	When you delete from B postgres has to lookup in A which rows reference  
the deleted rows in order to do the ON DELETE action you specified in the  
constraint.
	If you do not have an index on b_id, this can be quite slow... so you  
should check if your foreign key relations that need indexes have them.