Change hive_account_operations_uq1 index
The current index for AH calls with filtered operations is hive_account_operations_type_account_id_op_seq_idx
which includes the following columns
(op_type_id, account_id, account_op_seq_no DESC) INCLUDE (operation_id, block_num)
In this index, op_type_id filters out 1/93 of the results assuming equal distribution of the data. But in reality this is more like 1/20 or 1/10 depending on the operation. This index is not optimized the AH filtered calls. It is like searching a phone book by the first names. Over time (running in production) postgres decides to not use this index and rather use the other indexes while the query can take up to few minutes.
The index for unfiltered AH calls is hive_account_operations_uq_1
which includes the following columns
(account_id, account_op_seq_no)
If we add op_type_id
to this index, it can handle both filtered and unfiltered calls just fine while maintaining the unique index. I don't see why we can't do it.
Change hive_account_operations_uq_1
index to
(account_id, account_op_seq_no, op_type_id)
Related HAfAH!69 (comment 123859) and #137 (closed)