Remaining slow endpoints after #154: /accounts first-call planning, /witnesses accounts scan, and deep pages of witness and proposal vote history

Problem

After #154 (closed) and #155 (closed), these block explorer endpoints are still slow on a full-scale node. Each one is a query-only fix of the same kind #154 (closed) applied. Measured on 2026-10-17 (API p50 through PostgREST; DB = calls in one session):

endpoint now cause
/accounts/{a}, first call of each DB session 15–17 ms (calls 2–5: 3.3–3.7 ms; steady state 1.4–1.7 ms) planning: about 11 nested statements in balance_tracker's get_account_balances plus the block explorer's own get_account_* helpers are each custom-planned once per session. With balance_tracker alone, call 1 is 2.2 ms, so most of the remainder is the block explorer side. This is the block explorer's hottest endpoint: 32% of production requests.
/witnesses (every sort) 47–54 ms a full parallel scan of hafd.accounts (2.6M rows) to get names, the view-join estimate problem #154 (closed) fixed elsewhere
/witnesses/{w}/votes/history, deep pages (page 700) 304 ms names and blocks are joined for all ~70k vote events before the OFFSET
/proposals/{id}/votes/history, deep pages (asc page 600) 139 ms (#154 (closed) brought it from 554) still pays OFFSET over 30–60k rows

Change

  • /accounts/{a} first call:
    • Find which statements are planned on a session's first call, using auto_explain with log_nested_statements or per-statement EXPLAIN (SUMMARY).
    • Pin plan_cache_mode = force_generic_plan on the block explorer helpers whose predicates are all primary-key equalities. These are the ones where the generic plan is the same as the custom plan.
    • Or fold the per-field helpers into fewer statements.
    • Target: ≤ 3 ms on call 1. Leave balance_tracker's own functions alone; that is its repository.
  • /witnesses: replace the join to the accounts view with a per-row LATERAL (… LIMIT 1) or id = ANY(…) lookup, the pattern #154 (closed) used. Estimate < 10 ms.
  • /witnesses/{w}/votes/history and /proposals/{id}/votes/history:
    • paginate first: order plus LIMIT/OFFSET on the vote rows only, then enrich just the page with names and blocks;
    • estimate 20–40 ms for page 700;
    • keyset pagination would remove the OFFSET cost entirely, but it changes the API, so it is out of scope.

Acceptance

  • Every changed endpoint returns byte-identical responses (md5 of the ordered result) to before, on the tavern cases and on the deep-page cases above.
  • Quick, full and baseline slots pass.
  • Each change's before and after times on the full-scale node, or db-shape plans where a live run is not possible, are in the commit message.

Related-To: #154 (closed)