"Zhu, Joshua" <j...@vormetric.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
