Create index on field 'method_name' in JSONB args in transaction_actions

[telezhnaya]

Also, check whether we should add other indexes on other fields in args

https://github.com/near/near-indexer-for-explorer/discussions/171

[AngelBlock]

It will be great to also have an index on receiver_account_id table transactions please.

Query example below

SELECT * FROM transactions WHERE block_timestamp >= :block_timestamp AND receiver_account_id LIKE ('%.sputnikdao.near') ORDER BY block_timestamp ASC LIMIT :limit ) as t JOIN receipts r ON t.transaction_hash = r.originated_from_transaction_hash AND r.predecessor_account_id != 'system' JOIN blocks b ON b.block_timestamp = r.included_in_block_timestamp JOIN transaction_actions a ON t.transaction_hash = a.transaction_hash AND a.action_kind = 'FUNCTION_CALL' JOIN action_receipt_actions ra ON ra.receipt_id = r.receipt_id JOIN execution_outcomes e ON r.receipt_id = e.receipt_id AND e.status = 'SUCCESS_VALUE' ORDER BY t.block_timestamp ASC LIMIT :limit`

[frol]

Well, I remembered why we did not add this index. Most of the time it is not what people actually want.

@AngelBlock Are you sure you only want to inspect the transactions and ignore cross-contract calls? Would you like to account for %sputnikdao.near calls that are done by some other contract (e.g. multisig)?

[AngelBlock]

@frol Are you referring to this particular query or what index will be capable of, in general? This query is used for the sputnik DAO v1 which has no cross-contract calls, but I assume index needs to cover any future cases which may be beneficial for Sputnik DAO v2 (or any other smart-contract) and query will be different. I believe @roshkins is working on the v2 stats site, so he can shed some light.

[roshkins]

@AngelBlock I have been doing some work on that. I was trying to query methods that were executed on transactions to quickly count the number of proposals that happened in a timeframe to be able to do pretty plots and calculate deltas in proposal counts.

[frol]

@AngelBlock I am referring to transactions like this one: https://explorer.near.org/transactions/5oDv9vXVtqYCNZMfHWCZUTn7dwY82ZmiUP6owHEyNNw2 (you can find more if you open dao accounts and search for confirm method name or notice that the receiver of the transaction is not dao account)

As you can see, the transaction is issued as:

• from (sender/signer): pinkmole.near
• to (receiver): pinkmole.near
• actions: Function Call with a method name confirm and args {"request_id": 28}

It is a multisig (2FA) contract deployed to pinkmole.near account: https://explorer.near.org/accounts/pinkmole.near, which calls dao account through cross-contract call. Thus, you need to select on actions_receipts/action_receipt_actions instead of transactions/transaction_actions

[AngelBlock]

@frol Understand, you mean with that query as an example we are not catching the 2FA interactions. Thanks, will raise an issue in the repo to address it. Is there an index on action_receipt_actions?

[frol]

Is there an index on action_receipt_actions?

Yes, there is. That was my point, that we added index there and deliberately did not add it for transactions.

[frol]

I believe we don't need that index on transaction tables