From: Steve Crawford <scrawford@pinpointresearch.com>
To: Reuven M. Lerner <reuven@lerner.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Thu, 23 Feb 2012 07:47:36 -0800
Message-ID: <4F465F98.6090706@pinpointresearch.com> (raw)
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
References: <4F45FB55.1020600@lerner.co.il>
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
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: scrawford@pinpointresearch.com, reuven@lerner.co.il
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <4F465F98.6090706@pinpointresearch.com>
* 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