Skip site navigation (1) Skip section navigation (2)

Re: Delete query takes exorbitant amount of time

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Karim Nassar <karim(dot)nassar(at)acm(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Delete query takes exorbitant amount of time
Date: 2005-03-25 05:24:39
Message-ID: 200503242124.39558.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Karim NassarDate: 2005-03-25 05:35:55
Subject: Re: Delete query takes exorbitant amount of time
Previous:From: Vivek KheraDate: 2005-03-25 03:56:34
Subject: Re: Delete query takes exorbitant amount of time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group