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