Querying transfers for a specific token doesn't use an index

[ptrus]

On the tokens page, querying all the transfers uses the following API call: https://nexus.oasis.io/v1/sapphire/events?offset=0&limit=10&type=evm.log&evm_log_signature=ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef&rel=0x08Fe02Da45720f754e6FCA338eC1286e860d2d2f

This query doesn't use an index because we don't have an index covering all of the filtered columns. The combined index doesn't include the related accounts, while the related accounts index doesn't include other columns:

nexus/storage/migrations/01_runtimes.up.sql

Lines 172 to 183 in 7170c28

	CREATE INDEX ix_runtime_events_round ON chain.runtime_events(runtime, round); -- for sorting by round, when there are no filters applied
	CREATE INDEX ix_runtime_events_tx_hash ON chain.runtime_events USING hash (tx_hash);
	CREATE INDEX ix_runtime_events_tx_eth_hash ON chain.runtime_events USING hash (tx_eth_hash);
	CREATE INDEX ix_runtime_events_related_accounts ON chain.runtime_events USING gin(related_accounts); -- for fetching account activity for a given account
	CREATE INDEX ix_runtime_events_evm_log_signature ON chain.runtime_events(runtime, evm_log_signature, round); -- for fetching a certain event type, eg Transfers
	CREATE INDEX ix_runtime_events_evm_log_params ON chain.runtime_events USING gin(evm_log_params);
	CREATE INDEX ix_runtime_events_type ON chain.runtime_events (runtime, type);
	CREATE INDEX ix_runtime_events_nft_transfers ON chain.runtime_events (runtime, (body ->> 'address'), (body -> 'topics' ->> 3), round)
	WHERE
	type = 'evm.log' AND
	evm_log_signature = '\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' AND
	jsonb_array_length(body -> 'topics') = 4;

Actually, thinking about it a bit more, the above query is not actually the correct one, as it queries all ERC20Transfer events, where related account is the contract address. This would also include any other token transfers which would be sent to the contract address. The frontend should use the following query instead: https://nexus.oasis.io/v1/sapphire/events?offset=0&limit=10&type=evm.log&evm_log_signature=ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef&contract_address=0x08Fe02Da45720f754e6FCA338eC1286e860d2d2f (noterel replaced with contract_address).

However, the indexing problem remains, we need an index for evm log events, something like:

 CREATE INDEX ix_runtime_events_evm_events ON chain.runtime_events (runtime,  (body ->> 'address'), evm_log_signature, round) 
     WHERE 
         type = 'evm.log'

---

However, on the Accounts page, token-transfers sub-page (e.g. https://explorer.oasis.io/mainnet/sapphire/address/0x1265997E14Eb5e801eA46C360D964203d05C1821/token-transfers) , we also use the events query with same filters: https://nexus.oasis.io/v1/sapphire/events?offset=0&limit=10&type=evm.log&evm_log_signature=ddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef&rel=0x1265997E14Eb5e801eA46C360D964203d05C1821
In this case, using rel is correct, so we should update the indexes to also support this case.

[ptrus]

A similar issue is querying consensus events with "rel=". The gin index (which cannot include the round) is not efficient enough for any address with high number of events, since those then need to be sorted in memory. Maybe this could be improved with some db parameter updates.

[ptrus]

Also previously reported: #722