Performance issues when indexing an integration using filter_ref

[0xBigBoss]

I have an integration like so... integration.json

{
      "name": "send_account_transfers",
      "enabled": true,
      "table": {
        "name": "send_account_transfers",
        "columns": [
          {
            "name": "chain_id",
            "type": "numeric"
          },
          {
            "name": "log_addr",
            "type": "bytea"
          },
          {
            "name": "block_time",
            "type": "numeric"
          },
          {
            "name": "tx_hash",
            "type": "bytea"
          },
          {
            "name": "f",
            "type": "bytea"
          },
          {
            "name": "t",
            "type": "bytea"
          },
          {
            "name": "v",
            "type": "numeric"
          }
        ]
      },
      "block": [
        {
          "name": "chain_id",
          "column": "chain_id"
        },
        {
          "name": "block_time",
          "column": "block_time"
        },
        {
          "name": "tx_hash",
          "column": "tx_hash"
        },
        {
          "name": "log_addr",
          "column": "log_addr"
        }
      ],
      "event": {
        "type": "event",
        "name": "Transfer",
        "anonymous": false,
        "inputs": [
          {
            "indexed": true,
            "name": "from",
            "type": "address",
            "column": "f",
            "filter_op": "contains",
            "filter_ref": {
              "integration": "send_account_created",
              "column": "account"
            }
          },
          {
            "indexed": true,
            "name": "to",
            "type": "address",
            "column": "t",
            "filter_op": "contains",
            "filter_ref": {
              "integration": "send_account_created",
              "column": "account"
            }
          },
          {
            "indexed": false,
            "name": "value",
            "type": "uint256",
            "column": "v"
          }
        ]
      },
      "sources": [
        {
          "name": "base_logs",
          "start": "$BASE_BLOCK_START"
        }
      ]
    }

Currently there are about 2,983 send accounts created.

create table send_account_created
(
    chain_id     numeric                                            not null,
    log_addr     bytea                                              not null,
    block_time   numeric                                            not null,
    user_op_hash bytea,
    tx_hash      bytea                                              not null,
    account      bytea                                              not null,
    ig_name      text                                               not null,
    src_name     text                                               not null,
    block_num    numeric                                            not null,
    tx_idx       integer                                            not null,
    log_idx      integer                                            not null,
    id           serial primary key,
    event_id     text generated always as ((
        (((((((ig_name || '/'::text) || src_name) || '/'::text) || (block_num)::text) || '/'::text) ||
          (tx_idx)::text) || '/'::text) || (log_idx)::text)) stored not null
);

create unique index u_send_account_created on send_account_created (ig_name, src_name, block_num, tx_idx, log_idx);

create index shovel_account on send_account_created (account);

If I set the batch size to something like 2,000, shovel is basically unable to converge (I only waited 2 minutes). Reducing the batch size and it finally does converge but takes around 30 seconds. Is there a strategy to mitigate this?

As a workaround in order to not index data we don't care about, we periodically drop the erc20 transfers without a send account.

[ryansmith3136]

A few thoughts:

1. 2,000 is a large batch size given that you are downloading blocks. Typically N*1000 is a good batch size for integrations that only require log data since you can request up to 2,000 blocks worth of log data in a single request.

2. Can you see if there are any slow queries in your postgres log? Perhaps by setting log_min_duration_statement

3. Related to 2, I wonder if there is a missing index. It's important that the column that an integration depends on is part of a PG index. But the slow query log will highlight anything wrong here

[hokaxbt]

Here are my observations when using filter_ref:

• The reference column is properly indexed
• Queries to check for filter_ref existence execute quickly

However, performance is impacted because Shovel checks each filter_ref individually, executing SQL queries like:

SELECT $2 FROM ethereum_events_uniswap_v2_pair_created WHERE pair = $1