Skip site navigation (1) Skip section navigation (2)

Re: Very long deletion time on a 200 GB database

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Marcin Mańk <marcin(dot)mank(at)gmail(dot)com>
Cc: "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-23 14:25:13
Message-ID: 4F464C49.4050503@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-performance

On 02/23/2012 05:07 AM, Marcin Mańk 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 = 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 
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 
several steps, deleting records older than, say, 90 days, 60 days, 30 days.

cheers

andrew

In response to

Responses

pgsql-performance by date

Next:From: Andy ColsonDate: 2012-02-23 15:18:35
Subject: Re: set autovacuum=off
Previous:From: Claudio FreireDate: 2012-02-23 14:07:21
Subject: Re: Very long deletion time on a 200 GB database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group