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
