Re: puzzled by deletion performance

"Zhu, Joshua" <jzhu(at)vormetric(dot)com> writes:
> I have the following (hypothetical) tables and their relationships (primary keys are in square brackets):

> [server_id] [device_id] [sensor_id] [property_id]
> SERVER --- 1:n --- DEVICE --- 1:n --- SENSOR --- 1:n --- PROPERTY
> | |
> | m
> | |
> | MAPPING [mapping_id]
> | |
> | n
> | |
> + ----- 1:n --- AGENT [agent_id]

Are those arrows supposed to denote foreign key constraints?

> delete from SENSOR where sensor_id in (select sensor_id from SENSOR where device_id in
> (select device_id from DEVICE where server_id = 1)) -- statement 4

> The first 3 statements completed fairly quickly, however, the statement 4 takes VERY SIGNIFICANTLY longer time to execute, which is puzzling, especially comparing it to statement 3, the latter actually has more records to delete, and the execution plan according to "explain" for practically identical (only that statement 3 with more rows/slightly higher cost).

Nine times out of ten, when someone complains about deletions being lots
slower than updates, the problem is that the deletion is happening in a
table that is referenced by a foreign key constraint, and the referencing
column lacks an index. This forces each row deletion to do a seqscan of
the referencing table to verify that there are no referencing rows.

You generally can't see this problem with plain EXPLAIN, although
EXPLAIN ANALYZE will show a lot of time spent in the FK enforcement
trigger.

regards, tom lane