Go API query analysis

Read of apps/statshub-api on 2026-08-27: 273 Go files, 78,222 lines, 97 raw SQL queries across 84 files, 104 declared indexes over 29 tables.

The SQL is fine. The indexes are not.

Two things worth saying before the findings, because they set what to worry about.

Nothing is string-formatted into SQL. Every query goes through NewRaw(..., args...). There is no injection surface and statements are plan-cacheable — the two things that usually make a report like this urgent.

There are four query-in-loop sites and two are legitimate, being inserts inside a transaction. For 78k lines that is unusually clean.

So the exposure is not in how queries are written. It is in what they run against.

Findings

value_bets and betting_markets have no indexes at all

value_bets        0 indexes    referenced in 8 files
betting_markets   0 indexes    referenced in 5 files

value_bets backs the Value Bets boards and is filtered on a stable column set — source in 6 places, player_id and match_id in 4 each, market_type and line in 3, the two edge-percent columns in 9 between them. Every one of those is a sequential scan.

value_bets_v2_detail.go is the worst of them: it matches the four-tuple (player_id, market_type, line, match_id), filters source = 'prediction' OR updated_at >= NOW() - INTERVAL '24 hours', and sorts updated_at DESC NULLS LAST — none of it indexed.

CREATE INDEX CONCURRENTLY idx_value_bets_lookup
  ON value_bets (player_id, match_id, market_type, line);

CREATE INDEX CONCURRENTLY idx_value_bets_updated
  ON value_bets (updated_at DESC NULLS LAST);

-- `source` is low-cardinality, so a partial index beats a plain b-tree
CREATE INDEX CONCURRENTLY idx_value_bets_prediction
  ON value_bets (match_id, player_id) WHERE source = 'prediction';

betting_markets needs the same: props_enriched_screener.go:671 filters playername, market against nothing.

Column names here were read out of the SQL, not out of \d value_bets. Check them against the live table first, and use CONCURRENTLY — these are hot tables and a plain CREATE INDEX takes a write lock.

betting_odds has one index doing two jobs

One index, on (event_id), for a table 9 files touch. event_shots_model_odds.go:326 filters event_id = ? AND status IN (...) and reads bookmaker_id, odds. The index finds the event, then Postgres heap-fetches every row belonging to it to apply status and read two columns.

CREATE INDEX CONCURRENTLY idx_betting_odds_event_status
  ON betting_odds (event_id, status) INCLUDE (bookmaker_id, odds);

That turns an event-wide heap read into an index-only scan.

/api/event/counts-by-dates is an N+1, and the query inside it has two more faults

api/events.go:227 runs one COUNT per date pair. A month view is thirty round trips.

for i := 0; i < len(bounds); i += 2 {
    err := h.DB.NewRaw(`SELECT COUNT(DISTINCT e.id)::int AS count FROM events e
      LEFT JOIN tournaments t ON e.tournament_id = t.id
      WHERE e.time_start_timestamp >= ? AND e.time_start_timestamp < ?
        AND t.should_show = TRUE AND e.status <> 'postponed'`, start, end).Scan(...)
}

The loop collapses into one statement:

SELECT b.start_ts::text AS bucket, COUNT(*)::int AS count
FROM unnest(?::bigint[], ?::bigint[]) AS b(start_ts, end_ts)
LEFT JOIN events e
  ON e.time_start_timestamp >= b.start_ts
 AND e.time_start_timestamp <  b.end_ts
 AND e.status <> 'postponed'
JOIN tournaments t ON e.tournament_id = t.id AND t.should_show = TRUE
GROUP BY b.start_ts;

Two faults live inside the original regardless of the loop:

LEFT JOIN with a WHERE on the joined table is an inner join in disguise. WHERE t.should_show = TRUE discards exactly the NULL-extended rows the outer join produced. It behaves correctly and stops the planner from reordering the join early. Worth grepping the repo for the same shape.

COUNT(DISTINCT e.id) cannot deduplicate anything here. The join is e.tournament_id = t.id against a primary key, so it is many-to-one and no event row can be duplicated. The DISTINCT buys a sort or a hash for nothing. COUNT(*).

The value-bets batching loop could be one query

value_bets_v2_detail.go:88 chunks its key set by maxStatementParams / paramsPerGroupKey and emits (?::int, ?::text, ?::real, ?::int) per key. The batching is correct — it is respecting the 65535-parameter ceiling — but it costs one round trip and one plan per chunk for a single logical fetch. Arrays remove the ceiling:

JOIN unnest(?::int[], ?::text[], ?::real[], ?::int[])
  AS k(player_id, market_type, line, match_id)
  ON vb.player_id   = k.player_id
 AND vb.match_id    = k.match_id
 AND vb.market_type = k.market_type
 AND vb.line        = k.line

Four parameters, one query, one cached plan, any number of keys.

Smaller things

54 SELECTs carry no LIMIT, but most are keyed lookups — WHERE id IN (?), WHERE event_id = ? — and bounded by the key set. The list-shaped ones are worth a look: props_hunter_sql.go:376 and props_enriched_screener.go:311.

Nine SELECT DISTINCTs deserve the question asked of counts-by-dates: is the duplication real, or an artefact of a many-to-one join? team_ags_collection.go:131 — DISTINCT player_id ... WHERE team_id = ? AND event_id = ? — looks like a GROUP BY or an EXISTS wearing a disguise.

player_statistics_event carries two identical (player_id, event_id) indexes. It is the hottest table in the codebase at 37 files, so the duplicate costs write throughput and cache on every insert. Drop one.

event_extra_stats_batch.go:72 is the only query with four or more LEFT JOINs. Past roughly eight relations Postgres gives up exhaustive search for GEQO and plan quality stops being predictable. Worth an EXPLAIN if it is on a hot path.

Order to do them in

1. The value_bets, betting_markets and betting_odds indexes. Largest win, no code change, lowest risk.
2. counts-by-dates — an N+1 and two query faults in one small handler.
3. Drop the duplicate player_statistics_event index.
4. Convert the value-bets batching to unnest.
5. Sweep the LEFT JOIN-plus-WHERE shape and the nine DISTINCTs.

What this report cannot tell you

All of it is read from source. There is no EXPLAIN (ANALYZE, BUFFERS), no pg_stat_statements, and no row counts behind any of it, so the ranking is reasoning about access patterns rather than measured cost. A table with no indexes and a thousand rows does not need one.

Before acting, take the top twenty from pg_stat_statements by total_exec_time. If value_bets and betting_odds dominate it, the first two findings are confirmed. If something not named here dominates instead, start there — and the fact that this report missed it is the more useful finding.