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) ENDH2. 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)asLANGUAGE 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'sbridge_list_votes); forbridge_apianddatabase_api, read votes without the reputation join (e.g. a second statement, or a votes view variant withouthive_accounts_view). - H4:
SET plan_cache_mode = force_custom_planonlist_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 everyreputationinaccount_reputationsand 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-presentedlist_votescall, the executed plan contains no access toaccount_reputations; forcondenser_api/active_votes/bridge_list_votesthereputationfield is still present and unchanged. list_votescarriesplan_cache_mode = force_custom_plan.- On a full-scale node,
rep_log10per call ≤ 1 µs and the bridgelist_votesstatement 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— float8LANGUAGE sql IMMUTABLEimplementation.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.