SQL performance & flexibility

[4miners]

Belongs to: #449

• SQL performance & flexibility
  • Review queries and test their performance, find slow queries/views
    • Improve performance for existing queries/view (if needed)
  • Review data types performance & consistency

[4miners]

Rewrite getIdSequence - improve sync speed, reduce CPU usage.

• Before: Execution time: 4066.086 ms
• After: Execution time: ~0.2 ms

Indexes on mem_accounts:

• UPPER("address") - improve Account.prototype.getAll performance.
  Before: Execution time: 16.493 ms
  After: Execution time: ~0.2 ms

• ("address") - improve getting by address performance.

• ("blockId") - improve performance when joining.

• ("isDelegate"), ("vote" DESC, ENCODE("publicKey", 'hex') ASC) WHERE "isDelegate" = 1 - improve performance of getting delegates.
  For active 101:
  Before: Execution time: 9.471 ms
  After: Execution time: ~0.3 ms

Index on blocks:

• ((CEIL(height / 101::float)::int)) - improve performance when deling with rounds.

[4miners]

Rewrite updateMemAccounts - faster checking integration of mem_accounts on node start.

• Before: Execution time: 2579.080 ms (28672 rows)
• After: Execution time: 14.407 ms

[4miners]

Created new table rounds_fees for storing fees, modified in real time by triggers - faster aggregateBlocksReward query (get rewards for time period), flexibility for future features.
For example - Calculating rewards for top forging delegate, whole timeframe:

• Before: Execution time: 2584.124 ms
• After: Execution time: 116.724 ms

[4miners]

Recreated trs_list view and dropped needless ordering by NULLS LAST - faster dealing with transactions.
For example - get last 1000 transactions:

• Before: Execution time: 2732.536 ms
• After: Execution time: 12.890 ms