Improve Top Accounts listing performance

[gfreh]

Description

Loading the Top Accounts list is very slow.

Also, after the page is loaded, the count of transactions per account seems to be wrong.

Steps to Reproduce

Visit: https://blockscout.com/eth/mainnet/accounts

During the standup meeting (11/06/18) we commonly agreed that some changes were needed to enable this query (list_top_addresses) on the mainnet. In this agreement, we decided to simplify the query by removing the transaction count from it and also adding a new index to improve its performance.

The previous test result using the mainnet can be consulted in this PR: #1040

Acceptance Criteria

1. Be able to execute the query on the mainnet database as well;
2. Be able to load the Top Accounts listing page again without returning a Gateway Timeout error;

[mkosowsk]

From @acravenho

The accounts page loads very slow. This page is currently disabled and we would like to enable the page. We are currently increasing the speed for the count transaction query which should speed up the page some. Please try to avoid any modifications which would require a db reset.

Best of luck, bounty hunters!

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[rmshea]

Hey @WiechersV, are you still working on this issue? Let me know 😄

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[gitcoinbot]

@WiechersV Hello from Gitcoin Core - are you still working on this issue? Please submit a WIP PR or comment back within the next 3 days or you will be removed from this ticket and it will be returned to an ‘Open’ status. Please let us know if you have questions!

• warning (3 days)
• escalation to mods (6 days)

Funders only: Snooze warnings for 1 day | 3 days | 5 days | 10 days | 100 days

[rmshea]

Hey @WiechersV, going to open this back up to the rest of the community!

[svenski123]

I've submitted #1040 which reduces the accounts page rendering time from minutes to a second or so.

Despite the improved DB querying, htting the transactions table just to get the highest nonce will get more expensive as the table grows. I would suggest adding a nonce column to the addresses table and updating that when transactions are imported. I note the addresses table already has values effectively cacheing transaction state (i.e. fetched_coin_balance, etc.).

[gitcoinbot]

Issue Status: 1. Open 2. Started 3. Submitted 4. Done

---

Work for 75.0 DAI (75.0 USD @ $1.0/DAI) has been submitted by:

1. @svenski123

@mkosowsk please take a look at the submitted work:

• PR by @svenski123

---

• Learn more on the Gitcoin Issue Details page
• Want to chip in? Add your own contribution here.
• Questions? Checkout Gitcoin Help or the Gitcoin Slack
• $59,805.51 more funded OSS Work available on the Gitcoin Issue Explorer

[vs77bb]

@mkosowsk looks like this one may be good for payout 👍

[svenski123]

I've marked myself as stop work on this as the PR branch is done and has been merged into the project.
Please kindly pay out the bounty.
@mkosowsk

[mkosowsk]

@svenski123 paid out! Thanks for your contribution :)

[gitcoinbot]

Issue Status: 1. Open 2. Started 3. Submitted 4. Done

---

The funding of 75.0 DAI (75.0 USD @ $1.0/DAI) attached to this issue has been approved & issued to @svenski123.

• Learn more on the Gitcoin Issue Details page
• Questions? Checkout Gitcoin Help or the Gitcoin Slack
• $52,993.46 more funded OSS Work available on the Gitcoin Issue Explorer

[amandasposito]

Closed via #1140