Timeout while pulling recent transactions

[frol]

After the recent transactions number bump on mainnet and upgrading the hardware spec of our database instances I see that some of the account cannot get their transactions list anymore:

https://explorer.near.org/accounts/nft.nearapps.near

https://explorer.near.org/accounts/coin-op.near

I have already seen such problems before when giving more RAM to Postgres instance only made it worse. I fear there is nothing we can do there at the moment, and we will need to move away from the current "recent transactions" to "recent activity" faster as it is in fact simpler query and easier to reason about for the end-user as well:

https://near-explorer-frontend-with-indexer-pr-umte.onrender.com/accounts/nft.nearapps.near

@shelegdmitriy Can you extract the exact request from the Explorer backend, so @telezhnaya and I can profile it?

[shelegdmitriy]

Here is this request https://github.com/near/near-explorer/blob/df2f616bfa2190c6b115b5fe19a95d0c81c1c57a/backend/src/db-utils.js#L729-L761
@frol @telezhnaya

[telezhnaya]

I checked the plan: it looks good enough. We can't speed it up just by adding indexes.
TLDR: I don't see the fast way how to fix it, but I have few ideas how we can improve it.
BTW, I don't see any usages of the corresponding wamp endpoint https://github.com/near/near-explorer/blob/df2f616bfa2190c6b115b5fe19a95d0c81c1c57a/backend/src/wamp.js#L368
@shelegdmitriy could you please check that?

---

I tested, what if we add the condition "please take only fresh lines, for the last week/month". I thought it could cut the sample -> we may receive the boost in speed. No, it still works too slow.
Though, if we take the window by only one day

...
WHERE account_changes.affected_account_id = 'nft.nearapps.near'
AND account_changes.changed_in_block_timestamp < 1643122049000000000
AND account_changes.changed_in_block_timestamp >= 1643035649000000000
...

It works 20 seconds on 'nft.nearapps.near'.
If we cut off LIMIT 100, it works like charm, much faster (2-3 seconds), but the resulting sample could be too big to pass it to the frontend.

The problem here is the correct pagination.
We still need to think how to show the next transactions, I'm not sure I can imagine the simple, fast, and non-buggy solution for that.

---

We can also re-think the query and ask for 3 separate things: details from transactions, receipts, account_changes.
@frol mentioned that we have a plan to rewrite this component, so it's better to wait with this solution a bit.

[shelegdmitriy]

BTW, I don't see any usages of the corresponding wamp endpoint https://github.com/near/near-explorer/blob/df2f616bfa2190c6b115b5fe19a95d0c81c1c57a/backend/src/wamp.js#L368

@telezhnaya you're right. That's because we use it as a demo here to see if we have all the data we needed according to new design. And that PR is a draft one but still you could take a look how we use account-activity method there.

[frol]

@telezhnaya @shelegdmitriy Thanks for pulling these bits together! Let's keep this issue open, but overall, we should just implement the new Activity-based Account Details page, and that will address this issue naturally.