rep_log10 costs 11.5 µs per post object and list_votes joins voter reputation its bridge output discards: float8 rep_log10, no reputation join for bridge votes, force_custom_plan on list_votes

Problem and intended result

hivemind reads reputation live from the reputation tracker for most of what it serves: hive_accounts_view LEFT JOINs reptracker_app.account_reputations (author reputation of every post object via get_post_view_by_id / get_full_post_view_by_id; voter reputation of every vote via hive_votes_view), and rep_log10() formats author_reputation in every bridge post object and every profile. In a 15-minute capture from a production API node of hivemind cache misses (173,075 requests), methods that return reputation are ≈85 % of the volume — bridge.get_discussion 147k/h, bridge.get_post 113k/h, bridge.get_profile 106k/h, condenser_api.get_content 58k/h, bridge.get_account_posts 36k/h, bridge.get_ranked_posts 16k/h. Using medians of real sampled requests, production builds ≥1M post objects and ≥58M vote rows per hour, each with a reputation lookup.

The lookups themselves are cheap (every access is a primary-key index probe, ≈1 µs; in 27 real calls under auto_explain they were 0–6.5 % of the call, with no sequential or hash scan of the large tables anywhere). Two places are not, measured on a full-scale mainnet node running 1.28.8:

H1. rep_log10 costs 11.5 µs per call

hivemind_postgrest_utilities.rep_log10(bigint) is plpgsql over NUMERIC logarithms: 100,000 calls take 1.15 s (11.5 µs each; the same NUMERIC formula inlined in SQL is slower still, 2.6 s, so the cost is the NUMERIC LOG). A 156-post discussion spends ≈1.8 ms in it (bridge.get_discussion p50 is 8.7 ms).

The same algorithm in float8, rounded the same way, is identical for all 674,209 values in account_reputations plus the edge values 0, ±1, 999, 1000, ±10⁹, 999999999999, 309796838424306623, −250918066235071, ±(2⁶³−1), and runs in 0.4 µs (28×):

CASE WHEN r = 0 THEN 25::numeric ELSE
  round(( (CASE WHEN r < 0 THEN -1 ELSE 1 END) * GREATEST(
      floor(log(abs(r::float8)))
      + ( (log(floor(abs(r::float8) / 10^(floor(log(abs(r::float8))) - 3))) + 1e-8)
          - floor(log(floor(abs(r::float8) / 10^(floor(log(abs(r::float8))) - 3))) + 1e-8) )
      - 9, 0) * 9 + 25)::numeric, 2) END

H2. list_votes joins voter reputation that its bridge output discards

list_votes is hivemind's largest API-side function on that node: 649,289 calls, 1.62 ms self each, ≈46 % of all API DB time. 87 % of its calls come from create_bridge_post_object with the bridge_api presentation, which emits only rshares and voter. The statement still selects from hive_votes_view (→ hive_accounts_view → account_reputations) and passes the whole row to apply_vote_presentation, so the reputation join is executed per vote and thrown away. (With only the needed columns selected the planner removes the join itself — the record pass-through is what keeps it.)

Read-only A/B on 4 real posts, custom plans, interleaved, same statement with the votes read from hive_votes + hive_accounts (no reputation) instead of hive_votes_view:

post votes with reputation join without output
392 4.14 ms 3.54 ms md5 identical
175 2.22 ms 1.82 ms md5 identical
67 1.25 ms 0.95 ms md5 identical
14 0.96 ms 0.51 ms md5 identical

−14 … −47 % of the statement. At ≥50M bridge-presented vote rows per hour (median-based) that is an estimated ≥75 s/h of DB time.

H4. list_votes's generic plan is a full scan of hive_votes

The generic plan of the list_votes statement is a sequential scan of all ≈300M hive_votes rows (estimated cost 7.0×10⁹; forcing it ran over 60 s). Today the plan cache keeps the custom plan (cost ≈3,800) and production's calls average 1.0 ms, so this is latent — but the statement has the CASE-in-WHERE/ORDER BY + LIMIT shape that flipped to disastrous generic plans in sibling HAF apps.

Required behavior

  • H1: reimplement rep_log10(bigint) as LANGUAGE sql IMMUTABLE (inlinable) with the float8 expression above, keeping the signature and the NUMERIC return type.
  • H2: in list_votes, read voter reputation only for presentations that output it (condenser_api, active_votes, and develop's bridge_list_votes); for bridge_api and database_api, read votes without the reputation join (e.g. a second statement, or a votes view variant without hive_accounts_view).
  • H4: SET plan_cache_mode = force_custom_plan on list_votes.

Reached through every bridge/condenser method that builds post objects (create_bridge_post_object, create_condenser_post_object), condenser_api.get_active_votes, database_api.list_votes/find_votes, and get_profiles.

Context and decisions

develop (77685986) adds an opt-in vote-free post mode (_votes_mode = 1 in create_bridge_post_object) that skips list_votes entirely, and a bridge_list_votes presentation that does output reputation. The vote-free mode helps only clients that ask for it; the default (embedded active_votes) is unchanged, so H2 still applies to the default path. H2 must keep bridge_list_votes reading reputation.

Acceptance criteria

  • rep_log10(r) returns exactly the current value for every reputation in account_reputations and for the edge values listed above (an equality check over the whole table returns zero mismatches), and is inlined (no plpgsql function call per row).
  • Responses of bridge.get_discussion, bridge.get_post, bridge.get_account_posts, bridge.get_ranked_posts, condenser_api.get_content, condenser_api.get_active_votes, database_api.list_votes and bridge.get_profile are byte-identical to before for the existing pattern tests and for a sample of real requests.
  • For a bridge_api-presented list_votes call, the executed plan contains no access to account_reputations; for condenser_api/active_votes/bridge_list_votes the reputation field is still present and unchanged.
  • list_votes carries plan_cache_mode = force_custom_plan.
  • On a full-scale node, rep_log10 per call ≤ 1 µs and the bridge list_votes statement is faster on posts with ≥50 votes (before/after recorded in the commit).

Verification

The existing tavern/pattern tests; an equality query of old vs new rep_log10 over all of account_reputations on a full-scale database; before/after timing of list_votes for a few real posts with 10–400 votes.

Integration points

  • hive/db/sql_scripts/postgrest/utilities/rep_log10.sql — float8 LANGUAGE sql IMMUTABLE implementation.
  • hive/db/sql_scripts/postgrest/utilities/list_votes.sql — no reputation join for presentations that do not output it; force_custom_plan.

Scope boundary

No schema change: storing a precomputed display score in the reputation tracker was considered and is unnecessary once rep_log10 is cheap. The author-reputation probe per post object (≈1 µs) is left as is. The vote-free post mode is develop's existing work and is not changed.