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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-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

Browse pgsql-general by date

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

Browse pgsql-performance by date

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