Message-ID: <4702C8B7.5020701@digitalglobe.com>
Date: Tue, 02 Oct 2007 15:39:51 -0700
From: Greg Williamson <Gregory.Williamson@digitalglobe.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: Giulio Cesare Solaroli <giulio.cesare@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Newbie question about degraded performance on delete
 statement.
References: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
In-Reply-To: <ff737ac30710021455t41b6a753l4ce876f04255100d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Giulio Cesare Solaroli wrote:
> Hello everybody,
>
> I have just joined the list, as I am experiencing a degradation on
> performances on my PostgreSQL instance, and I was looking for some
> insights on how to fix/avoid it.
>
> 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.
>   
Make sure you run ANALYZE on the table in question after changes to make 
sure the stats are up to date.
> 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.
>
>   
As another poster indicated, this sounds like foreign constraints where 
the postmaster process has to make sure there are no child references in 
dependent tables; if you are lacking proper indexing on those tables a 
sequential scan would be involved.

Posting the DDL for the table in question and anything that might refer 
to it with an FK relationship would help the list help you.

Try running the query with EXPLAIN ANALYZE ... to see what the planner 
says. Put this in a transaction and roll it back if you want to leave 
the data unchanged, e.g.
BEGIN;
EXPLAIN ANALYZE DELETE FROM foo WHERE pk = 1234;  -- or whatever values 
you'd be using
ROLLBACK;

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, 
is for the sole use of the intended recipient(s) and may contain 
confidential and privileged information and must be protected in 
accordance with those provisions. Any unauthorized review, use, 
disclosure or distribution is prohibited. If you are not the intended 
recipient, please contact the sender by reply e-mail and destroy all 
copies of the original message.

(My corporate masters made me say this.)