GetMessagesByAddress optimisation

[ryuash]

Currently when the Tx table gets too big GetMessagesByAddress will stop working correctly. This might be an issue with missing indexes.

Below is the query i'm currently using:

query GetMessagesByAddress($address: _text, $limit: bigint = 50, $offset: bigint = 0 $types: _text = "{}") {
  messagesByAddress: messages_by_address(args: {addresses: $address, types: $types, limit: $limit, offset: $offset}) {
    transaction {
      height
      hash
      success
      messages
      logs
      block {
        height
        timestamp
      }
    }
  }
}

Even if the limit is set to 10 the data will not return.
This is currently happening on Band and Sifchain

[RiccardoM]

I think this is due to the fact that the message table which this query is relying on did not have the following indexes:

CREATE INDEX message_type_index ON message (type);
CREATE INDEX message_involved_accounts_addresses ON message (involved_accounts_addresses);

I have now tried creating them on the PostgreSQL database for Desmos in both the testnet and the mainnet and I've pushed them here as well with 9db8eb1. @MonikaCat can you see if you can add them to the Band and SifChain databases to see whether they improve the mentioned query performance please?

[MonikaCat]

@RiccardoM I have updated band and sifchain database with the above indexes. @ryuash Can you confirm if you can see improvement in the query performance?

[ryuash]

No improvements:
https://band.bigdipper.live/accounts/band1qf73zvv3jquf2znwx2qc7tkmgtz7tvq4p97he4
https://sweet-potato.bigdipper.live/validators/sifvaloper1avau6q23mrmmuz2nlyqk0mgrdzna5tf6dws9em

Still timing out and coming back with 504. Tested on both frontend and console(s)

[tgntr]

FYI we are having the same issue. I noticed that our instance CPU was overloading and we just tried adding an extra cpu core. So far it seems to do the job! Not sure if that's considered a workaround or if actually postgres really needs some processing power.

https://explorer-v2.cudos.org/

[tgntr]

By the way, have you considered trying postgres partitioning feature? Do you think it's worth checking out for our case?

[huichiaotsou]

we applied partitioning in staging environment and it looks promising :D
we will update soon