Speed up DB

[robcxyz]

Added more alotted memory to the instances but still queries like this are taking VERY long

select count(hash) from transactions;  --~30s sng / 1m ams 
select count(*) from blocks;  --~10s sng / 20s ams  

-- Have not got this to finish yet 
SELECT count(s.i) AS missing_numbers
		FROM generate_series(1::int,10000000::int) s(i)
		WHERE NOT EXISTS (SELECT 1 FROM blocks WHERE number = s.i);

Sng

max_connections = 2000
shared_buffers = 50GB
effective_cache_size = 150GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 6553kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 40
max_parallel_workers_per_gather = 4

Ams

max_connections = 1000
shared_buffers = 32040MB
effective_cache_size = 90GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5898kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

Increased effective / shared cache / buffers with a little improvement on ams. Total memory usage is ~45 GB and I think all the indexes fit in memory. For the counts, it should finish as this does not need a scan.

Explain:

EXPLAIN ANALYZE select count(*) from blocks;

Planning Time: 1.591 ms
JIT:
Finalize Aggregate  (cost=1731425.77..1731425.78 rows=1 width=8) (actual time=34660.575..35228.949 rows=1 loops=1)
Execution Time: 35252.304 ms
"  Timing: Generation 1.019 ms, Inlining 215.537 ms, Optimization 25.734 ms, Emission 22.489 ms, Total 264.779 ms"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
  Functions: 12
  ->  Gather  (cost=1731425.35..1731425.76 rows=4 width=8) (actual time=34659.844..35228.936 rows=5 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Partial Aggregate  (cost=1730425.35..1730425.36 rows=1 width=8) (actual time=34638.350..34638.350 rows=1 loops=5)
              ->  Parallel Index Only Scan using block_idx_peer_id on blocks  (cost=0.56..1694381.44 rows=14417564 width=0) (actual time=0.533..34176.779 rows=11319270 loops=5)
                    Heap Fetches: 43982428

Installed pg_buffercache and was not able to make too much sense of it as to why the queries are so slow. I just don't think counting 50M rows should take that long right?