Add partial index on entities(total_commits) for leaderboard + public-rank queries

[peetzweg]

Context

The entities table currently has no indexes except the primary key (id). Two read paths scan it in full today:

1. Leaderboard (queryLeaderboard, public/private/both/followers) — ORDER BY total_commits DESC LIMIT k. Without an index this is a sequential scan plus a top-N sort.
2. Public rank (publicRankFor, shown on every profile) — SELECT count(*) WHERE suspended_at IS NULL AND total_commits > $x. Sequential scan + count aggregate. The comparison view fires up to 8 of these concurrently per page load.

This is fine at our current size, but worth fixing before the table grows.

Proposal

A partial index matching the active-entity filter both paths use:

CREATE INDEX entities_total_commits_idx
  ON entities (total_commits DESC)
  WHERE suspended_at IS NULL;

• Leaderboard: ORDER BY total_commits DESC LIMIT k becomes an index scan that reads ~k rows instead of sorting the whole table. This is the biggest win — O(n log n) sort → effectively O(log n + k).
• Public rank: the count becomes an index range scan (index-only, no heap fetch) instead of a full heap scan.
• Write cost: negligible — rows change only on cache refresh, not on reads.
• Partial (WHERE suspended_at IS NULL) keeps it small and exactly matches the query predicate.

How much it saves / when it's worth it

Rough Postgres seq-scan throughput on a narrow table is millions of rows/sec, so:

Rows in entities	Per-query scan cost today	Verdict
< ~10k	sub-millisecond	Index unnecessary — noise.
~10k–100k	low single-digit ms	Nice-to-have; leaderboard sort starts to show.
~100k–500k	10s of ms, ×(leaderboard + up to 8 rank counts per compare view)	Index clearly worth it.
> ~1M	100ms+ per scan	Required.

So the trigger is roughly ~100k active rows, or sooner if profile/leaderboard traffic climbs (each compare-view load = up to 8 concurrent rank scans).

Action

• Add the partial index as a drizzle migration when we approach ~100k rows (or sooner if we see slow queries).
• Re-check with EXPLAIN ANALYZE on the leaderboard and rank queries after adding it.

Note: the public-rank feature itself needs no schema change — publicRank is a computed response field, not a stored column. This issue is purely a future performance optimization that also speeds up the existing leaderboard.

[peetzweg]

Scope update: PR #120 implements this, extended beyond total_commits after a full sweep of every query in the app.

What the sweep found:

• metricRankFor runs a count(*) seq scan per available metric on every profile view (up to ~7), and queryLeaderboard sorts the same population per page — so each ranked column gets its own partial index on kind = 'user' AND suspended_at IS NULL AND built_at IS NOT NULL (the exact predicate both share), plus an expression index for the total mode's summed ordering, and one for the org board (kind = 'org').
• Worse than this issue anticipated: queryRecent GROUP BYs the entire append-only lookups table on every strip poll — unbounded growth, no index. PR Add indexes for hot query paths; bound the recent-lookups scan #120 adds lookups(searched_at DESC) and bounds the query to the newest 400 rows.
• queryOrgMembers sorts an org's members unindexed → partial org_members(org_id, commits DESC, member_id).
• monthly_commits needs nothing — fully covered by its (entity_id, month) PK.

The remaining action after merge is applying the migration (deliberately not done yet — see #118) and dropping any index that pg_stat_user_indexes shows unused after a week.