Fix favorite filter performance

6 files changed, 43 insertions, 28 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
index 1d34346888..24892fef92 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.ByName.cs
@@ -168,21 +168,16 @@ public sealed partial class BaseItemRepository
             IsSeries = filter.IsSeries
         });
 
-        // Resolve, then materialize, the set of clean values belonging to items that match the inner filter.
-        var matchingCleanValues = context.ItemValuesMap
-            .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type))
-            .Join(
-                innerQueryFilter,
-                ivm => ivm.ItemId,
-                g => g.Id,
-                (ivm, g) => ivm.ItemValue.CleanValue)
-            .Distinct()
-            .ToList();
-
-        // Match CleanName against the resolved clean values.
         var innerQuery = PrepareItemQuery(context, filter)
             .Where(e => e.Type == returnType)
-            .WhereOneOrMany(matchingCleanValues, e => e.CleanName!);
+            .Where(e => context.ItemValuesMap
+                .Where(ivm => itemValueTypes.Contains(ivm.ItemValue.Type) && ivm.ItemValue.CleanValue == e.CleanName)
+                .Join(
+                    innerQueryFilter,
+                    ivm => ivm.ItemId,
+                    g => g.Id,
+                    (ivm, g) => ivm.ItemId)
+                .Any());
 
         var outerQueryFilter = new InternalItemsQuery(filter.User)
         {
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
index de25e9b763..47f8a40b9c 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.TranslateQuery.cs
@@ -434,20 +434,34 @@ public sealed partial class BaseItemRepository
 
         if (filter.IsLiked.HasValue)
         {
-            var isLiked = filter.IsLiked.Value;
-            baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue) == isLiked);
-        }
+            var likedIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.Rating >= UserItemData.MinLikeValue)
+                .Select(ud => ud.ItemId);
 
-        if (filter.IsFavoriteOrLiked.HasValue)
-        {
-            var isFavoriteOrLiked = filter.IsFavoriteOrLiked.Value;
-            baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavoriteOrLiked);
+            baseQuery = filter.IsLiked.Value
+                ? baseQuery.Where(e => likedIds.Contains(e.Id))
+                : baseQuery.Where(e => !likedIds.Contains(e.Id));
         }
 
-        if (filter.IsFavorite.HasValue)
+        if (filter.IsFavoriteOrLiked.HasValue || filter.IsFavorite.HasValue)
         {
-            var isFavorite = filter.IsFavorite.Value;
-            baseQuery = baseQuery.Where(e => e.UserData!.Any(ud => ud.UserId == filter.User!.Id && ud.IsFavorite) == isFavorite);
+            var favoriteIds = context.UserData
+                .Where(ud => ud.UserId == filter.User!.Id && ud.IsFavorite)
+                .Select(ud => ud.ItemId);
+
+            if (filter.IsFavoriteOrLiked.HasValue)
+            {
+                baseQuery = filter.IsFavoriteOrLiked.Value
+                    ? baseQuery.Where(e => favoriteIds.Contains(e.Id))
+                    : baseQuery.Where(e => !favoriteIds.Contains(e.Id));
+            }
+
+            if (filter.IsFavorite.HasValue)
+            {
+                baseQuery = filter.IsFavorite.Value
+                    ? baseQuery.Where(e => favoriteIds.Contains(e.Id))
+                    : baseQuery.Where(e => !favoriteIds.Contains(e.Id));
+            }
         }
 
         if (filter.IsPlayed.HasValue)
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
index 5715447c7d..ee36be035b 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
@@ -61,8 +61,11 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated });
         // resume
         builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey });
-        // alternate versions of an item, e.g. resolving the played date of a version onto its primary
-        builder.HasIndex(e => e.PrimaryVersionId);
+        // alternate versions of an item, e.g. resolving the played date of a version onto its primary.
+        // Filtered: almost no item has a primary version, and an index covering those rows too would tempt
+        // the planner into serving "PrimaryVersionId IS NULL" - true for the whole library - out of it.
+        builder.HasIndex(e => e.PrimaryVersionId)
+            .HasFilter("\"PrimaryVersionId\" IS NOT NULL");
         // sorted library queries (e.g., Series sorted by SortName)
         builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName });
         // NextUp: per-series episode ordering (index seek + range scan on season/episode)
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs
index a35f226734..bc23ad7faa 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.Designer.cs
@@ -376,7 +376,8 @@ namespace Jellyfin.Server.Implementations.Migrations
 
                     b.HasIndex("PresentationUniqueKey");
 
-                    b.HasIndex("PrimaryVersionId");
+                    b.HasIndex("PrimaryVersionId")
+                        .HasFilter("\"PrimaryVersionId\" IS NOT NULL");
 
                     b.HasIndex("SeasonId");
 
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs
index b8dbd1f35a..069104244c 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/20260724185102_AddPrimaryVersionIdIndex.cs
@@ -13,7 +13,8 @@ namespace Jellyfin.Server.Implementations.Migrations
             migrationBuilder.CreateIndex(
                 name: "IX_BaseItems_PrimaryVersionId",
                 table: "BaseItems",
-                column: "PrimaryVersionId");
+                column: "PrimaryVersionId",
+                filter: "\"PrimaryVersionId\" IS NOT NULL");
         }
 
         /// <inheritdoc />
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs
index 6a1963637b..0916f72d9c 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/Migrations/JellyfinDbModelSnapshot.cs
@@ -373,7 +373,8 @@ namespace Jellyfin.Server.Implementations.Migrations
 
                     b.HasIndex("PresentationUniqueKey");
 
-                    b.HasIndex("PrimaryVersionId");
+                    b.HasIndex("PrimaryVersionId")
+                        .HasFilter("\"PrimaryVersionId\" IS NOT NULL");
 
                     b.HasIndex("SeasonId");