Thread: Deletes from tables with foreign keys taking too long

On Tue, 1 Oct 2002, Adam Siegel wrote:

> 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?

That seems reasonable.  It's still going to be doing some action on those
tables and it's going to have to scan the tables in some case.  It's wierd
that it's taking that long to do it in any case however, what does the
schema for the tables look like?

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@agliodbs.com
Aglio Database Solutions
San Francisco