Re: DELETE FROM takes forever

On Thu, Feb 10, 2011 at 9:57 AM, Josh <slushie(at)gmail(dot)com> wrote:

> Hi
>
> I'm trying to do a DELETE FROM on my large table (about 800 million
> rows) based on the contents of another, moderately large table (about
> 110 million rows). The command I'm using is:
>
> DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records);
>
> This process ran for about two weeks before I decided to stop it -- it
> was dragging down the DB server. I can understand long-running
> processes, but two weeks seems a bit much even for a big table.
>
> Is this the best way to approach the problem? Is there a better way?
>

You need

delete from records r where not exists (select 1 from unique_records ur
where ur.id = r.id);