@NamedQueries( { @NamedQuery(name = CallTimeDataKey.QUERY_DELETE_BY_RESOURCES, query = "DELETE CallTimeDataKey ctdk WHERE ctdk.schedule IN ( SELECT ms FROM MeasurementSchedule ms WHERE ms.resource.id IN ( :resourceIds ) )") }) forces the database into full table scans per resource id deleted (actually, only one resource id is passed in, but this does not matter here). Putting an index on rhq_calltime_data_value.key_id speeds up the deletion dramatically (from multiple minutes in my case to several seconds) CREATE INDEX ct_key_index ON rhq_calltime_data_value USING btree (key_id);
This index was never put in place. Still valid, I guess. Maybe not relevant as I thin calltime is moving to cassandra.