Message-ID: <4F465F98.6090706@pinpointresearch.com>
Date: Thu, 23 Feb 2012 07:47:36 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.26) Gecko/20120131 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: "Reuven M. Lerner" <reuven@lerner.co.il>
CC: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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