Reduce correlated EXISTS queries

2 files changed, 121 insertions, 110 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
index a1f02be059..7570421e78 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.QueryBuilding.cs
@@ -117,28 +117,44 @@ public sealed partial class BaseItemRepository
         // Only collapse specific item types, keep others untouched
         var collapsibleTypeNames = collapsibleTypes.Select(t => _itemTypeLookup.BaseItemKindNames[t]).ToList();
 
+        // Categorize items in currentIds in a single pass to avoid multiple correlated EXISTS over BaseItems.
+        var categorized = context.BaseItems
+            .AsNoTracking()
+            .Where(bi => currentIds.Contains(bi.Id))
+            .Select(bi => new
+            {
+                bi.Id,
+                IsCollapsible = collapsibleTypeNames.Contains(bi.Type),
+                IsBoxSet = bi.Type == boxSetTypeName
+            });
+
+        var collapsibleChildIds = categorized.Where(c => c.IsCollapsible).Select(c => c.Id);
+
+        // Single JOIN: manual links to BoxSet parents, restricted to currentIds children.
+        var manualBoxSetLinks = context.LinkedChildren
+            .Where(lc => lc.ChildType == Database.Implementations.Entities.LinkedChildType.Manual
+                && currentIds.Contains(lc.ChildId))
+            .Join(
+                context.BaseItems.Where(bs => bs.Type == boxSetTypeName),
+                lc => lc.ParentId,
+                bs => bs.Id,
+                (lc, bs) => new { lc.ChildId, lc.ParentId });
+
+        var childrenInBoxSet = manualBoxSetLinks.Select(x => x.ChildId).Distinct();
+
         // Items whose type is NOT collapsible (always kept in results)
-        var nonCollapsibleIds = currentIds
-            .Where(id => !context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type)));
-
-        // Collapsible items that are NOT in any box set (kept in results)
-        var collapsibleNotInBoxSet = currentIds
-            .Where(id =>
-                context.BaseItems.Any(bi => bi.Id == id && collapsibleTypeNames.Contains(bi.Type))
-                && !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName)
-                && !context.LinkedChildren.Any(lc =>
-                    lc.ChildId == id
-                    && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual
-                    && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)));
-
-        // Box set IDs containing at least one accessible collapsible child item
-        var boxSetIds = context.LinkedChildren
-            .Where(lc =>
-                lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual
-                && currentIds.Contains(lc.ChildId)
-                && context.BaseItems.Any(bi => bi.Id == lc.ChildId && collapsibleTypeNames.Contains(bi.Type))
-                && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))
-            .Select(lc => lc.ParentId)
+        var nonCollapsibleIds = categorized.Where(c => !c.IsCollapsible).Select(c => c.Id);
+
+        // Collapsible items that are not a BoxSet themselves and not a manual child of any BoxSet
+        var collapsibleNotInBoxSet = categorized
+            .Where(c => c.IsCollapsible && !c.IsBoxSet)
+            .Select(c => c.Id)
+            .Where(id => !childrenInBoxSet.Contains(id));
+
+        // BoxSet IDs containing at least one collapsible child item from currentIds
+        var boxSetIds = manualBoxSetLinks
+            .Where(x => collapsibleChildIds.Contains(x.ChildId))
+            .Select(x => x.ParentId)
             .Distinct();
 
         var collapsedIds = nonCollapsibleIds.Union(collapsibleNotInBoxSet).Union(boxSetIds);
@@ -150,23 +166,25 @@ public sealed partial class BaseItemRepository
         IQueryable<Guid> currentIds,
         string boxSetTypeName)
     {
-        // Items that are NOT box sets and NOT in any box set
-        var notInBoxSet = currentIds
-            .Where(id =>
-                !context.BaseItems.Any(bs => bs.Id == id && bs.Type == boxSetTypeName)
-                && !context.LinkedChildren.Any(lc =>
-                    lc.ChildId == id
-                    && lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual
-                    && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName)));
-
-        // Box set IDs containing at least one accessible child item
-        var boxSetIds = context.LinkedChildren
-            .Where(lc =>
-                lc.ChildType == Jellyfin.Database.Implementations.Entities.LinkedChildType.Manual
-                && currentIds.Contains(lc.ChildId)
-                && context.BaseItems.Any(bs => bs.Id == lc.ParentId && bs.Type == boxSetTypeName))
-            .Select(lc => lc.ParentId)
-            .Distinct();
+        // Single JOIN: manual links to BoxSet parents, restricted to currentIds children.
+        var manualBoxSetLinks = context.LinkedChildren
+            .Where(lc => lc.ChildType == Database.Implementations.Entities.LinkedChildType.Manual
+                && currentIds.Contains(lc.ChildId))
+            .Join(
+                context.BaseItems.Where(bs => bs.Type == boxSetTypeName),
+                lc => lc.ParentId,
+                bs => bs.Id,
+                (lc, bs) => new { lc.ChildId, lc.ParentId });
+
+        var childrenInBoxSet = manualBoxSetLinks.Select(x => x.ChildId).Distinct();
+        var boxSetIds = manualBoxSetLinks.Select(x => x.ParentId).Distinct();
+
+        // Items in currentIds that are not BoxSets themselves and not a manual child of any BoxSet
+        var notInBoxSet = context.BaseItems
+            .AsNoTracking()
+            .Where(e => currentIds.Contains(e.Id) && e.Type != boxSetTypeName)
+            .Select(e => e.Id)
+            .Where(id => !childrenInBoxSet.Contains(id));
 
         var collapsedIds = notInBoxSet.Union(boxSetIds);
         return context.BaseItems.AsNoTracking().Where(e => collapsedIds.Contains(e.Id));
@@ -405,32 +423,36 @@ public sealed partial class BaseItemRepository
                 e.InheritedParentalRatingValue != null || !unratedItemTypes.Contains(e.UnratedType));
         }
 
-        // Apply excluded tags filtering (blocked tags)
+        // Apply excluded tags filtering (blocked tags).
+        // Pre-build the blocked-item-id set as a sub-select; then four index-seek Contains checks
+        // instead of one EXISTS over a 4-way OR predicate that defeats index seeks.
         if (filter.ExcludeInheritedTags.Length > 0)
         {
             var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray();
+            var blockedTagItemIds = context.ItemValuesMap
+                .Where(f => f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue))
+                .Select(f => f.ItemId);
+
             baseQuery = baseQuery.Where(e =>
-                !context.ItemValuesMap.Any(f =>
-                    f.ItemValue.Type == ItemValueType.Tags
-                    && excludedTags.Contains(f.ItemValue.CleanValue)
-                    && (f.ItemId == e.Id
-                        || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value)
-                        || e.Parents!.Any(p => f.ItemId == p.ParentItemId)
-                        || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value))));
+                !blockedTagItemIds.Contains(e.Id)
+                && !(e.SeriesId.HasValue && blockedTagItemIds.Contains(e.SeriesId.Value))
+                && !e.Parents!.Any(p => blockedTagItemIds.Contains(p.ParentItemId))
+                && !(e.TopParentId.HasValue && blockedTagItemIds.Contains(e.TopParentId.Value)));
         }
 
-        // Apply included tags filtering (allowed tags - item must have at least one)
+        // Apply included tags filtering (allowed tags - item must have at least one).
         if (filter.IncludeInheritedTags.Length > 0)
         {
             var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray();
+            var allowedTagItemIds = context.ItemValuesMap
+                .Where(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue))
+                .Select(f => f.ItemId);
+
             baseQuery = baseQuery.Where(e =>
-                context.ItemValuesMap.Any(f =>
-                    f.ItemValue.Type == ItemValueType.Tags
-                    && includeTags.Contains(f.ItemValue.CleanValue)
-                    && (f.ItemId == e.Id
-                        || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value)
-                        || e.Parents!.Any(p => f.ItemId == p.ParentItemId)
-                        || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value))));
+                allowedTagItemIds.Contains(e.Id)
+                || (e.SeriesId.HasValue && allowedTagItemIds.Contains(e.SeriesId.Value))
+                || e.Parents!.Any(p => allowedTagItemIds.Contains(p.ParentItemId))
+                || (e.TopParentId.HasValue && allowedTagItemIds.Contains(e.TopParentId.Value)));
         }
 
         // Exclude alternate versions (have PrimaryVersionId set) and owned non-extra items.
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index d14b62c3a0..9a57691fbd 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -461,20 +461,14 @@ public sealed partial class BaseItemRepository
                 var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
                 var boxSetTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.BoxSet];
 
-                // Series: played = all episodes played, unplayed = any episode unplayed
-                var seriesWithEpisodes = hasSeries
+                // Series: played = at least one episode AND all episodes played; unplayed = otherwise.
+                IQueryable<Guid> playedSeriesIds = hasSeries
                     ? context.BaseItems
+                        .AsNoTracking()
                         .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue)
-                        .Select(e => e.SeriesId!.Value)
-                        .Distinct()
-                    : Enumerable.Empty<Guid>().AsQueryable();
-
-                var seriesWithUnplayedEpisodes = hasSeries
-                    ? context.BaseItems
-                        .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue
-                            && !e.UserData!.Any(ud => ud.UserId == userId && ud.Played))
-                        .Select(e => e.SeriesId!.Value)
-                        .Distinct()
+                        .GroupBy(e => e.SeriesId!.Value)
+                        .Where(g => !g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played)))
+                        .Select(g => g.Key)
                     : Enumerable.Empty<Guid>().AsQueryable();
 
                 // BoxSet: played = all children played
@@ -496,14 +490,14 @@ public sealed partial class BaseItemRepository
                 if (isPlayed)
                 {
                     baseQuery = baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && seriesWithEpisodes.Contains(e.Id) && !seriesWithUnplayedEpisodes.Contains(e.Id))
+                        (e.Type == seriesTypeName && playedSeriesIds.Contains(e.Id))
                         || (e.Type == boxSetTypeName && playedBoxSetIds.Contains(e.Id))
                         || (e.Type != seriesTypeName && e.Type != boxSetTypeName && playedItemIds.Contains(e.Id)));
                 }
                 else
                 {
                     baseQuery = baseQuery.Where(e =>
-                        (e.Type == seriesTypeName && (!seriesWithEpisodes.Contains(e.Id) || seriesWithUnplayedEpisodes.Contains(e.Id)))
+                        (e.Type == seriesTypeName && !playedSeriesIds.Contains(e.Id))
                         || (e.Type == boxSetTypeName && !playedBoxSetIds.Contains(e.Id))
                         || (e.Type != seriesTypeName && e.Type != boxSetTypeName && !playedItemIds.Contains(e.Id)));
                 }
@@ -528,41 +522,33 @@ public sealed partial class BaseItemRepository
                 var seriesTypeName = _itemTypeLookup.BaseItemKindNames[BaseItemKind.Series];
                 var isResumable = filter.IsResumable.Value;
 
-                // Series with at least one in-progress episode.
-                var seriesWithInProgressEpisodes = context.BaseItems
-                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue
-                        && e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0))
-                    .Select(e => e.SeriesId!.Value)
-                    .Distinct();
-
-                // Series with at least one played episode.
-                var seriesWithPlayedEpisodes = context.BaseItems
-                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue
-                        && e.UserData!.Any(ud => ud.UserId == userId && ud.Played))
-                    .Select(e => e.SeriesId!.Value)
-                    .Distinct();
-
-                // Series with at least one unplayed episode.
-                var seriesWithUnplayedEpisodes = context.BaseItems
-                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue
-                        && !e.UserData!.Any(ud => ud.UserId == userId && ud.Played))
-                    .Select(e => e.SeriesId!.Value)
-                    .Distinct();
+                // Aggregate per series in a single GROUP BY pass, instead of three full scans.
+                var seriesEpisodeStats = context.BaseItems
+                    .AsNoTracking()
+                    .Where(e => !e.IsFolder && !e.IsVirtualItem && e.SeriesId.HasValue)
+                    .GroupBy(e => e.SeriesId!.Value)
+                    .Select(g => new
+                    {
+                        SeriesId = g.Key,
+                        HasInProgress = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0)),
+                        HasPlayed = g.Any(e => e.UserData!.Any(ud => ud.UserId == userId && ud.Played)),
+                        HasUnplayed = g.Any(e => !e.UserData!.Any(ud => ud.UserId == userId && ud.Played))
+                    });
+
+                // A series is resumable if it has an in-progress episode,
+                // or if it has both played and unplayed episodes (partially watched).
+                var resumableSeriesIds = seriesEpisodeStats
+                    .Where(s => s.HasInProgress || (s.HasPlayed && s.HasUnplayed))
+                    .Select(s => s.SeriesId);
 
                 // Non-series items: resumable if PlaybackPositionTicks > 0
                 var resumableItemIds = context.UserData
                     .Where(ud => ud.UserId == userId && ud.PlaybackPositionTicks > 0)
                     .Select(ud => ud.ItemId);
 
-                // A series is resumable if it has an in-progress episode,
-                // or if it has both played and unplayed episodes (partially watched).
                 baseQuery = baseQuery.Where(e =>
-                    (e.Type == seriesTypeName
-                        && (seriesWithInProgressEpisodes.Contains(e.Id)
-                            || (seriesWithPlayedEpisodes.Contains(e.Id) && seriesWithUnplayedEpisodes.Contains(e.Id)))
-                        == isResumable)
-                    || (e.Type != seriesTypeName
-                        && resumableItemIds.Contains(e.Id) == isResumable));
+                    (e.Type == seriesTypeName && resumableSeriesIds.Contains(e.Id) == isResumable)
+                    || (e.Type != seriesTypeName && resumableItemIds.Contains(e.Id) == isResumable));
             }
             else
             {
@@ -1024,31 +1010,34 @@ public sealed partial class BaseItemRepository
                 .Where(e => e.SeriesPresentationUniqueKey == filter.SeriesPresentationUniqueKey);
         }
 
+        // Pre-build the blocked-item-id set as a sub-select
         if (filter.ExcludeInheritedTags.Length > 0)
         {
             var excludedTags = filter.ExcludeInheritedTags.Select(e => e.GetCleanValue()).ToArray();
+            var blockedTagItemIds = context.ItemValuesMap
+                .Where(f => f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue))
+                .Select(f => f.ItemId);
+
             baseQuery = baseQuery.Where(e =>
-                !context.ItemValuesMap.Any(f =>
-                    f.ItemValue.Type == ItemValueType.Tags
-                    && excludedTags.Contains(f.ItemValue.CleanValue)
-                    && (f.ItemId == e.Id
-                        || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value)
-                        || e.Parents!.Any(p => f.ItemId == p.ParentItemId)
-                        || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value))));
+                !blockedTagItemIds.Contains(e.Id)
+                && !(e.SeriesId.HasValue && blockedTagItemIds.Contains(e.SeriesId.Value))
+                && !e.Parents!.Any(p => blockedTagItemIds.Contains(p.ParentItemId))
+                && !(e.TopParentId.HasValue && blockedTagItemIds.Contains(e.TopParentId.Value)));
         }
 
         if (filter.IncludeInheritedTags.Length > 0)
         {
             var includeTags = filter.IncludeInheritedTags.Select(e => e.GetCleanValue()).ToArray();
             var isPlaylistOnlyQuery = includeTypes.Length == 1 && includeTypes.FirstOrDefault() == BaseItemKind.Playlist;
+            var allowedTagItemIds = context.ItemValuesMap
+                .Where(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue))
+                .Select(f => f.ItemId);
+
             baseQuery = baseQuery.Where(e =>
-                context.ItemValuesMap.Any(f =>
-                    f.ItemValue.Type == ItemValueType.Tags
-                    && includeTags.Contains(f.ItemValue.CleanValue)
-                    && (f.ItemId == e.Id
-                        || (e.SeriesId.HasValue && f.ItemId == e.SeriesId.Value)
-                        || e.Parents!.Any(p => f.ItemId == p.ParentItemId)
-                        || (e.TopParentId.HasValue && f.ItemId == e.TopParentId.Value)))
+                allowedTagItemIds.Contains(e.Id)
+                || (e.SeriesId.HasValue && allowedTagItemIds.Contains(e.SeriesId.Value))
+                || e.Parents!.Any(p => allowedTagItemIds.Contains(p.ParentItemId))
+                || (e.TopParentId.HasValue && allowedTagItemIds.Contains(e.TopParentId.Value))
 
                 // A playlist should be accessible to its owner regardless of allowed tags
                 || (isPlaylistOnlyQuery && e.Data!.Contains($"OwnerUserId\":\"{filter.User!.Id:N}\"")));