Testnet indexer request takes multiple seconds

[lzpap]

Bug description

I noticed that transactions are loaded pretty slowly on the explorer, one has to wait usually multiple seconds (5-10-15) for them to appear on the bottom of either the Pacakge/Object view page or on the Checkpoint page.

Looking at the monitoring dashboard, the cause of the slow loading is the huge response time from the indexer instance. On the attached screenshot you can see the spike that occured when this explorer page was opened:

• The red line marks the indexer metric.
• The blue line is the access node with JSON RPC, not relevant for this problem.
• What takes time during the explorer page load is loading transactions that are part of the checkpoint. As you can see, these are simple consensus txs, nothing fancy, and there are only 4, so I suspect that the problem is either some setup/config on the machines or the lack of pruning.

[kodemartin]

A first inquiry suggests the following:

• iotax_queryTransactionBlocks (filters txs by checkpoint id) performs consistently with what is reported here (~14,15 sec)
• A naive 1+N query (iota_getCheckpoint + iota_getTransactionBlocks) performs much better (~4s) but still has large latency.
• GraphQL responds rapidly

  {
    checkpoint(id: {sequenceNumber: 10843159}) {
      transactionBlocks {
        nodes {
          digest
          sender {
            address
          }
          gasInput {
            gasPrice
            gasBudget
          }
          effects {
            status
            errors
            timestamp
            objectChanges {
              edges {
                node {
                  idCreated
                  idDeleted
                }
              }
            }
          }
        }
      }
    }
  }    

So it seems that we can seek to optimize the query in the indexer JSON-RPC server, following the call in the graphQL server.

[tomxey]

Replicated the slowness on local setup by artificially increasing indexer DB size to contain ~20M records (I believe it's a bit above 40M on testnet now) in transactions table.

Calling:

curl 'localhost:9005/'   -H 'content-type: application/json'   --data-raw '{
      "jsonrpc":"2.0",
      "id":4,
      "method":"iotax_queryTransactionBlocks",
      "params":[{"filter":{"Checkpoint":"10"},"options":{"showEffects":true,"showInput":true}},null,20,true]}'

Makes diesel generate and execute a query similar to the following:

SELECT *
FROM transactions
WHERE checkpoint_sequence_number = 10
ORDER by tx_sequence_number DESC;

which is slow (~40s) because of missing index on checkpoint_sequence_number (the only index is tx_sequence_number).

I didn't deeply investigate what graphql does internally, but since the entrypoint of the query is the checkpoint object, I assume that it may start looking at the checkpoints table, get transaction ids from there and then go for transactions table.
Such approach would be very fast, even without additional indexes.

The following query takes only 0.1s, producing the same results as the query above.

SELECT *
FROM transactions
WHERE tx_sequence_number between (
	select min_tx_sequence_number from checkpoints where sequence_number = 10
) and (
	select max_tx_sequence_number from checkpoints where sequence_number = 10
)
ORDER BY tx_sequence_number DESC;

Creating the index:

CREATE INDEX transactions_checkpoint_sequence_number ON public.transactions USING btree (checkpoint_sequence_number)

makes the original query perform really fast (<0.01s) and also speeds up iotax_queryTransactionBlocks endpoint to around 0.3s.

[kodemartin]

I didn't deeply investigate what graphql does internally, but since the entrypoint of the query is the checkpoint object, I assume that it may start looking at the checkpoints table, get transaction ids from there and then go for transactions table.
Such approach would be very fast, even without additional indexes.

Ideally we would like to avoid adding a new index, as it will affect insertion time and hence transaction effects latency with the current setting.

So this solution seems like a good candidate. At any case, I would suggest that you resolve the exact query used by GraphQL. I would assume to be the optimal solution.

[kodemartin]

@tomxey this upstream patch might be relevant too.