[GENERAL] puzzled by deletion performance - Mailing list pgsql-general

From Zhu, Joshua
Subject [GENERAL] puzzled by deletion performance
Date
Msg-id d4b96945fd884d40b1504ba324c178c8@EXUSDAGORL01.INTERNAL.ROOT.TES
Whole thread Raw
Responses Re: [GENERAL] puzzled by deletion performance
List pgsql-general

 

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

 

pgsql-general by date:

Previous
From: Jeff Janes
Date:
Subject: Re: [GENERAL] Monitoring of a hot standby with a largely idle master
Next
From: Tom Lane
Date:
Subject: Re: [GENERAL] puzzled by deletion performance