The ecocredit txes must be delivered to client side ordered by descending block height because our activity table shows the most recent activities first.
The initial implementation of the all_ecocredit_txes was incredibly inefficient when I added an order by block height clause.
After digging in to the problem I realized that I made use of the wrong type of index for the tx_response.code field in our tx.data JSONB column.
The postgresql documentation about indexes and order by clearly states this but I missed it initially:
Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order.
So as such, this PR switches relevant index from GIN to BTREE.
Additionally this PR makes an adjustment to the all_ecocredit_txes query, since BTREE indexes support different operator types than GIN indexes.
After making this change in production there is a big performance boost to the query.
You can test it by going here and running the query below:
The ecocredit txes must be delivered to client side ordered by descending block height because our activity table shows the most recent activities first. The initial implementation of the
all_ecocredit_txes
was incredibly inefficient when I added an order by block height clause. After digging in to the problem I realized that I made use of the wrong type of index for thetx_response.code
field in ourtx.data
JSONB column.The postgresql documentation about indexes and order by clearly states this but I missed it initially:
https://www.postgresql.org/docs/current/indexes-ordering.html
So as such, this PR switches relevant index from GIN to BTREE. Additionally this PR makes an adjustment to the
all_ecocredit_txes
query, since BTREE indexes support different operator types than GIN indexes. After making this change in production there is a big performance boost to the query.You can test it by going here and running the query below:
This scales really well too, i.e. running the same query but asking for all of the results as opposed to just finishes in just a couple more seconds: