Block processing seq-scans all 2.6M accounts every block (168 ms/block): resolve names in account_ids with a per-name lateral lookup

Problem and intended result

Live block processing costs about 168 ms of database time per block on a full-scale mainnet test node: reptracker_block_range_data averaged 167.8 ms over 108,626 single-block calls (pg_stat_user_functions), about 15,000 shared-buffer hits per block for roughly 7 votes. At one block every 3 s that is ≈200 s of DB time per hour (≈5.6 % of a core), on every node, forever — as much as all of haf_block_explorer's block processing (balance tracker included). Production runs the same code (514f880c; develop 5e73b7b0 has no SQL change) against an accounts table of the same size, so the same cost is expected there.

Cause. In the main statement of db/process_block_range.sql, the account_ids CTE is

account_ids AS (
  SELECT ua.name, ha.id
  FROM unique_accounts ua
  JOIN accounts_view ha ON ha.name = ua.name
),

accounts_view and operations_view are HAF context views; their block_num <= c.min_block predicate is estimated at the default one-third selectivity, so operations_in_range is estimated at 3.06 million rows for one block (actual: 2–24). With that estimate the planner joins account_ids as a hash join over a sequential scan of all 2,621,482 hafd.accounts rows — in every block, in both the custom and the generic plan. EXPLAIN (ANALYZE, BUFFERS) for block 110867695 (24 votes): Seq Scan on accounts ha … actual rows=2621482, 108.8 ms, 16,016 of the statement's 16,144 buffer hits.

Intended result. The names in a block (a few dozen at most) are resolved by index probes, and the statement no longer reads the whole accounts table per block. Expected: block processing ≈168 → ≈12 ms per block.

Required behavior

Replace the join with a per-name lateral lookup, which plans as an index probe regardless of the row estimate:

account_ids AS (
  SELECT ua.name, ha.id
  FROM unique_accounts ua
  CROSS JOIN LATERAL (SELECT av.id FROM accounts_view av WHERE av.name = ua.name LIMIT 1) ha
),

Reached from production through reptracker_app.process_blocks → reptracker_process_blocks → reptracker_single_processing / reptracker_massive_processing → reptracker_block_range_data. Everything else in the statement stays as it is.

Evidence for the fix (measured read-only on live mainnet data)

The function's statement was reproduced as a read-only query (the insert_new_permlinks, delete_votes, upsert_votes and materialize_votes CTEs stubbed), returning md5 digests of check_if_prev_balances_canceled, ranked_data and votes_with_subsequent_delete — the rows the writes and the reputation loop consume. Base and candidate were prepared with the function's five plpgsql variables as parameters and run interleaved over 15 recent blocks (2–24 votes, some with deletes):

plan base median / p95 candidate median / p95 output
generic 145.3 / 148.9 ms 0.4 / 1.8 ms md5 identical 15/15
custom 146.3 / 149.8 ms 1.4 / 1.7 ms md5 identical 15/15

The remainder of a block's cost (the reputation loop ≈1.4 ms, the other statements ≈7 ms) is unchanged, hence the ≈12 ms/block estimate. In MASSIVE_PROCESSING (10,000-block batches) the hash join is a reasonable plan, but the lateral form is also correct there; the batch timing must not regress noticeably (see criteria).

Acceptance criteria

  • For any block range, the rows written to permlinks, active_votes and account_reputations are identical to the current code's (the regression comparison against hived's reputations passes unchanged).
  • In LIVE mode, the main statement of reptracker_block_range_data no longer contains a sequential scan of hafd.accounts; names are resolved by index scans on the accounts name index.
  • On a full-scale node, per-block DB time of reptracker_block_range_data drops from ≈168 ms to under 30 ms; the commit message records the before/after per-block timing and how it was measured.
  • A MASSIVE_PROCESSING batch of 10,000 blocks takes no more than 10 % longer than before.

Verification

  • The project's existing sync + regression check (every account's reputation compared with hived's) on the 5M-block test data.
  • Before/after timing of one LIVE block's statement on a full-scale node (EXPLAIN ANALYZE of the statement or pg_stat_user_functions mean over a few hundred blocks), plus one MASSIVE batch timing.

Integration points

  • db/process_block_range.sql — reptracker_block_range_data: the account_ids CTE becomes a per-name CROSS JOIN LATERAL … LIMIT 1 lookup.

Scope boundary

The VACUUM FULL requests issued during MASSIVE_PROCESSING are #13 (closed). The endpoint/role defects are a separate issue. The underlying context-view estimate is a HAF-level matter and is not changed here.

Related-To: #13 (closed)