From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Karim Nassar <karim.nassar@acm.org>
Subject: Re: Delete query takes exorbitant amount of time
Date: Thu, 24 Mar 2005 21:24:39 -0800
User-Agent: KMail/1.6.2
Cc: pgsql-performance@postgresql.org
References: <1111709457.9085.127.camel@k2.cet.nau.edu>
	<2630.1111711927@sss.pgh.pa.us>
	<1111715304.9089.179.camel@k2.cet.nau.edu>
In-Reply-To: <1111715304.9089.179.camel@k2.cet.nau.edu>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <200503242124.39558.josh@agliodbs.com>

Karim,

> Problem now is: this referencing table I expect to grow to about 110
> million rows in the next 2 months, then by 4 million rows per month
> thereafter. I expect that the time for recreating the foreign key will
> grow linearly with size.
>
> Is this just the kind of thing I need to watch out for? Any other
> suggestions for dealing with tables of this size? What can I do to my
> indexes to make them mo' betta?

How about getting some decent disk support?  A single 10K SCSI disk is a bit 
sub-par for a database with 100's of millions of records.  Too bad you didn't 
get a v40z ...

Beyond that, you'll want to do the same thing whenever you purge the 
referencing table; drop keys, delete, re-create keys.  Or think about why it 
is you need to delete batches of records from this FKed table at all.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco