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' byte 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.
CodePudding user response:
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,
Trying to scan through the entire DB, find all of them, and join on it in one go
is the root cause for this query taking 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;