Message-ID: <4F465A7A.4000905@lerner.co.il>
Date: Thu, 23 Feb 2012 17:25:46 +0200
From: "Reuven M. Lerner" <reuven@lerner.co.il>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: Andrew Dunstan <andrew@dunslane.net>
CC: =?UTF-8?B?TWFyY2luIE1hxYRr?= <marcin.mank@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
	<CAK61fk6bSQtmDUzm2rNv33kN4Oar_dYZ-yFEa9zR_zLL8L9esQ@mail.gmail.com>
	<4F464C49.4050503@dunslane.net>
In-Reply-To: <4F464C49.4050503@dunslane.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi, everyone.  Thanks for all of the help and suggestions so far; I'll 
try to respond to some of them soon.  Andrew wrote:

>> How about:
>>
>> DELETE FROM B
>> WHERE r_id IN (SELECT distinct R.id
>> FROM R WHERE r.end_date< (NOW() - (interval '1 day' * 30))
>>
>> ?
>>
>
> Or possibly without the DISTINCT. But I agree that the original query
> shouldn't have B in the subquery - that alone could well make it crawl.

I put B in the subquery so as to reduce the number of rows that would be 
returned, but maybe that was indeed backfiring on me.  Now that I think 
about it, B is a huge table, and R is a less-huge one, so including B in 
the subselect was probably a mistake.

>
> What is the distribution of end_dates? It might be worth running this in
> several steps, deleting records older than, say, 90 days, 60 days, 30 days.

I've suggested something similar, but was told that we have limited time 
to execute the DELETE, and that doing it in stages might not be possible.

Reuven


-- 
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner