Thread: [GENERAL] puzzled by deletion performance
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]
They have the following record counts:
SERVER: 10
DEVICE: 10000 for each server
SENSOR: 120000 for devices on each server
AGENT: 150000 for devices on each server
PROPERTY: 440000 for sensors on each server
MAPPING: 450000 for sensors and agents on each server
When there is a need to delete all records belonging to a server (let’s say of server_id 1), the following SQL statements are executed (in that order, each with its own transaction):
delete from MAPPING where mapping_id in (select mapping_id from MAPPING where sensor_id in
(select sensor_id from SERSOR where device_id in (select device_id from DEVICE where server_id = 1))) –- statement 1
delete from PROPERTY where property_id in (select property_id from PROPERTY where sensor_id in
(select sensor_id from SENSOR where device_id in (select device_id from DEVICE where server_id = 1))) –- statement 2
delete from AGENT where agent_id in (select agent_id from AGENT where device_id in
(select distinct device_id from DEVICE where server_id = 1))) –- statement 3
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
delete from DEVICE where device_id in (select device_id from DEVICE where server_id = 1) –- statement 5
delete from SERVER where server_id = 1 -- statement 6
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).
Anyone can shed some light on this behavior, or suggestions on how statement 4 can be rewritten, with better performance (there is already an index in PROPERTY table on its foreign key sensor_id)?
Thanks
"Zhu, Joshua" <jzhu@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
theexecution plan according to "explain" for practically identical (only that statement 3 with more rows/slightly
highercost).
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