Design: API v2/transactions query parameter note-prefix unacceptably slow

[purestaketdb]

Summary

Searching the note-prefix results in +15minutes of querying directly on the database, it is essentially impossible to search this through the API

Scope/Requirements

Either the query parameter should be removed from the API, or some combination of new columns or indexes need to created so searching with note-prefix can complete in a useful time period.

Urgency/Relative Priority

We are going to remove the capability from the API for now as it can cause issues.

Note: The query might not be translating quite correctly when pulled out, I've attempted both

SELECT t.round, t.intra, t.txnbytes, t.extra, t.asset, h.realtime FROM txn t JOIN block_header h ON t.round = h.round WHERE substring(decode(t.txn -> 'txn' ->> 'note', 'base64') from 1 for 8) = 'UkFGLU1JTlQ=' ORDER BY t.round, t.intra LIMIT 1000;

and the encoded of %3D for the "=" sign in the search string.

[brianolson]

Filtering only on Note will exhaustively search all txns. If there's any other indexed filter than can be added, round range, address, etc. that would help speed up the query.

This could be indexed by PostgreSQL. The following index works for an updated query.

CREATE INDEX CONCURRENTLY IF NOT EXISTS txn_noteb ON txn (decode(txn.txn -> 'txn' ->> 'note', 'base64'), round, intra);

-- original query
EXPLAIN SELECT t.round, t.intra, t.txnbytes, t.extra, t.asset, h.realtime FROM txn t JOIN block_header h ON t.round = h.round WHERE substring(decode(t.txn -> 'txn' ->> 'note', 'base64') from 1 for 8) = 'UkFGLU1JTlQ=' ORDER BY t.round, t.intra LIMIT 1000;
-- new query
EXPLAIN SELECT t.round, t.intra, t.txnbytes, t.extra, t.asset, h.realtime FROM txn t JOIN block_header h ON t.round = h.round WHERE decode(t.txn -> 'txn' ->> 'note', 'base64') LIKE 'RAF-MINT%' ORDER BY t.round, t.intra LIMIT 1000;

[purestaketdb]

I did check with additional criteria and it is faster, but nothing precludes users from using it directly, which is a concern.

I'm adding the index in our dev environment and will test today.

[brianolson]

I think the new query structure is needed to use the index, and that will be a code change to Indexer.

[yaovi-a]

Should be an epic due the number of components that needs to be touched.

[winder]

This option is now disabled by default. An Index should be created for custom deployments if it is needed.