Re: Newbie question about degraded performance on delete statement.

From: "Dan Langille" <dan(at)langille(dot)org>
To: "Giulio Cesare Solaroli" <giulio(dot)cesare(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Newbie question about degraded performance on delete statement.
Date: 2007-10-02 22:02:20
Message-ID: 470287AC.27608.6274E3F7@dan.langille.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Decibel! 2007-10-02 22:17:53 Re: Linux mis-reporting memory
Previous Message Giulio Cesare Solaroli 2007-10-02 21:55:27 Newbie question about degraded performance on delete statement.