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

Deletes from tables with foreign keys taking too long

From: "Adam Siegel" <adam(at)sycamorehq(dot)com>
To: <pgsql-general(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Deletes from tables with foreign keys taking too long
Date: 2002-10-01 18:28:04
Message-ID: 00ca01c26978$490ee4a0$1537140a@orbital.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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.

If I do a vacuum analyze on each of the large tables just after the delete then deleting the rows from the first table takes just a second or two.  My guess is that postgres is still check the foriegn keys from the first table to the others even though the records are deleted in the larger tables.  The vacuum cleans up the deleted records, so it goes faster.  Am I wrong.  Any ideas?

Regards,
Adam


Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2002-10-01 21:44:05
Subject: Re: [pgsql-performance] Deletes from tables with foreign keys taking too long
Previous:From: Stephan SzaboDate: 2002-10-01 18:27:56
Subject: Re: [pgsql-performance] Deletes from tables with foreign keys taking

pgsql-general by date

Next:From: snpeDate: 2002-10-01 18:32:45
Subject: Re: count() for a select statement?
Previous:From: Stephan SzaboDate: 2002-10-01 18:27:56
Subject: Re: [pgsql-performance] Deletes from tables with foreign keys taking

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