Database query performance audit — 2026-09-02

This audit traced the StatsHub v1 and v2 read surfaces against a production-sized Postgres mirror, ranked the slow statements, changed only plans that improved under measurement, and checked the affected responses against their pre-change data or the live legacy API.

The work removed one multi-second scan and several avoidable aggregate costs. The largest remaining v1 delays come from returning megabytes of data that a page cannot use at once, not from a missing index. Those endpoints need paginated v2 replacements and UI migration rather than more indexes on the legacy response shape.

Scope and method

The dynamic sweep covered 201 representative v1 read routes and the public v2 surface. It used a local mirror with production-scale row counts rather than an empty test database. Query logging recorded the SQL and duration for each request; EXPLAIN (ANALYZE, BUFFERS) separated slow plans from time spent encoding or transferring large responses.

A candidate change was accepted only when it satisfied both gates:

• The measured plan or repeated endpoint timing improved.
• The response stayed equal at the byte, decoded-data, cursor, or live-contract level appropriate to the endpoint.

Two plausible rewrites failed the performance gate and were removed. This matters because a shorter or more set-oriented query is not necessarily cheaper for the actual data distribution.

Accepted optimizations

The referee index is defined in supabase/migrations/20260902081500_optimize_referee_fouls.sql. The query changes live in:

• apps/api/internal/api/referees_list.go
• apps/api/internal/api/v2_referees.go
• apps/api/internal/api/admin_premier_league_export.go
• apps/api/internal/apiv2/betting.go
• apps/api/internal/api/world_cup_player_cards.go
• apps/api/internal/api/team_players_performance.go

The implementation was delivered in commits 0d28b1c32, f23789657, b1c5ec303, 16f23bb78, ad1252690, and c9ba9e70a. Commit 9715fd08d also taught the raw-SQL column checker to recognize PostgreSQL materialized CTE syntax and system columns.

Why the changes improved the plans

Bound the odds-movement scan before sorting

The slow odds query allowed Postgres to consider and sort a much larger movement set before the event restriction became effective. Materializing the selected event rows changes the order of work: resolve the fixture first, then walk the existing (event_id, bookmaker_id, recorded_at) access path. The optimization uses the existing index rather than adding another overlapping one.

Preserve composite keys as integers

The export query previously built team_id || '-' || event_id strings on both sides of a membership test. That discarded the database's knowledge that the values are integers and prevented a direct keyed join. Parallel unnest arrays retain the two-column identity and reduce the statement from hundreds of milliseconds to less than one millisecond on the mirror.

Index only the foul rows the referee board reads

The referee board needs event_id, homeValue, and awayValue only where statistic_key = 'fouls' and period = 'ALL'. The partial covering index stores that narrow slice and includes both values, reducing v1 buffer activity from 131,735 to 32,568 blocks in the measured plan.

The shared filtered_games CTE remains NOT MATERIALIZED. Forcing it into a wide intermediate result measured at 273.9 ms after indexing, while allowing the two consumers to receive their own narrower plans measured at 175.9 ms.

Replace correlated enrichments with grouped inputs

The team player performance query enriched thousands of event-stat rows with separate card and woodwork lookups. The replacement groups each input once and joins by player and event. Card reason arrays retain the prior physical row order because changing their order would change the byte-level legacy response.

Response parity

The audit kept representative payloads before each rewrite and compared them afterward.

The API passed its complete lint, vet, column validation, type validation, unit test, and build gates. The contract package passed 73 of 73 unit tests and its TypeScript check.

The full 1,365-case v1 contract surface was also exercised. The repository-wide baseline is not green: direct Go verification against committed goldens reported 462 passing cases and 903 mismatches. Many committed expectations no longer match the live legacy process because authentication, fixture data, status codes, and method handling have drifted. That run is evidence of existing parity debt, not proof that every API route currently matches its old golden. For the queries changed by this audit, live legacy-to-Go comparisons and saved pre-change payloads provide the relevant regression evidence.

The focused suite still reports a few non-data differences:

• Go returns structured validation behavior where legacy sometimes returns a primitive body or a 500 response.
• Cached team player responses expose the remaining cache lifetime, so the numeric max-age value changes between requests.

Neither difference changes the optimized response data.

Unbounded v1 responses are the largest remaining waste

The following endpoints return far more data than a table or scroll view can render at once:

These routes can have reasonable individual query plans and still be slow because Postgres builds large JSON values, the API decodes and re-encodes them, and the client downloads data it has not displayed. An index cannot remove that work.

The v1 contracts should remain stable for existing consumers. New UI tables should use v2 endpoints with server-side pagination, deterministic ordering, and explicit filters. A page should request the visible slice and fetch detail data after selection instead of loading a hidden full collection for local filtering.

Remaining aggregate costs

Two measured statements remain above the ordinary read-query range.

The v2 referee ranking takes roughly 270–310 ms because every sort option must aggregate 124,395 finished fixtures across all referees before selecting one page. The plan uses sensible sequential and indexed access without a disk spill. This is the cost of recomputing a global analytical view, not a missing-index symptom.

The AGS collection's main query takes roughly 374 ms while constructing a 13.8 MB legacy response. A set-based rewrite measured no faster and was discarded. Pagination is the meaningful fix for new consumers; changing the v1 payload would break its contract.

A duplicate-safe experiment combined the referee aggregates and foul lookup. It kept the data identical but raised median SQL time to about 341 ms because preaggregating 144,759 foul-stat rows cost more than the eliminated CTE scan. That change was also discarded.

After these classifications, the next measured individual statements were approximately 108 ms for unique-tournament player statistics and 84 ms for the value-bet count. Other higher endpoint totals consist of multiple smaller queries rather than one pathological statement.

Redis is not the next database optimization

Redis would not have fixed the problems addressed here. A cache hit can hide a slow query, but it does not improve the cold path, and it adds invalidation, staleness, network, and operational failure modes. The accepted index and query changes reduce the work at its source.

Redis becomes useful if StatsHub runs multiple API instances and repeatedly computes the same small public result after edge-cache misses. The referee ranking is the clearest candidate. A safe design would use canonicalized filter and sort parameters as the key, a 60–300 second lifetime, and request coalescing so simultaneous misses trigger one database computation. Fixture or statistics ingestion would invalidate the relevant generation.

The 6–82 MB v1 payloads should not be stored in Redis. Caching those responses would trade database and network waste for memory pressure while leaving the UI contract unbounded.

For referee rankings, a refreshed summary table or materialized read model is a better cold-path optimization than Redis. It can store one aggregate row per referee, refresh after ingestion, and let filtering, sorting, counting, and pagination operate on thousands of summary rows instead of more than one hundred thousand fixtures. Redis can sit in front of that read model later if traffic proves that a shared response cache is still valuable.

Conclusion

The material query defects found by the trace sweep were corrected and pushed to dev. The fastest improvement reduced a representative v2 odds request from 8.12 seconds to 10 milliseconds without changing its data. The remaining work is architectural: move new UI consumers away from unbounded v1 collections and precompute the referee analytical view if its cold latency becomes important.

Adding Redis now would increase system complexity without addressing the largest remaining waste. Real pagination and a small refreshed read model are the higher-value next steps.