pgsql-jellyfin/docs/CORRELATED_SUBQUERY_FIX.md

# Correlated Subquery Performance Fix

## Problem Identified

**Query with 165-second execution time** was caused by correlated subquery pattern in BaseItemRepository.cs:

```csharp
// BEFORE (SLOW - Correlated Subquery)
var tempQuery = dbQuery
    .GroupBy(e => e.PresentationUniqueKey)
    .Select(e => e.FirstOrDefault())
    .Select(e => e!.Id);
dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id));
```

### Generated SQL (Problematic):
```sql
SELECT b."Id", b."Album", [70+ columns...]
FROM library."BaseItems" AS b
WHERE b."Id" IN (
    SELECT (
        SELECT b1."Id"
        FROM library."BaseItems" AS b1
        WHERE b1."Type" = $3 
          AND b1."IsVirtualItem" = $1
          AND (b1."TopParentId" = ANY ($2))
          AND (b0."PresentationUniqueKey" = b1."PresentationUniqueKey")
        LIMIT 1
    )
    FROM library."BaseItems" AS b0
)
```

**Performance Impact:**
- 165 seconds max execution time
- 15.7 seconds average
- 108,209 rows returned requiring 108,209+ subquery executions
- Caused exponential performance degradation

---

## Solution Applied

**AFTER (FAST - DistinctBy)**
```csharp
// Use DistinctBy() to generate PostgreSQL DISTINCT ON
dbQuery = dbQuery.DistinctBy(e => e.PresentationUniqueKey);
```

### Generated SQL (Optimized):
```sql
SELECT DISTINCT ON (b."PresentationUniqueKey")
       b."Id", b."Album", [70+ columns...]
FROM library."BaseItems" AS b
LEFT JOIN library."UserData" AS u ON b."Id" = u."ItemId"
WHERE b."Type" = $3 
  AND b."IsVirtualItem" = $1
  AND (b."TopParentId" = ANY ($2))
```

**Expected Performance:**
- **<50ms execution time** (600-3300x faster)
- Single table scan instead of correlated lookups
- Proper index usage

---

## Locations Fixed

Fixed **5 locations** in `Jellyfin.Server.Implementations/Item/BaseItemRepository.cs`:

1. **Line 603-605** (PRIMARY CULPRIT): `GroupBy(PresentationUniqueKey)` → `DistinctBy(PresentationUniqueKey)`
2. **Line 600-602**: `GroupBy(new { PresentationUniqueKey, SeriesPresentationUniqueKey })` → `DistinctBy(...)`
3. **Line 608-610**: `GroupBy(SeriesPresentationUniqueKey)` → `DistinctBy(SeriesPresentationUniqueKey)`
4. **Line 1660-1666**: `GroupBy(PresentationUniqueKey)` in masterQuery → `DistinctBy(PresentationUniqueKey)`
5. **Line 1828-1834**: `GroupBy(PresentationUniqueKey)` in masterQuery → `DistinctBy(PresentationUniqueKey)`

---

## Verification Steps

### 1. Build the Solution
```powershell
dotnet build Jellyfin.sln -c Release
```

### 2. Re-run Performance Diagnostics
```powershell
. .\scripts\db-config.ps1
& $PSQL_PATH -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f "sql\diagnostics.sql"
```

**Expected Results:**
- Max query time should drop from 165s to <1s
- ItemValues index usage should improve from 50% to >90%
- Sequential scans should reduce significantly

### 3. Check Top Slow Queries
```powershell
& $PSQL_PATH -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f "sql\query-analysis.sql" > query_analysis_after_fix.txt
```

**Expected Results:**
- No queries with >100s execution time
- Average query time <100ms for BaseItems queries

---

## Technical Background

### Why GroupBy().FirstOrDefault() is Slow

EF Core translates this pattern to:
```sql
WHERE Id IN (SELECT (SELECT ... LIMIT 1) FROM ...)
```

This creates a **correlated subquery** where:
- Outer query scans BaseItems
- For EACH row, inner subquery executes
- PostgreSQL can't optimize this pattern
- Indexes are bypassed

### Why DistinctBy() is Fast

EF Core translates DistinctBy() to PostgreSQL's native `DISTINCT ON`:
```sql
SELECT DISTINCT ON (PresentationUniqueKey) ...
```

This:
- Performs single table scan
- Uses indexes effectively
- PostgreSQL native optimization applies
- Returns first row per unique key efficiently

---

## References

- **docs/database-query-optimization.md**: Original documentation of this anti-pattern
- **sql/query-analysis.sql**: Query analysis tool that identified the 165s query
- **sql/diagnostics.sql**: Comprehensive database diagnostics
- **critical_query_165s.txt**: Full extracted query showing correlated subquery structure

---

## Date Applied

**March 6, 2025**

**Diagnostics Results (Before Fix):**
- Max query time: 165 seconds
- ItemValues index usage: 50.25%
- Sequential scans: 595,439 (4.7B rows)
- Cache hit ratio: 97.42%

**Next Steps:**
1. Build solution
2. Re-run diagnostics
3. Compare before/after performance
4. Document improvements