Add a new DB index for AGS Destroy action
During a customer investigation, we found one of the queries used by the destroy action in the retention schedule was running very slowly, taking around 50 minutes, to process each record.
Debug logging has big gaps between each node being processed.
Thread dumps showed the thread DefaultScheduler_Worker-5 waiting in socketRead0 against the database for this class
The SQL query we’re waiting on is
Their DBA collected a query plan showing the query was doing an index scan on the alf_node_properties table holding 600m rows.
This matches what I’m seeing in my test environment with 1.4 million rows
Their observation is that our TMDQ index ‘idx_alf_nprop_l' can’t be used here because its leftmost column is qname_id and not long_value.
After creating an index on alf_node_properties.long_value, I get the following query plan
The left outer join on (p.long_value = cd.id) reads 12 rows using the new index, instead of 1444415 with the full index scan.
It seems unlikely that we could or should alter the TMDQ index, so should we look at a new RM/AGS index to support the destroy query?
feedback from the customer is that after rebuilding the indexes on the affected tables, they did not actually create this index and performance was still acceptable.