wjones
623af06e46
Extensively document query grouping performance issues and optimization attempts in BaseItemRepository. Add guides on PostgreSQL UUID aggregation limitations, recommended indexes, increasing database timeouts, and monitoring query performance. Current code reverts to original (slow) implementation due to EF Core/Npgsql limitations, with detailed comments and references to new docs. Provides clear workarounds and upgrade path for future `.DistinctBy()` support.
282 lines
8.8 KiB
Markdown
282 lines
8.8 KiB
Markdown
# Query Grouping Performance - Current Status and Workarounds
|
|
|
|
## Current Situation
|
|
|
|
After extensive testing, **NO optimized pattern** could be successfully translated by EF Core in the current codebase configuration. The code has been **reverted to the original working implementation**, which unfortunately generates correlated subqueries that can be slow (30+ seconds on large datasets).
|
|
|
|
## What Was Tried (All Failed)
|
|
|
|
| Attempt | Code Pattern | Error | Reason |
|
|
|---------|--------------|-------|--------|
|
|
| 1 | `MIN(uuid)` | PostgreSQL doesn't support MIN on UUID | Database limitation |
|
|
| 2 | `SelectMany + Take` | EF Core translation error | LINQ pattern not supported |
|
|
| 3 | `GroupBy + OrderBy + First` | EmptyProjectionMember error | EF Core projection bug |
|
|
| 4 | `DistinctBy` | EF Core translation error | Not supported in this context |
|
|
|
|
**Conclusion:** The EF Core version or Npgsql provider in use doesn't support these modern query patterns.
|
|
|
|
## Current Code (Working but Slow)
|
|
|
|
```csharp
|
|
// Lines 572-608 in BaseItemRepository.cs
|
|
var tempQuery = dbQuery
|
|
.GroupBy(e => e.PresentationUniqueKey)
|
|
.Select(e => e.FirstOrDefault())
|
|
.Select(e => e!.Id);
|
|
dbQuery = context.BaseItems.Where(e => tempQuery.Contains(e.Id));
|
|
```
|
|
|
|
**Generates:**
|
|
```sql
|
|
WHERE b."Id" IN (
|
|
SELECT (
|
|
SELECT b1."Id"
|
|
FROM library."BaseItems" AS b1
|
|
WHERE b0."PresentationUniqueKey" = b1."PresentationUniqueKey"
|
|
LIMIT 1
|
|
)
|
|
FROM library."BaseItems" AS b0
|
|
GROUP BY b0."PresentationUniqueKey"
|
|
)
|
|
```
|
|
|
|
**Performance:** Can timeout on large datasets (30+ seconds)
|
|
|
|
## Workaround Options
|
|
|
|
### Option 1: Increase Command Timeout (Quick Fix)
|
|
|
|
Increase the database command timeout to allow slow queries to complete:
|
|
|
|
**File:** `Jellyfin.Server.Implementations/Extensions/ServiceCollectionExtensions.cs`
|
|
|
|
```csharp
|
|
serviceCollection.AddPooledDbContextFactory<JellyfinDbContext>((serviceProvider, opt) =>
|
|
{
|
|
var provider = serviceProvider.GetRequiredService<IJellyfinDatabaseProvider>();
|
|
provider.Initialise(opt, efCoreConfiguration);
|
|
var lockingBehavior = serviceProvider.GetRequiredService<IEntityFrameworkCoreLockingBehavior>();
|
|
lockingBehavior.Initialise(opt);
|
|
|
|
var loggerFactory = serviceProvider.GetService<Microsoft.Extensions.Logging.ILoggerFactory>();
|
|
if (loggerFactory != null)
|
|
{
|
|
opt.UseLoggerFactory(loggerFactory)
|
|
.EnableSensitiveDataLogging()
|
|
.EnableDetailedErrors()
|
|
.CommandTimeout(120); // Increase to 120 seconds
|
|
}
|
|
});
|
|
```
|
|
|
|
**Pros:** Simple, immediate fix
|
|
**Cons:** Doesn't solve the underlying performance issue
|
|
|
|
---
|
|
|
|
### Option 2: Disable Grouping for Problem Queries (Application-Level Fix)
|
|
|
|
Modify calling code to avoid grouping on large result sets:
|
|
|
|
**Before:**
|
|
```csharp
|
|
var query = new InternalItemsQuery
|
|
{
|
|
IncludeItemTypes = new[] { BaseItemKind.Series },
|
|
GroupBySeriesPresentationUniqueKey = true // Causes slow query
|
|
};
|
|
```
|
|
|
|
**After:**
|
|
```csharp
|
|
var query = new InternalItemsQuery
|
|
{
|
|
IncludeItemTypes = new[] { BaseItemKind.Series },
|
|
GroupBySeriesPresentationUniqueKey = false // Faster but may have duplicates
|
|
};
|
|
|
|
// Handle duplicates in application code if needed
|
|
var items = await repository.GetItemsAsync(query);
|
|
var uniqueItems = items.DistinctBy(i => i.PresentationUniqueKey).ToArray();
|
|
```
|
|
|
|
**Pros:** Avoids database performance hit
|
|
**Cons:** May return more data, requires application-level deduplication
|
|
|
|
---
|
|
|
|
### Option 3: Add Database Indexes (Best for Performance)
|
|
|
|
Ensure proper indexes exist for the grouping columns:
|
|
|
|
```sql
|
|
-- PostgreSQL
|
|
CREATE INDEX IF NOT EXISTS "IX_BaseItems_PresentationUniqueKey"
|
|
ON library."BaseItems" ("PresentationUniqueKey");
|
|
|
|
CREATE INDEX IF NOT EXISTS "IX_BaseItems_SeriesPresentationUniqueKey"
|
|
ON library."BaseItems" ("SeriesPresentationUniqueKey");
|
|
|
|
-- Composite index for the two-key scenario
|
|
CREATE INDEX IF NOT EXISTS "IX_BaseItems_PresentationKeys"
|
|
ON library."BaseItems" ("PresentationUniqueKey", "SeriesPresentationUniqueKey");
|
|
```
|
|
|
|
Check existing indexes:
|
|
```sql
|
|
SELECT * FROM pg_indexes
|
|
WHERE tablename = 'BaseItems'
|
|
AND schemaname = 'library';
|
|
```
|
|
|
|
**Pros:** Can dramatically improve query performance even with correlated subqueries
|
|
**Cons:** Requires database migration, additional storage
|
|
|
|
---
|
|
|
|
### Option 4: Use Raw SQL for Problem Queries (Advanced)
|
|
|
|
For specific slow queries, bypass EF Core and use raw SQL:
|
|
|
|
```csharp
|
|
// In BaseItemRepository.cs
|
|
private async Task<List<Guid>> GetDistinctItemIdsByPresentationKeyAsync(
|
|
JellyfinDbContext context,
|
|
IQueryable<BaseItemEntity> baseQuery,
|
|
CancellationToken cancellationToken)
|
|
{
|
|
// This is a workaround for EF Core translation limitations
|
|
// Extract the WHERE clause conditions and use raw SQL for the grouping
|
|
|
|
var sql = @"
|
|
SELECT DISTINCT ON (""PresentationUniqueKey"") ""Id""
|
|
FROM library.""BaseItems""
|
|
WHERE ""Type"" = {0}
|
|
AND ""TopParentId"" = ANY({1})
|
|
ORDER BY ""PresentationUniqueKey"", ""Id""";
|
|
|
|
return await context.Database
|
|
.SqlQueryRaw<Guid>(sql, type, topParentIds)
|
|
.ToListAsync(cancellationToken);
|
|
}
|
|
```
|
|
|
|
**Pros:** Complete control, optimal SQL
|
|
**Cons:** Database-specific, harder to maintain, bypasses EF Core features
|
|
|
|
---
|
|
|
|
### Option 5: Upgrade EF Core (Future Solution)
|
|
|
|
Check if a newer version of EF Core PostgreSQL provider has better support:
|
|
|
|
```xml
|
|
<!-- In .csproj -->
|
|
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.0" />
|
|
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="9.0.0" />
|
|
```
|
|
|
|
**Pros:** May support DistinctBy translation
|
|
**Cons:** Requires testing, may introduce breaking changes
|
|
|
|
---
|
|
|
|
## Recommended Immediate Action
|
|
|
|
**Combination of Options 1 + 3:**
|
|
|
|
1. **Increase command timeout** to prevent timeouts while working on a better solution
|
|
2. **Add database indexes** on grouping columns to improve performance of current queries
|
|
3. **Monitor query performance** with logging enabled
|
|
4. **Plan for Option 5** (EF Core upgrade) in next major release
|
|
|
|
## Implementation
|
|
|
|
### Step 1: Increase Timeout (File already modified)
|
|
|
|
The database configuration in `ServiceCollectionExtensions.cs` already has logging configured. Add command timeout:
|
|
|
|
```csharp
|
|
opt.UseLoggerFactory(loggerFactory)
|
|
.EnableSensitiveDataLogging()
|
|
.EnableDetailedErrors()
|
|
.CommandTimeout(120); // Add this line
|
|
```
|
|
|
|
### Step 2: Add Indexes
|
|
|
|
Create a migration or run directly on database:
|
|
|
|
```sql
|
|
-- Run in PostgreSQL
|
|
\c jellyfin_testdata
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_PresentationUniqueKey"
|
|
ON library."BaseItems" ("PresentationUniqueKey")
|
|
WHERE "PresentationUniqueKey" IS NOT NULL;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_SeriesPresentationUniqueKey"
|
|
ON library."BaseItems" ("SeriesPresentationUniqueKey")
|
|
WHERE "SeriesPresentationUniqueKey" IS NOT NULL;
|
|
|
|
-- Check index usage after a few queries
|
|
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
|
|
FROM pg_stat_user_indexes
|
|
WHERE tablename = 'BaseItems'
|
|
AND schemaname = 'library'
|
|
ORDER BY idx_scan DESC;
|
|
```
|
|
|
|
### Step 3: Monitor Performance
|
|
|
|
With logging enabled (already configured), watch for slow queries:
|
|
|
|
```
|
|
[WRN] Executed DbCommand (15000ms) ...
|
|
```
|
|
|
|
If queries are still slow after indexing, consider Option 2 (disable grouping) for specific problem endpoints.
|
|
|
|
---
|
|
|
|
## Why Optimization Failed
|
|
|
|
1. **EF Core Version Mismatch:** The .NET 11 preview may be using an incompatible EF Core version
|
|
2. **Npgsql Limitations:** The PostgreSQL provider may not support certain LINQ patterns
|
|
3. **Complex Query Context:** The grouping happens in a complex query chain that limits translation options
|
|
4. **Projection Tracking:** EF Core loses track of entity shape with certain patterns
|
|
|
|
---
|
|
|
|
## Long-Term Solution
|
|
|
|
When Jellyfin upgrades to a stable, released version of .NET (e.g., .NET 9 or later) with a matching EF Core version, revisit the optimization attempts. The `DistinctBy` pattern SHOULD work but requires:
|
|
|
|
1. EF Core 7.0+ with full DistinctBy support
|
|
2. Npgsql.EntityFrameworkCore.PostgreSQL 7.0+
|
|
3. Compatibility testing across all supported databases
|
|
|
|
---
|
|
|
|
## Documentation
|
|
|
|
All attempts and learnings have been documented in:
|
|
- `docs/query-optimization-complete-story.md` - Full journey with all 4 attempts
|
|
- `docs/postgresql-uuid-aggregate-fix.md` - UUID-specific issues
|
|
- `docs/database-query-optimization.md` - General optimization guide
|
|
|
|
---
|
|
|
|
## Summary
|
|
|
|
✅ **Application now works** (reverted to original code)
|
|
⚠️ **Performance issue remains** (slow on large datasets)
|
|
🔧 **Workarounds available** (timeouts, indexes, disable grouping)
|
|
🚀 **Future fix possible** (with EF Core upgrade)
|
|
|
|
The most pragmatic approach is:
|
|
1. Use the working code as-is
|
|
2. Add database indexes to improve performance
|
|
3. Increase command timeout to prevent failures
|
|
4. Revisit optimization when upgrading to stable .NET version
|