---
id: ML-169.11
title: "Fix: Correlated subqueries in ListeningStats.recent_activity"
status: To Do
assignee: []
created_date: "2026-05-19 11:48"
updated_date: "2026-05-23 15:48"
labels:
  - perf
  - fix
  - query
dependencies: []
references:
  - >-
    backlog/documents/doc-27 -
    Phase-4-Performance-Audit-Low-Hanging-Fruit-Findings.md
modified_files:
  - lib/music_library/listening_stats.ex
parent_task_id: ML-169
priority: medium
ordinal: 32000
---

## Description

<!-- SECTION:DESCRIPTION:BEGIN -->

Optimize the `tracks_with_record_info_query` in `ListeningStats.recent_activity` to eliminate 3 correlated subqueries per row by using batch post-processing in Elixir.

## Problem

The `tracks_with_record_info_query` includes 3 correlated subqueries per row (matching_records, artist_id, cover_hash), each with a nested subquery through `record_releases`. This runs for up to 100 tracks on every StatsLive mount and every scrobble update.

```sql
(SELECT json_group_array(...) FROM records r WHERE r.musicbrainz_id = ...),
(SELECT min(ar.musicbrainz_id) FROM artist_records ar ...),
(SELECT r.cover_hash FROM records r ...)
```

## Impact

300 correlated subqueries total for 100 tracks. Already noted in code comments: "cost scales with number of result rows (≤ limit)". This fires on mount + every 5-minute scrobble update.

## Files

- `lib/music_library/listening_stats.ex:339-397` — `tracks_with_record_info_query`

## Fix

Consider a batch post-processing approach:

1. Fetch tracks without correlated subqueries
2. Collect all unique album release IDs
3. Batch-look up records in a single query
4. Merge record info in Elixir
<!-- SECTION:DESCRIPTION:END -->

## Acceptance Criteria

<!-- AC:BEGIN -->

- [ ] #1 recent_activity no longer uses correlated subqueries
- [ ] #2 Batch query produces same data as before
- [ ] #3 No regression in StatsLive activity feed rendering
- [ ] #4 Performance improvement measurable via QueryReporter
- [ ] #5 Existing tests for ListeningStats.recent_activity pass
<!-- AC:END -->

## Implementation Notes

<!-- SECTION:NOTES:BEGIN -->

Validation on 2026-05-23: The task is not valid as written. `tracks_with_record_info_query/0` still has three correlated subqueries, but commit d8c84e787b5acc50db0e265630193a392540a5f2 intentionally introduced the correlated-subquery shape after benchmarking against the dev DB (104k tracks), improving `recent_activity(tz, 100)` from 38.98 ms to 2.87 ms. Follow-up commits 9f4135661e6a70c85824de9fefd472d551ec86ea and cfd267627c6ddeb73514c7f1883d78c3cc5247c4 changed the scalar record lookups into `matching_records` JSON while keeping the same performance-oriented pattern. A fresh Tidewave Benchee check on the current dev DB returned matching results for a batch-post-processing prototype, but the prototype was slower: current correlated `recent_activity/2` averaged 2.30 ms vs 7.41 ms for the prototype (3.22x slower). Targeted regression suite `mix test test/music_library/listening_stats_test.exs` passed (44 tests). Recommendation: archive this task as an invalid/false-positive audit finding rather than implement AC #1.

<!-- SECTION:NOTES:END -->
