To: "Heikki Linnakangas" <heikki@enterprisedb.com>
Cc: "Galantucci Giovanni" <Giovanni.Galantucci@italtel.it>,
	<pgsql-performance@postgresql.org>
Subject: Re: DELETE queries slow down
In-Reply-To: <46EE4676.8040406@enterprisedb.com> (Heikki Linnakangas's message
	of "Mon\, 17 Sep 2007 10\:18\:46 +0100")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <AF4EB29748E178458A0067661112724B0FCF74@BESONE.corp.dom>
	<46EE4676.8040406@enterprisedb.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 17 Sep 2007 11:21:44 +0100
Message-ID: <873axdpp6v.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Heikki Linnakangas" <heikki@enterprisedb.com> writes:

> Galantucci Giovanni wrote:
>
>> For 1 or 2 hours we update only one table, and everything goes ok, where
>> DELETE last at most 6 or 7 seconds.
>> 
>> Then for a minute we do INSERT on both table, and everything continue
>> going ok, with DELETE that last about 10 seconds.
>> 
>> From that moment on, DELETES become timeless, and last for 240 and more
>> seconds! 

What do the inserts and deletes actually look like? Are there subqueries or
joins or are they just inserting values and deleting simple where clauses?

And are these in autocommit mode or are you running multiple commands in a
single transaction?

Generally it's faster to run more commands in a single transaction but what
I'm worried about is that you may have a transaction open which you aren't
committing for a long time. This can stop vacuum from being able to clean up
dead space and if it's in the middle of a query can actually cause vacuum to
get stuck waiting for the query to finish using the page it's using.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com