Fix performance on view graphql_account_rewards

[ghost]

Purpose

See task for full discussion: https://concordium.atlassian.net/browse/CD-947 Initial task: https://github.com/Concordium/concordium-scan/issues/17 Prior PR (which will be closed): https://github.com/Concordium/concordium-scan/pull/60

Successfully created an index which overrules default index graphql_account_statement_entries_pkey.

By looking at the execution plan

explain (analyse, verbose, buffers, summary, costs, settings, wal, timing)
select *
from graphql_account_statement_entries
where account_id = 83271
  and entry_type >= 6
order by index desc;

We have

Index Scan Backward using account_statement_entries_account_id_index_rewards_index on public.graphql_account_statement_entries  (cost=0.56..1055171.74 rows=14113168 width=60) (actual time=0.017..0.018 rows=0 loops=1)
"  Output: account_id, index, ""time"", entry_type, amount, account_balance, block_id, transaction_id"
  Index Cond: (graphql_account_statement_entries.account_id = 83271)
  Buffers: shared hit=4
"Settings: effective_cache_size = '14980MB', effective_io_concurrency = '256', max_parallel_workers = '2', max_parallel_workers_per_gather = '1', random_page_cost = '1.1', search_path = 'public', work_mem = '25567kB'"
Planning:
  Buffers: shared hit=4
Planning Time: 0.165 ms
JIT:
  Functions: 2
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 0.239 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.239 ms"
Execution Time: 0.301 ms

Using the view gives the same

explain (analyse, verbose, buffers, summary, costs, settings, wal, timing)
select *
from graphql_account_rewards
where account_id = 83271
order by index desc;

Index Scan Backward using account_statement_entries_account_id_index_rewards_index on public.graphql_account_statement_entries  (cost=0.56..1055199.78 rows=14113550 width=44) (actual time=0.068..0.070 rows=0 loops=1)
"  Output: graphql_account_statement_entries.account_id, graphql_account_statement_entries.index, graphql_account_statement_entries.""time"", graphql_account_statement_entries.entry_type, graphql_account_statement_entries.amount, graphql_account_statement_entries.block_id"
  Index Cond: (graphql_account_statement_entries.account_id = 83271)
  Buffers: shared hit=4
"Settings: effective_cache_size = '14980MB', effective_io_concurrency = '256', max_parallel_workers = '2', max_parallel_workers_per_gather = '1', random_page_cost = '1.1', search_path = 'public', work_mem = '25567kB'"
Planning:
  Buffers: shared hit=8
Planning Time: 0.662 ms
JIT:
  Functions: 4
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 1.111 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.111 ms"
Execution Time: 1.412 ms

I have validated that locally I’m able to open up details for account 35CJPZohio6Ztii2zy1AYzJKvuxbGG44wrBn7hLHiYLoF2nxnh which we can’t do on mainnet

Changes

• Apply index on table graphql_account_statement_entries which filters out rewards used by view graphql_account_rewards

Checklist

• [x] My code follows the style of this project.
• [ ] The code compiles without warnings.
• [x] I have performed a self-review of the changes.
• [x] I have documented my code, in particular the intent of the hard-to-understand areas.
• [x] (If necessary) I have updated the CHANGELOG.