Re: [PERFORM] Very long deletion time on a 200 GB database

[Andrew Dunstan]

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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance