Replaces `involved_addresses` with JSONB queries inside `message` table

[RiccardoM]

trafficstars

Feature description

Currently when parsing a message we put all the involved accounts addresses inside the involved_addresses column. This later allows to query the transactions inside which a specific account is involved. Although this works, there is a better way to allow such searches: search directly inside the raw JSON value of a message.

Implementation proposal

To do this, we could try:

1. using GIN indexes to index the raw message value;
2. using JSON operators.

Another alternative is to read the message value as a TEXT and then using the LIKE operator:

SELECT * FROM message WHERE (SELECT CAST(message AS TEXT) LIKE "%address%")

[MonikaCat]

Hey @RiccardoM I tested your suggested implementation (here) but it looks that it is a bit slower than the current approach we are using. Currently the execution time for query A is 1011.704 ms and reading from value column for the same query takes around 6896.864 ms. Is there any chain that you feel is too slow to fetch the transactions?

[RiccardoM]

@MonikaCat This was an idea I had in order to be able to remove the MessagesParser from Juno. If we are able to use this method instead of the involved_addresses, then we can remove the messages parsers all together. What happens if instead of a GIN INDEX we simply use an INDEX instead? Would this work and make the query run faster?

[MonikaCat]

@RiccardoM I am not sure if we can use just INDEX coz it seems that the value size is too big to index it and I am running into this error when trying to create it index row requires 19192 bytes, maximum size is 8191.