From: Dan Langille <dan@langille.org>
To: Giulio Cesare Solaroli <giulio.cesare@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Newbie question about degraded performance on delete statement.
Date: Tue, 02 Oct 2007 18:02:20 -0400
Message-ID: <470287AC.27608.6274E3F7@dan.langille.org> (raw)
In-Reply-To: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
References: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
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
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: dan@langille.org, giulio.cesare@gmail.com
Subject: Re: Newbie question about degraded performance on delete statement.
In-Reply-To: <470287AC.27608.6274E3F7@dan.langille.org>
* 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