perf: /proposals/votes times out (statement timeout) on default page size; ~6s even for small pages

Summary

GET /hafbe-api/proposals/votes (hafbe_endpoints.get_proposal_votes, added in #129 (closed)) is too slow to use: it times out (HTTP 500, statement timeout) on the default page size, and even small-page queries take ~6s. This blocks block_explorer_ui#784 (closed) (switching the FE's proposal-vote reads from condenser_api.list_proposal_votes to this endpoint).

Reproduction (live, testapi.hivescan.info, 2026-10-07)

Request Result
/hafbe-api/proposals/votes?voter=blocktrades (no page-size → default) ❌ HTTP 500 in 11.4s — {"code":"57014","message":"canceling statement due to statement timeout"}
/hafbe-api/proposals/votes?voter=blocktrades&page-size=2 ✅ 200 but slow (6.2s)
/hafbe-api/proposals/votes?proposal-id=0&page-size=2 ✅ 200, 6.4s
/hafbe-api/proposals/votes?page=1&page-size=2 ✅ 200, 6.9s (total_votes: 66198, total_pages: 33099)

So any consumer that does not pass a small page-size hits the 500, and even the fast path (~6s) is far above an acceptable FE latency and sits close to the timeout cliff.

Expected

  • No statement timeout at the default page size.
  • Sub-second (or at least low-single-digit-second) response for typical queries (voter=<name>, proposal-id=<n>, plain paged list).

Likely area

The slowness looks structural (full scan / expensive per-row stake aggregation), not data-size-specific — a 2-row page still takes ~6s, which points at the query plan rather than row count. Worth checking:

  • Missing/!unused index for the voter and proposal-id filters and the default ordering.
  • The voter-stake computation (voter_vests/direct_vests/proxied_vests, the fields added for the history endpoint in #140 (closed)/!496 (merged)) being computed over the whole set before paging, rather than for the page.

Secondary (minor)

sort rejects some values with HTTP 400: ?sort=voter&direction=desc → invalid input value for enum hafbe_backend.order_by_proposal_vote: "voter". Please document/accept the intended sort keys (the enum hafbe_backend.order_by_proposal_vote).