Transactions queries filtered by sender/recipient enrollment id scan the whole table

[EvanYan1024]

Describe the issue

TransactionStore.QueryTransactions filters transactions with (sender_eid = X OR recipient_eid = Y) (built by HasTransactionParams in token/services/storage/db/sql/common/tcondition.go), ordered by stored_at with a limit. The transactions table, however, is only indexed on tx_id and stored_at.

Without an index on sender_eid / recipient_eid, the planner walks the entire stored_at index (or the heap) applying the filter row by row. In our deployment this meant scanning ~11M rows to return a handful of transactions for a low-activity wallet, taking ~2.6s per query.

Proposed fix

Add two composite indexes to the transactions DDL in TransactionStore.GetSchema:

CREATE INDEX IF NOT EXISTS idx_sender_eid_storedat_%s ON %s ( sender_eid, stored_at DESC );
CREATE INDEX IF NOT EXISTS idx_recipient_eid_storedat_%s ON %s ( recipient_eid, stored_at DESC );

With these, Postgres BitmapOrs the two indexes and applies the limit cheaply — the same query drops from ~2.6s to ~1ms in our deployment.

I have the change ready and will submit a PR.

[adecaro]

@EvanYan1024 I can also write another big thank you for this continuous effort 🙏