Go API query analysis, measured

The source-read report closes by naming what it could not do:

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. […] 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.

This is that run. Production, PostgreSQL 17.11, read-only session, 2026-08-27. Something not named there does dominate.

The premise was inverted

The first report's headline finding is that the hot tables have no indexes. It counted the CREATE INDEX statements in supabase/migrations/. Production has many more indexes than the migrations declare, because indexes have been added to the live database directly.

So the first two findings, and the "largest win, no code change, lowest risk" at the top of its running order, are recommendations to create indexes that already exist.

The third — that player_statistics_event carries two identical (player_id, event_id) indexes and one should be dropped — is also wrong, and dangerously so. They are not identical:

btree (player_id, event_id) WHERE (minutes_played > 0)   2,480,012,179 scans
btree (player_id, event_id)                                 55,217,750 scans

One is partial. It is the most-scanned index in the database by an order of magnitude, and the plain one still serves 55 million scans. Dropping either would hurt.

The real problem is the opposite of the one reported. The database is not under-indexed. It carries 162 indexes that have never been scanned once, totalling 3,735 MB:

shared_buffers   4096 MB
database size     243 GB
dead indexes     3735 MB   ← 91% of the buffer pool, never read

betting_markets_archive alone holds about 2.3 GB of them, and it is the table the most expensive query in the database joins against. There are also 12 sets of exact-duplicate indexes — same table, same columns, same predicate — including one on betting_odds.

The most expensive query is not ours

 total_s     calls  mean_ms   pct  query
 31478.7  1,063,333    29.60  18.8  SELECT DISTINCT ON (COALESCE(playerid::text, 'name:' || playername), …
 11423.4     14,736   775.20   6.8  WITH wl_teams AS (SELECT home_team_id FROM events WHERE …

Between them these are a quarter of all database time. Neither has a source in this repository. betting_markets_archive appears only in scripts/dev-db/schema.sql; wl_teams and dribblesPercentage appear nowhere at all. Something outside this repo — a bot, a scraper, a pipeline — shares the database and is its heaviest user.

The first one is not badly planned. Against the worst event in the table it uses both indexes and finishes in 2 ms:

Index Scan using idx_betting_markets_event_market_bookmaker  (rows=541)
Index Scan using betting_markets_archive_event_market_bookmaker_idx (rows=0)
Execution Time: 1.992 ms          Buffers: shared hit=43 read=122

Its production mean is 29.6 ms. The gap is not the plan, it is read=122 against hit=43 — three quarters of its buffers come from disk. With 4 GB of cache for 243 GB of data and 3.7 GB of it occupied by indexes nothing reads, that is the expected outcome.

So: before adding any index, drop the dead ones. It is the same lever, pulled the other way.

The telemetry is about to destroy itself

pg_stat_statements entries   4931
pg_stat_statements.max       5000

At 5,000 Postgres evicts the least-used entries and their counters are gone. The table is at 98.6% and 834 of those entries — 17% — are one logical query each, fragmented by how many parameters it happened to carry:

entries  calls        total_s  shape
   1393  116,978        460.4  insert into "value_bets" (…) values ($N)
    377  1,268,104      461.2  select "id","playername",… where … in ($N)
    281  2,738,791     1123.5  select … from "player_statistics_event" where … in ($N)
     82  368,990       8150.1  select "event_id" from "betting_odds" where "event_id" in ($N)

IN ($1, $2, $3) and IN ($1, $2, $3, $4) are different statements. Different text, different plan cache entry, different pg_stat_statements row. A query called with a varying number of ids produces one of each.

This is the Go API's, and it is everywhere: 128 sites pass bun.In(...) into IN (?), against 66 that already use = ANY(?::int[]).

The fix costs nothing at the database. The plans are identical:

= ANY('{…}'::int[])   Index Scan using idx_betting_odds_event_odds  rows=35  0.113 ms
IN (…)                Index Scan using idx_betting_odds_event_odds  rows=35  0.126 ms

Same index, same rows, same time — because Postgres rewrites IN to = ANY internally anyway. The only difference is the SQL text, and therefore the number of plan cache and statistics entries it burns. Converting the 128 sites would return roughly 790 entries to a table that is one percent from overflowing.

What the first report got right

/api/event/counts-by-dates was an N+1. One COUNT per date pair, thirty round trips for a month view. Its two secondary observations were also correct:

• LEFT JOIN tournaments followed by WHERE t.should_show = TRUE is an inner join in disguise — the WHERE discards exactly the NULL-extended rows the outer join produced.
• COUNT(DISTINCT e.id) deduplicates nothing. tournaments.id is the primary key, so the join is many-to-one: 51 event rows in the test window, 51 rows after the join. The DISTINCT bought a sort for nothing.

Its proposed replacement was broken, and the tests it did not have would have caught it:

FROM unnest(?::bigint[], ?::bigint[]) AS b(start_ts, end_ts)
LEFT JOIN events e ON …
JOIN tournaments t ON e.tournament_id = t.id AND t.should_show = TRUE   -- inner
GROUP BY b.start_ts;

The inner join to tournaments discards the NULL-extended row a bucket with no events produces, so an empty day loses its key entirely rather than reporting 0. Measured over a 5-day window with no fixtures:

        date   loop       report   verdict
  1798761600      0   row absent   DIVERGES: key disappears

The home board's date strip reads counts[startTimestamp] for each of its seven tabs. A missing key renders blank, not zero. COUNT(*) is wrong for the same reason — it counts 1 for a NULL-extended row.

The version now in events.go keeps both joins outer and counts a column:

SELECT b.start_ts::int AS bucket, COUNT(e.id)::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'
LEFT JOIN tournaments t ON t.id = e.tournament_id
WHERE t.should_show IS TRUE OR e.id IS NULL
GROUP BY b.start_ts

Checked against the per-date loop over a 65-day window containing 64 empty days: 65 days agreeing, 0 missing keys, 0 mismatches. Seven days now cost 2.8 ms in one round trip against 6.7 ms in seven, and the event-counts-by-dates contract case still passes against the golden recorded from production.

The value_bets_v2_detail batching loop is real but almost never loops — perBatch is 60000 / 4 = 15000 keys, and a page of value bets carries far fewer. Its cost is not the round trips the report describes; it is that VALUES (?::int, ?::text, ?::real, ?::int), … emits a different statement for every key count, which is the fragmentation above wearing a different hat.

Two routes the contract run found, running the API against production

Neither is a port regression — both are as slow in the original, for the same reason — but neither had been measured against production rows before.

/api/player/all with no filters takes over 60 seconds and ends in a 500. The legacy golden for it is "Error fetching players", so this is not new: the route aggregates every appearance of every player, with the whole statistics projection, and no LIMIT. It is usable only with a team or competition filter and nothing requires one. This is the LIMIT rule in the go-api skill — "an unbounded read passes on today's data and takes the process out when the table doubles" — except the table has already doubled.

/api/event/{id}/teams-shots-model-odds costs 3.2–4.5 s cold, and 0.28 s served from shotModelCache. During the contract run the same request went past 60 s. The suite issues one request at a time, so that swing is not the route contending with itself; it is other traffic on the shared database, and it is the same cache-starvation the slowest query in the database shows as read=122 against hit=43. A route whose cold cost moves by a factor of fifteen depending on what else is running is measuring the buffer pool, not its own plan.

Order to do them in

1. Drop the 162 never-scanned indexes, starting with the 2.3 GB on betting_markets_archive, and the 12 duplicate sets. This is the cache pressure behind the slowest query in the database, and it needs no code change. DROP INDEX CONCURRENTLY.
2. Convert IN (?) to = ANY(?::int[]) across the 128 bun.In sites. Identical plans, and it returns ~790 pg_stat_statements entries.
3. Raise pg_stat_statements.max past 5,000 regardless, so the next person measuring gets data that has not been evicted.
4. Find out what runs the betting_markets DISTINCT ON query. A quarter of the database's time belongs to code nobody here can read.

What this report cannot tell you

The Go API is not in production. www.statshub.com serves the legacy Next.js app, so every number above is legacy traffic plus a few hundred calls from local contract runs. The findings transfer because the port is faithful — the same SQL shapes, the same tables — but nothing here measures the Go binary under real load.

The index findings are attribution-free and hold regardless. The IN (?) fragmentation is read from the Go source, and the shape counts confirm the legacy app produces the same pattern, which is why it is visible at all.