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

Re: [pgsql-performance] Deletes from tables with foreign keys taking too long

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Adam Siegel" <adam(at)sycamorehq(dot)com>,<pgsql-general(at)postgresql(dot)org>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: [pgsql-performance] Deletes from tables with foreign keys taking too long
Date: 2002-10-01 21:44:05
Message-ID: 200210011444.05157.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
Adam,

> I have a table that has about 200 rows in it.  I have 2 other tables that
> have about 300,000 rows each that reference the first table through a
> foriegn key.  I run a process that rebuilds these tables.  First I delete
> the rows in the large tables (takes about 30 seconds), then I delete the
> the rows in the first table (takes about 5 minutes !!!).  Each of these are
> done in separate transactions.

Not that this answers your performance questions, but you will be able to do 
this faster if you use TRUNCATE instead of DELETE.

-- 
Josh Berkus
josh(at)agliodbs(dot)com
Aglio Database Solutions
San Francisco

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-10-01 21:51:29
Subject: Comparitive UPDATE speed
Previous:From: Adam SiegelDate: 2002-10-01 18:28:04
Subject: Deletes from tables with foreign keys taking too long

pgsql-general by date

Next:From: Mark WilsonDate: 2002-10-01 22:27:10
Subject: database session variables
Previous:From: Tom LaneDate: 2002-10-01 21:08:06
Subject: Re: Core Dump

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