[hfm] very low performance of hive.enable_indexes_of_irreversible()
There was an experiment on steem-4 which dumping 56M of blocks to hive_fork_manager. At end of sync function 'hive.enable_indexes_of_irreversible()' last 11h. The function creates indexes and FKs for irreversible blocks tables. It looks like that tables 'hive.transactions' and 'hive.operations' are responsible for the problem.
Size of the whole DB: 1438GB
A table hive.operations contains (for 56M blocks) 2'733'086'127 rows and two indexes:
- hive_operations_block_num_type_trx_in_block_idx 57GB
- pk_hive_operations 57GB
A table hive.transactions contains (for 56M blocks) 1'538'787'405 rows and two indexes:
- hive_transactions_block_num_trx_in_block_idx 32GB
- pk_hive_transactions 97GB