Fix favorite filter performance

1 files changed, 5 insertions, 2 deletions

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)