Re: Very long deletion time on a 200 GB database

On 02/23/2012 12:39 AM, Reuven M. Lerner wrote:
> Hi, everyone...
> This is basically what I'm trying to execute:
>
> 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

I don't recall which versions like which approach, but have you tried
...WHERE EXISTS (SELECT... instead of WHERE IN? Depending on the version
of PostgreSQL, one or the other may yield a superior result.

> (2) I tried to grab the rows that *do* interest me, put them into a
> temporary table, TRUNCATE the existing table, and then copy the rows
> back. I only tested that with a 1 GB subset of the data, but that
> took longer than other options.
>

Was the 1GB subset the part you were keeping or the part you were
deleting? Which part was slow (creating the temp table or copying it back)?

Try running EXPLAIN on the SELECT query that creates the temporary table
and try to optimize that. Also, when copying the data back, you are
probably having to deal with index and foreign keys maintenance. It will
probably be faster to drop those, copy the data back then recreate them.

I know you are a *nix-guy in a Windows org so your options are limited,
but word-on-the-street is that for high-performance production use,
install PostgreSQL on *nix.

Cheers,
Steve