You do not have indexes on all of the columns which are linked by foreign key constraints. For example, let's say that I had a "scientist" table with a single column "scientist_name" and another table "discovery" which had "scientist_name" as a column with a foreign key constraint to the "scientist" table. If the system were to try to delete a row from the scientist table, then it would need to scan the discovery table for any row which referenced that scientist_name. If there is an index on the scientist_name column in the discovery table, this is a fast operation. In your case however, there most likely isn't an index on that column, so it needs to do a full table scan of the discovery table for each row deleted from the scientist table. If the discovery table has 100,000 rows, and there are 100 scientists, then deleting those 100 scientists would require scanning 100,000 * 100 = 10M records, so this sort of thing can quickly become a very expensive operation. Because of this potential for truly atrocious update/delete behavior, some database systems (SQL Server at least, and IIRC Oracle as well) either automatically create the index on discovery.scientist_name when the foreign key constraint is created, or refuse to create the foreign key constraint if there isn't already an index. PG doesn't force you to have an index, which can be desirable for performance reasons in some situations if you know what you're doing, but allows you to royally shoot yourself in the foot on deletes/updates to the parent table if you're not careful. If you have a lot of constraints and want to track down which one is unindexed, then doing an EXPLAIN ANALYZE of deleting a single row from the parent table will tell you how long each of the referential integrity checks takes, so you can figure out which indexes are missing. -- Mark Lewis On Tue, 2006-09-19 at 15:22 +0200, Ivan Voras wrote: > I've just fired off a "DELETE FROM table" command (i.e. unfiltered > DELETE) on a trivially small table but with many foreign key references > (on similar-sized tables), and I'm waiting for it to finish. It's been > 10 minutes now, which seems very excessive for a table of 9000 rows on a > 3 GHz desktop machine. > > 'top' says it's all spent in USER time, and there's a ~~500KB/s write > rate going on. Just before this DELETE, I've deleted data from a larger > table (50000 rows) using the same method and it finished in couple of > seconds - maybe it's a PostgreSQL bug? > > My question is: assuming it's not a bug, how to optimize DELETEs? > Increasing work_mem maybe? > > (I'm using PostgreSQL 8.1.4 on FreeBSD 6- amd64) > > (I know about TRUNCATE; I need those foreign key references to cascade) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
