Summary

Fixes a PostgreSQL compatibility bug and optimizes address page database queries for accounts with large transaction counts.

Bug Fix

• Fixed missing subquery aliases in GetElTransactionsByAccountIDCombined and GetElTokenTransfersByAccountIDCombined that caused subquery in FROM must have an alias errors on PostgreSQL (introduced in 62847ad for SQLite compatibility)

Performance Improvements

Measured via EXPLAIN ANALYZE against a production-scale database (14M transactions, 11.5M token transfers, 56.9M internal transactions):

Changes

• Count queries: Replaced UNION ALL count patterns with separate index-only scans per direction (from_id / to_id counted independently). Slightly overcounts self-referencing entries but is acceptable since counts are already capped at 100k.
• Tab visibility checks: Added HasElTransactionsInternalByAccount and HasElWithdrawalsByAccountID using SELECT EXISTS(...) instead of running full count queries just to check if rows exist.
• Withdrawal query: Replaced CTE-based count+data approach with separate count and data queries, consistent with other table patterns.
• New indexes on el_token_transfers: Composite indexes (from_id, token_type) and (to_id, token_type) to enable index-only scans for token transfer count queries filtered by token type.
• New indexes on el_transactions_internal: Single-column indexes (from_id) and (to_id) for efficient count and EXISTS queries. These are smaller than the existing composite indexes and reduce heap fetches from 100k+ to <200 for index-only scans.

All queries verified on both PostgreSQL and SQLite.

PostgreSQL tuning notes

Analysis revealed that shared_buffers = 128MB on a 37GB database causes severe cold-cache performance issues (e.g., index-only scans hitting disk for every heap fetch). Recommended:

• Increase shared_buffers to at least 2-4GB
• Ensure autovacuum runs frequently to maintain visibility maps (reduces heap fetches)
• Set random_page_cost = 1.1 if using SSD storage