From: "Dan Langille" <dan@langille.org>
To: "Giulio Cesare Solaroli" <giulio.cesare@gmail.com>
Date: Tue, 02 Oct 2007 18:02:20 -0400
MIME-Version: 1.0
Subject: Re: Newbie question about degraded performance on delete statement.
CC: pgsql-performance@postgresql.org
Message-ID: <470287AC.27608.6274E3F7@dan.langille.org>
In-reply-to: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
References: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

On 2 Oct 2007 at 23:55, Giulio Cesare Solaroli wrote:

> What I have observed are impossibly high time on delete statements on
> some tables.
> 
> The delete statement is very simple:
> delete from table where pk = ?
> 
> The explain query report a single index scan on the primary key index,
> as expected.
> 
> I have run vacuum using the pgAdmin tool, but to no avail.
> 
> I have also dropped and recreated the indexes, again without any benefit.
> 
> I have later created a copy of the table using the "create table
> table_copy as select * from table" syntax.
> 
> Matching the configuration of the original table also on the copy
> (indexes and constraints), I was able to delete the raws from the new
> table with regular performances, from 20 to 100 times faster than
> deleting from the original table.

There may be more to that original table.  What about triggers?  
rules?  Perhaps there other things going on in the background.

-- 
Dan Langille - http://www.langille.org/
Available for hire: http://www.freebsddiary.org/dan_langille.php