Skip to content

Optimalization of hafbe_app_c

Michal Zander requested to merge mzander/post-query-optimalization into mzander/search-endpoints

Origin query was borrowed from reputation_tracker (https://gitlab.syncad.com/hive/reputation_tracker/-/blob/develop/db/calculate_account_reputations.sql?ref_type=heads), query works fine on most block ranges but there are occasions when the query slows down to even 216sec (block range 45940001-45950000).

block_num btracker_app_a btracker_app_b hafbe_app_a hafbe_app_b hafbe_app_c state_provider
45940001     2.326	   4.209	   0.001	0.030	  216.536	0.024

After my change the timings of the borderline cases are resolved and the sync in the hafbe_app_c doesn't slow down anymore (sync time accelerated by 2,5h, 1651m to 1505m), the issue is caused by nested loop inside the subquery (AND NOT EXISTS).

block_num btracker_app_a btracker_app_b hafbe_app_a hafbe_app_b hafbe_app_c state_provider
45940001   2.252	   4.237	   0.001	0.030	   2.998	0.004

the changes in hafbe might be reused in reputation tracker code in case if the sync slows down similarly

Edited by Michal Zander

Merge request reports