/accounts/{a} still costs 15–17 ms on each DB session's first call: plan-cache the explorer's own get_account statements (left over from #158)
Problem
/accounts/{a} is the block explorer's busiest data endpoint. Behind the API cache on api.hive.blog it serves about 1,090 requests/h (6-hour backend log, 2026-10-17), at p50 9.5 ms and p95 67.5 ms on 1.28.8. Most of its latency on a fresh DB session is planning, not execution.
On a full-scale node, with the block explorer integration branch plus balance_tracker's integration branch (#68 (closed) included), the same calls in one DB session cost:
- call 1: 15–17 ms;
- calls 2–5: 3.3–3.7 ms;
- steady state (calls 6+): 1.4–1.7 ms.
balance_tracker's get_account_balances alone now costs 2.2 ms on call 1, since #68 (closed). The rest of the first-call cost is the block explorer's own hafbe_endpoints.get_account / hafbe_backend.get_account_* statements, each custom-planned once per session. PostgREST reaches PostgreSQL through pgbouncer, so production keeps opening new server sessions, and every new session pays this cost.
This was item 1 of #158 (closed). #158 (closed) landed the /witnesses and vote-history items but did not change /accounts.
Change
- Find exactly which statements
/accounts/{a}plans on a session's first call. Useauto_explainwithlog_nested_statements = onandlog_min_duration = 0, orEXPLAIN (ANALYZE, SUMMARY)per statement, on a session-localSET. - For each block explorer statement whose predicates are all primary-key or unique equalities, so the generic plan equals the custom plan, set
plan_cache_mode = force_generic_planon the function, or fold the per-field helpers into fewer statements. - Don't change balance_tracker's or reputation_tracker's functions (submodules).
- Measuring safely: on the db-shape copy, use aidev's estimate commands (
aidev db shape plan --nested --setup …,plan --both) or the slot's own clone of the shared HAF. Never runCREATE DATABASE … TEMPLATE shape_*: the shape is a 6.5 TB sparse database, and a template copy writes it out in full (aidev#16266).
Acceptance
/accounts/{a}gives byte-identical output to before on the tavern accounts.- Call 1 of a fresh session is ≤ 3 ms of DB time on a full-scale node, or the commit shows per-statement planning times before and after and explains what remains.
- Quick, full and baseline slots pass.
Related-To: #158 (closed)