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

Question

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:

• 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef is the ERC20 Transfer event log's topic ID.
• 0xEf213441a85DF4d7acBdAe0Cf78004E1e486BB96 is the smart contract of the ERC20 token.
• the \\x in Postgres' bytea format is used to type hexadecimal values as string literals, may be considered to be equivalent to the 0x prefix.
• In the Covalent database, chain_rsk_mainnet.block_log_events is a table with all events emitted by smart contracts on RSK Mainnet
• In the Covalent database, chain_rsk_mainnet.block_transactions is a table with all RSK Mainnet transaction details
• The reason that e.topics is matched twice is a performance optimisation. Strictly speaking, only the latter one is necessary.

Answer 1

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;