From c5f5377e00b64058b5c04ca82cefd6cb646a3638 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sun, 27 Sep 2026 16:30:59 -0400 Subject: 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 Backported-by: Cody Robibero --- .../IJellyfinDatabaseProvider.cs | 9 +++ .../SqliteDatabaseProvider.cs | 81 ++++++++++++++++++---- 2 files changed, 77 insertions(+), 13 deletions(-) (limited to 'src') 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 @@ -44,6 +44,15 @@ public interface IJellyfinDatabaseProvider /// A representing the asynchronous operation. Task RunScheduledOptimisation(CancellationToken cancellationToken); + /// + /// If supported this should refresh the query planner statistics, e.g. after a library scan changed the data. + /// Unlike it should not reclaim space, so that it stays + /// cheap enough to run after every scan. + /// + /// The token to abort the operation. + /// A representing the asynchronous operation. + Task RefreshStatistics(CancellationToken cancellationToken) => Task.CompletedTask; + /// /// 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 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 @@ -109,6 +109,35 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider return OptimizeAsync(cancellationToken); } + /// + 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); + } + } + } + /// public void OnModelCreating(ModelBuilder modelBuilder) { @@ -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 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 ReadPragmaValueAsync(DbCommand command, CancellationToken cancellationToken) { var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false); -- cgit v1.2.3