Tom, I've got a similar problem with deletes taking a very long time. I know that there are lots of foreign keys referencing this table, and other foreign keys referencing those tables, etc. I've been curious, is there a way to find out how long the foreign key checks take for each dependent table?
-- Mark Lewis On Thu, 2005-03-24 at 16:52, Tom Lane wrote: > Karim Nassar <[EMAIL PROTECTED]> writes: > > Here is the statement: > > > orfs=# explain analyze DELETE FROM int_sensor_meas_type WHERE > > id_meas_type IN (SELECT * FROM meas_type_ids); > > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------------- > > Hash Join (cost=11.53..42.06 rows=200 width=6) (actual > > time=1.564..2.840 rows=552 loops=1) > > ... > > Total runtime: 2499616.216 ms > > (7 rows) > > Notice that the actual join is taking 2.8 ms. The other ~40 minutes is > in operations that we cannot see in this plan, but we can surmise are ON > DELETE triggers. > > > Where do I go from here? > > Look at what your triggers are doing. My private bet is that you have > unindexed foreign keys referencing this table, and so each deletion > forces a seqscan of some other, evidently very large, table(s). > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match