CODEWITHSUNDEEP
sqlrskrootstock
serlokiyo
serlokiyo

Reputation: 363

How to find out gas fees paid for the latest transactions of a token on RSK?

I want to query the Covalent database to find out the amount of gas paid out in the latest 100 rUSDT token transfer transactions on the RSK blockchain.

In the following SQL query I am trying to join these two tables to find out the gas fees paid for each of the latest 100 transactions.

SELECT
  t.fees_paid
FROM chain_rsk_mainnet.block_log_events e 
INNER JOIN chain_rsk_mainnet.block_transactions t ON
  e.block_id = t.block_id
  AND e.tx_offset = t.tx_offset
WHERE 
  e.topics @> array[E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'::bytea]
  AND e.topics[1] = E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND e.sender = E'\\xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96'
ORDER BY e.block_id DESC, e.tx_offset DESC
LIMIT 100;

Unfortunately this query appears to take too long to process.

How can I modify this query?

More context:

Upvotes: 5

Views: 84

Answers (1)

bguiz
bguiz

Reputation: 28587

You need to put a date range on the query or else it will run for a very long time. There are a huge number of rUSDT Transfer event logs on RSK. Scanning the full table to find all of them, and joining these all in one go is the root cause that this query takes too long.

To solve this, for each of the tables being joined, add a condition to the time-related fields (block_log_events.block_signed_at and block_transactions.signed_at), to limit it to a certain interval, say a month:

AND e.block_signed_at > NOW() - INTERVAL '1 month' AND e.block_signed_at <= NOW()
AND t.signed_at > NOW() - INTERVAL '1 month' AND t.signed_at <= NOW()

Here's the full query:

SELECT
  t.fees_paid
FROM chain_rsk_mainnet.block_log_events e 
INNER JOIN chain_rsk_mainnet.block_transactions t ON
  e.block_id = t.block_id
  AND e.tx_offset = t.tx_offset
WHERE 
  e.topics @> array[E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'::bytea]
  AND e.topics[1] = E'\\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND e.sender = E'\\xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96'
  AND e.block_signed_at > NOW() - INTERVAL '1 month' AND e.block_signed_at <= NOW()
  AND t.signed_at > NOW() - INTERVAL '1 month' AND t.signed_at <= NOW()
ORDER BY e.block_id DESC, e.tx_offset DESC
LIMIT 100;

Upvotes: 6

Related Questions