Assess query perfomance for filtering transactions

[tomimor]

🌟 Feature Request

📝 Description

The query for filtering transactions in the Prividium and normal Block Explorer should be assessed for possible optimization to improve performance. Currently, the system performs several sequential scans when users retrieve their transaction history, indicating potential improvement opportunities.

🤔 Rationale

Reviewing this query is important because:

• It directly impacts user experience when viewing transaction history.
• Sequential scans are resource-intensive and can lead to slower response times.
• The current implementation may not scale well with increasing data volume in Prividium Mode.

📋 Additional Context

Sample query:

explain analyze SELECT
    "addressTransactions"."number",
    "transactionReceipts"."gasUsed",
    "transactionReceipts"."contractAddress",
    "batches"."commitTxHash",
    "batches"."executeTxHash",
    "batches"."proveTxHash"
FROM
    "addressTransactions"
LEFT JOIN
    "transactions" ON "addressTransactions"."transactionHash" = "transactions"."hash" -- Or your actual FK column
LEFT JOIN
    "transactionReceipts" ON "transactions"."hash" = "transactionReceipts"."transactionHash" -- Or "transaction"."transactionReceiptId" = "transactionReceipt"."id", or your actual FK relationship
LEFT JOIN
    "batches" ON "transactions"."l1BatchNumber" = "batches"."number" -- Or your actual FK column
left join
    "logs" on "transactions"."hash" = "logs"."transactionHash"
WHERE
    "addressTransactions"."address" = '\xDFC0938C8067E50D5D1DD7EB7CF3DCBEA0332F6D' -- Parameter for filterOptions.address
    or "logs"."topics"[2] = '\x000000000000000000000000DFC0938C8067E50D5D1DD7EB7CF3DCBEA0332F6D'
	or "logs"."topics"[3] = '\x000000000000000000000000DFC0938C8067E50D5D1DD7EB7CF3DCBEA0332F6D'
	or "logs"."topics"[4] = '\x000000000000000000000000DFC0938C8067E50D5D1DD7EB7CF3DCBEA0332F6D'