Optimize ABI parsing SQL Queries

[uscinski]

Copied from https://github.com/oasisprotocol/internal-ops/issues/625

Consider optimizing ABI-parsing-related SQL queries, as they're pressuring the database the most and making ABI parsing work queues seemingly stalled.

The following SQL queries are very slow on Mainnet.

Query 1

WITH
  abi_contracts AS (
  SELECT
    runtime,
    contract_address AS addr,
    abi,
    verification_info_downloaded_at
  FROM
    chain.evm_contracts
  WHERE
    runtime = $1
    AND abi IS NOT NULL )
SELECT
  abi_contracts.addr,
  abi_contracts.abi,
  evs.round,
  evs.tx_index,
  evs.body
FROM
  abi_contracts
JOIN
  chain.address_preimages AS preimages
ON
  abi_contracts.addr = preimages.address
JOIN
  chain.runtime_events AS evs
ON
  evs.type = $3
  AND evs.runtime = abi_contracts.runtime
  AND body->>$4 = encode(preimages.address_data,
    $5)
WHERE
  (evs.abi_parsed_at IS NULL
    OR evs.abi_parsed_at < abi_contracts.verification_info_downloaded_at)
LIMIT
  $2

Average number of returned rows is 487.
Average execution time is 9 minutes.
Called 136 times a day.
Total expected execution time per day: 136 x 9m = 1224m = 20h 24m
Details: https://console.cloud.google.com/sql/instances/nexus-blue-mainnet-db-62ae0fb3/insights;database=nexusmainnet;start=2025-02-12T16:20:44.829Z;end=2025-02-13T16:20:44.829Z;trace=46918b37681f107f3cd572e667cb23f1;span=3671f879811979b4;query_hash=11560467139641470048;sort_by=TOTAL_EXEC_TIME?authuser=1&chat=true&inv=1&invt=AbpdVg&project=infra-opf-prd

Query 2

SELECT
  COUNT(*)
FROM (
  WITH
    abi_contracts AS (
    SELECT
      runtime,
      contract_address AS addr,
      abi,
      verification_info_downloaded_at
    FROM
      chain.evm_contracts
    WHERE
      runtime = $1
      AND abi IS NOT NULL )
  SELECT
    abi_contracts.addr,
    abi_contracts.abi,
    txs.tx_hash,
    decode(txs.body->>$3,
      $4),
    txs.error_message_raw
  FROM
    abi_contracts
  JOIN
    chain.runtime_transactions AS txs
  ON
    txs.runtime = abi_contracts.runtime
    AND txs.to = abi_contracts.addr
    AND txs.method = $5
  WHERE
    txs.body IS NOT NULL
    AND (txs.abi_parsed_at IS NULL
      OR txs.abi_parsed_at < abi_contracts.verification_info_downloaded_at)
  ORDER BY
    addr
  LIMIT
    $2) subquery

Average number of rows returned is 1.
Average execution time is 6m 10s.
Called 136 times per day.
Total expected execution time per day: 136 x 6m 10s = 14h
Details: https://console.cloud.google.com/sql/instances/nexus-blue-mainnet-db-62ae0fb3/insights;database=nexusmainnet;start=2025-02-12T16:20:44.829Z;end=2025-02-13T16:20:44.829Z;trace=0a6b0cfd918ffc29845e8e62578b58b4;span=a904696ef974ab79;query_hash=4800199275330180946;sort_by=TOTAL_EXEC_TIME/executed?authuser=1&chat=true&inv=1&invt=AbpeYA&project=infra-opf-prd

Query 3

WITH
  abi_contracts AS (
  SELECT
    runtime,
    contract_address AS addr,
    abi,
    verification_info_downloaded_at
  FROM
    chain.evm_contracts
  WHERE
    runtime = $1
    AND abi IS NOT NULL )
SELECT
  abi_contracts.addr,
  abi_contracts.abi,
  txs.tx_hash,
  decode(txs.body->>$3,
    $4),
  txs.error_message_raw
FROM
  abi_contracts
JOIN
  chain.runtime_transactions AS txs
ON
  txs.runtime = abi_contracts.runtime
  AND txs.to = abi_contracts.addr
  AND txs.method = $5
WHERE
  txs.body IS NOT NULL
  AND (txs.abi_parsed_at IS NULL
    OR txs.abi_parsed_at < abi_contracts.verification_info_downloaded_at)
ORDER BY
  addr
LIMIT
  $2

Average number of rows returned is 12.
Average execution time is 5m 55s.
Called 136 times a day.
Total expected execution time per day: 5m 55s x 136 = 13h 25m
Details: https://console.cloud.google.com/sql/instances/nexus-blue-mainnet-db-62ae0fb3/insights;database=nexusmainnet;start=2025-02-12T16:38:25.006Z;end=2025-02-13T16:38:25.006Z;trace=1e6cf79d323e02bafeb5ad61a673bf6f;span=55c69f35d2be6fc5;query_hash=4338949837127035279;sort_by=TOTAL_EXEC_TIME?authuser=1&chat=true&inv=1&invt=AbpdVg&project=infra-opf-prd

The chain.runtime_events table has 285M+ records on Mainnet.