From: Reuven M. Lerner <reuven@lerner.co.il>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Marcin Mańk <marcin.mank@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Thu, 23 Feb 2012 17:25:46 +0200
Message-ID: <4F465A7A.4000905@lerner.co.il> (raw)
In-Reply-To: <4F464C49.4050503@dunslane.net>
References: <4F45FB55.1020600@lerner.co.il>
<CAK61fk6bSQtmDUzm2rNv33kN4Oar_dYZ-yFEa9zR_zLL8L9esQ@mail.gmail.com>
<4F464C49.4050503@dunslane.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: reuven@lerner.co.il, andrew@dunslane.net, marcin.mank@gmail.com
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <4F465A7A.4000905@lerner.co.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox