Missing indexes lead to timeouts. Please add.

[AngelBlock]

Please add following index to the database table: transactions, field: receiver_account_id for both testnet and mainnet

Example of the query:

SELECT t.transaction_hash,
       b.block_hash,
       t.block_timestamp,
       t.signer_account_id as payer,
       r.receiver_account_id as payee,
       a.args
FROM transactions t,
     receipts r,
     blocks b,
     transaction_actions a,
     action_receipt_actions ra,
     execution_outcomes e
WHERE t.transaction_hash = r.originated_from_transaction_hash
  AND r.receipt_id = e.receipt_id
  AND b.block_timestamp = r.included_in_block_timestamp
  AND ra.receipt_id = r.receipt_id
  AND ra.action_kind = 'TRANSFER'
  AND t.transaction_hash = a.transaction_hash
  AND a.action_kind = 'FUNCTION_CALL'
  AND e.status = 'SUCCESS_VALUE'
  AND r.predecessor_account_id != 'system'
  AND t.receiver_account_id = 'dev-1631521265288-35171138540673'
  AND b.block_height >= 72465934
LIMIT 20;

endpoint: postgres://public_readonly:nearprotocol@35.184.214.98/testnet_explorer

If block height "AND b.block_height >= 72465934" is close to the latest block: SELECT (select max(block_height) from blocks) - 1e5; query usually returns data within 30 secs.

If we increase requested blocks by, for example: SELECT (select max(block_height) from blocks) - 1e6;

We get an error: ERROR: canceling statement due to statement timeout

[frol]

transactions.receiver_account_id index is already there:

https://github.com/near/near-indexer-for-explorer/blob/master/migrations/2021-01-20-152056_index-transactions-receiver-account-id/up.sql

[AngelBlock]

Can you add to the testnet implementation too, for the constancy between environments, please?

[frol]

@telezhnaya Please, double check that

[telezhnaya]

Sure

We accidentally did not apply 3 migrations on Testnet, which include 5 indexes:

action_receipt_actions_action_kind_idx
execution_outcomes_status_idx
receipts_originated_from_transaction_hash_idx
transactions_actions_action_kind_idx
transactions_receiver_account_id_idx

Added all of them. @AngelBlock thank you for the catch!