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_explainwithlog_nested_statementsor per-statementEXPLAIN (SUMMARY). - Pin
plan_cache_mode = force_generic_planon 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.
- Find which statements are planned on a session's first call, using
/witnesses: replace the join to the accounts view with a per-rowLATERAL (… LIMIT 1)orid = ANY(…)lookup, the pattern #154 (closed) used. Estimate < 10 ms./witnesses/{w}/votes/historyand/proposals/{id}/votes/history:- paginate first: order plus
LIMIT/OFFSETon 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.
- paginate first: order plus
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)