Message-ID: <4F464C49.4050503@dunslane.net>
Date: Thu, 23 Feb 2012 09:25:13 -0500
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0) Gecko/20120131 Thunderbird/10.0
MIME-Version: 1.0
To: =?UTF-8?B?TWFyY2luIE1hxYRr?= <marcin.mank@gmail.com>
CC: "Reuven M. Lerner" <reuven@lerner.co.il>, 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>
In-Reply-To: 
 <CAK61fk6bSQtmDUzm2rNv33kN4Oar_dYZ-yFEa9zR_zLL8L9esQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable


On 02/23/2012 05:07 AM, Marcin Ma=C5=84k wrote:
>> DELETE FROM  B
>> WHERE r_id IN (SELECT R.id
>>      FROM R, B
>>     WHERE r.end_date<  (NOW() - (interval '1 day' * 30))
>>       AND r.id =3D b.r_id
>>
> 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=20
shouldn't have B in the subquery - that alone could well make it crawl.

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

cheers

andrew