Backport pull request #18196 from jellyfin/release-12.z

Skip statistics on an empty library and refresh them after every scan

Original-merge: 83b89218e54754b22831399d592cd0f4b6185812

Merged-by: crobibero <cody@robibe.ro>

Backported-by: Cody Robibero <cody@robibe.ro>

4 files changed, 246 insertions, 13 deletions

diff --git a/Emby.Server.Implementations/Data/RefreshDatabaseStatisticsPostScanTask.cs b/Emby.Server.Implementations/Data/RefreshDatabaseStatisticsPostScanTask.cs
new file mode 100644
index 0000000000..be1fe660c9
--- /dev/null
+++ b/Emby.Server.Implementations/Data/RefreshDatabaseStatisticsPostScanTask.cs
@@ -0,0 +1,35 @@
+using System;
+using System.Threading;
+using System.Threading.Tasks;
+using Jellyfin.Database.Implementations;
+using MediaBrowser.Controller.Library;
+
+namespace Emby.Server.Implementations.Data;
+
+/// <summary>
+/// Refreshes the database statistics after every library scan.
+/// </summary>
+/// <remarks>
+/// The scheduled optimization skips itself while a scan runs, so without this the first scan of a new server
+/// leaves every query planned for an empty library until the next scheduled run.
+/// </remarks>
+public class RefreshDatabaseStatisticsPostScanTask : ILibraryPostScanTask
+{
+    private readonly IJellyfinDatabaseProvider _databaseProvider;
+
+    /// <summary>
+    /// Initializes a new instance of the <see cref="RefreshDatabaseStatisticsPostScanTask"/> class.
+    /// </summary>
+    /// <param name="databaseProvider">The database provider.</param>
+    public RefreshDatabaseStatisticsPostScanTask(IJellyfinDatabaseProvider databaseProvider)
+    {
+        _databaseProvider = databaseProvider;
+    }
+
+    /// <inheritdoc />
+    public async Task Run(IProgress<double> progress, CancellationToken cancellationToken)
+    {
+        await _databaseProvider.RefreshStatistics(cancellationToken).ConfigureAwait(false);
+        progress.Report(100);
+    }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
index 0a72287ba9..6efced6556 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
@@ -45,6 +45,15 @@ public interface IJellyfinDatabaseProvider
     Task RunScheduledOptimisation(CancellationToken cancellationToken);
 
     /// <summary>
+    /// If supported this should refresh the query planner statistics, e.g. after a library scan changed the data.
+    /// Unlike <see cref="RunScheduledOptimisation(CancellationToken)"/> it should not reclaim space, so that it stays
+    /// cheap enough to run after every scan.
+    /// </summary>
+    /// <param name="cancellationToken">The token to abort the operation.</param>
+    /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
+    Task RefreshStatistics(CancellationToken cancellationToken) => Task.CompletedTask;
+
+    /// <summary>
     /// If supported this should perform any actions that are required on stopping the jellyfin server. This runs
     /// against a deadline imposed by the service manager, so unlike
     /// <see cref="RunScheduledOptimisation(CancellationToken)"/> it should only do work whose cost does not grow with
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
index 3330b64b69..a3fbe42e02 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
@@ -110,6 +110,35 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
     }
 
     /// <inheritdoc/>
+    public async Task RefreshStatistics(CancellationToken cancellationToken)
+    {
+        if (DbContextFactory is null)
+        {
+            return;
+        }
+
+        var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
+        await using (context.ConfigureAwait(false))
+        {
+            await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
+            try
+            {
+                if (!await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false))
+                {
+                    return;
+                }
+
+                _logger.LogInformation("Analyzing jellyfin.db");
+                await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false);
+            }
+            finally
+            {
+                await context.Database.CloseConnectionAsync().ConfigureAwait(false);
+            }
+        }
+    }
+
+    /// <inheritdoc/>
     public void OnModelCreating(ModelBuilder modelBuilder)
     {
         modelBuilder.SetDefaultDateTimeKind(DateTimeKind.Utc);
@@ -161,14 +190,11 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
             try
             {
                 long? tempStore;
-                long? analysisLimit;
                 var pragmaCommand = context.Database.GetDbConnection().CreateCommand();
                 await using (pragmaCommand.ConfigureAwait(false))
                 {
                     pragmaCommand.CommandText = "PRAGMA temp_store";
                     tempStore = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false);
-                    pragmaCommand.CommandText = "PRAGMA analysis_limit";
-                    analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false);
                 }
 
                 await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
@@ -192,19 +218,15 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
                     }
                 }
 
-                await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false);
-                try
+                // Statistics taken while the library is empty make the planner treat every table as one row and
+                // pick full scans once it fills up; no statistics at all plan far better until there is data.
+                if (await HasLibraryItemsAsync(context, cancellationToken).ConfigureAwait(false))
                 {
-                    await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false);
+                    await AnalyzeAsync(context, cancellationToken).ConfigureAwait(false);
                 }
-                finally
+                else
                 {
-                    if (analysisLimit is not null)
-                    {
-                        await context.Database.ExecuteSqlRawAsync(
-                            FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"),
-                            CancellationToken.None).ConfigureAwait(false);
-                    }
+                    _logger.LogInformation("Not analyzing jellyfin.db, the library holds no items yet");
                 }
 
                 await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
@@ -217,6 +239,39 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
         }
     }
 
+    private static Task<bool> HasLibraryItemsAsync(JellyfinDbContext context, CancellationToken cancellationToken)
+    {
+        // Folders and the seeded placeholder exist before any library has been scanned.
+        return context.BaseItems.AnyAsync(e => !e.IsFolder && e.Type != "PLACEHOLDER", cancellationToken);
+    }
+
+    private static async Task AnalyzeAsync(JellyfinDbContext context, CancellationToken cancellationToken)
+    {
+        long? analysisLimit;
+        var pragmaCommand = context.Database.GetDbConnection().CreateCommand();
+        await using (pragmaCommand.ConfigureAwait(false))
+        {
+            pragmaCommand.CommandText = "PRAGMA analysis_limit";
+            analysisLimit = await ReadPragmaValueAsync(pragmaCommand, cancellationToken).ConfigureAwait(false);
+        }
+
+        await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false);
+        try
+        {
+            await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false);
+        }
+        finally
+        {
+            // The connection goes back to the pool, so hand it over the way it was handed to us.
+            if (analysisLimit is not null)
+            {
+                await context.Database.ExecuteSqlRawAsync(
+                    FormattableString.Invariant($"PRAGMA analysis_limit={analysisLimit.Value}"),
+                    CancellationToken.None).ConfigureAwait(false);
+            }
+        }
+    }
+
     private static async Task<long?> ReadPragmaValueAsync(DbCommand command, CancellationToken cancellationToken)
     {
         var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
diff --git a/tests/Jellyfin.Server.Implementations.Tests/Data/SqliteDatabaseStatisticsTests.cs b/tests/Jellyfin.Server.Implementations.Tests/Data/SqliteDatabaseStatisticsTests.cs
new file mode 100644
index 0000000000..153e1dc154
--- /dev/null
+++ b/tests/Jellyfin.Server.Implementations.Tests/Data/SqliteDatabaseStatisticsTests.cs
@@ -0,0 +1,134 @@
+using System;
+using System.Linq;
+using System.Threading;
+using System.Threading.Tasks;
+using Jellyfin.Database.Implementations.Entities;
+using Jellyfin.Database.Providers.Sqlite;
+using Jellyfin.Server.Implementations.Tests.Item;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.Extensions.Logging.Abstractions;
+using Xunit;
+
+namespace Jellyfin.Server.Implementations.Tests.Data;
+
+/// <summary>
+/// Statistics taken on a freshly created database describe every table as a single row, and SQLite then plans
+/// the user data and series queries of a filled library as full scans (#17886).
+/// </summary>
+public sealed class SqliteDatabaseStatisticsTests : SqliteDbTestFixture
+{
+    private readonly SqliteDatabaseProvider _provider;
+
+    public SqliteDatabaseStatisticsTests()
+    {
+        _provider = new SqliteDatabaseProvider(ApplicationPaths, NullLogger<SqliteDatabaseProvider>.Instance)
+        {
+            DbContextFactory = CreateDbContextFactory()
+        };
+    }
+
+    [Fact]
+    public async Task RunScheduledOptimisation_EmptyLibrary_RecordsNoStatistics()
+    {
+        SeedFolders(3);
+
+        await _provider.RunScheduledOptimisation(CancellationToken.None);
+
+        Assert.Null(ReadAnalyzedItemCount());
+    }
+
+    [Fact]
+    public async Task RunScheduledOptimisation_LibraryWithItems_RecordsStatistics()
+    {
+        SeedFolders(1);
+        SeedEpisodes(4);
+
+        await _provider.RunScheduledOptimisation(CancellationToken.None);
+
+        Assert.Equal(CountItems(), ReadAnalyzedItemCount());
+    }
+
+    [Fact]
+    public async Task RefreshStatistics_NoStatistics_Analyzes()
+    {
+        SeedEpisodes(5);
+
+        await _provider.RefreshStatistics(CancellationToken.None);
+
+        Assert.Equal(CountItems(), ReadAnalyzedItemCount());
+    }
+
+    [Fact]
+    public async Task RefreshStatistics_LibraryChanged_Reanalyzes()
+    {
+        SeedEpisodes(10);
+        Analyze();
+        SeedEpisodes(5);
+
+        await _provider.RefreshStatistics(CancellationToken.None);
+
+        Assert.Equal(CountItems(), ReadAnalyzedItemCount());
+    }
+
+    [Fact]
+    public async Task RefreshStatistics_EmptyLibrary_RecordsNoStatistics()
+    {
+        SeedFolders(2);
+
+        await _provider.RefreshStatistics(CancellationToken.None);
+
+        Assert.Null(ReadAnalyzedItemCount());
+    }
+
+    private void SeedFolders(int count)
+    {
+        using var context = CreateDbContext();
+        context.BaseItems.AddRange(Enumerable.Range(0, count).Select(_ => new BaseItemEntity
+        {
+            Id = Guid.NewGuid(),
+            Type = "MediaBrowser.Controller.Entities.Folder",
+            IsFolder = true
+        }));
+        context.SaveChanges();
+    }
+
+    private void SeedEpisodes(int count)
+    {
+        using var context = CreateDbContext();
+        context.BaseItems.AddRange(Enumerable.Range(0, count).Select(_ => new BaseItemEntity
+        {
+            Id = Guid.NewGuid(),
+            Type = "MediaBrowser.Controller.Entities.TV.Episode",
+            IsFolder = false
+        }));
+        context.SaveChanges();
+    }
+
+    private void Analyze()
+    {
+        using var context = CreateDbContext();
+        context.Database.ExecuteSqlRaw("ANALYZE");
+    }
+
+    private long CountItems()
+    {
+        using var context = CreateDbContext();
+        return context.BaseItems.LongCount();
+    }
+
+    private long? ReadAnalyzedItemCount()
+    {
+        using var context = CreateDbContext();
+        var hasStatistics = context.Database
+            .SqlQueryRaw<long>("SELECT count(*) AS \"Value\" FROM sqlite_schema WHERE type = 'table' AND name = 'sqlite_stat1'")
+            .Single();
+        if (hasStatistics == 0)
+        {
+            return null;
+        }
+
+        return context.Database
+            .SqlQueryRaw<long?>("SELECT max(CAST(stat AS INTEGER)) AS \"Value\" FROM sqlite_stat1 WHERE tbl = 'BaseItems'")
+            .Single();
+    }
+}