I noticed the following slow query in the user_queries table:
select block_num, sum(value)
from transfer
where address = 0x8C9037D1Ef5c6D1f6816278C7AAF5491d24CD527
and block_num > 14000000
and "to" = 0x0000000000000000000000000000000000000000
group by block_num
When I first encountered it the query was timing out. I played around with block_num predicates and found that doing something like:
where block_num > 14000000 and block_num < 16000000
Was quite quick. I repeated the query up until the latest block, going 2M blocks per query, and was able to get the entire set quickly. I then noticed that after doing that, the original query took about 3s (at this point all the data is cached in memory).
This made me wonder if we can rewrite queries to paginate through blocks.
I noticed the following slow query in the user_queries table:
When I first encountered it the query was timing out. I played around with block_num predicates and found that doing something like:
Was quite quick. I repeated the query up until the latest block, going 2M blocks per query, and was able to get the entire set quickly. I then noticed that after doing that, the original query took about 3s (at this point all the data is cached in memory).
This made me wonder if we can rewrite queries to paginate through blocks.