/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. Use auto_explain with log_nested_statements = on and log_min_duration = 0, or EXPLAIN (ANALYZE, SUMMARY) per statement, on a session-local SET.
  • 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_plan on 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 run CREATE 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)