# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1754918416 -7200
# Node ID 9734488b55c0b9be20e7349e5bb3cf27725a2eaf
# Parent  579c1c1a28792b4ab5f237107082aee941bc8918
Optimized UpdateStatistics and DeleteResource

diff -r 579c1c1a2879 -r 9734488b55c0 PostgreSQL/NEWS
--- a/PostgreSQL/NEWS	Mon Aug 11 13:14:00 2025 +0200
+++ b/PostgreSQL/NEWS	Mon Aug 11 15:20:16 2025 +0200
@@ -26,6 +26,8 @@
 
 Maintenance:
 * Optimized the CreateInstance SQL query.
+* Optimized UpdateStatistics and DeleteResource as described in 
+  https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6
 * Internals: 
   - The PatientRecyclingOrder has been refactored and is now stored in
     Patient metadata (18: IsProtected, 19: PatientRecyclingOrder)
diff -r 579c1c1a2879 -r 9734488b55c0 PostgreSQL/Plugins/SQL/Downgrades/Rev6ToRev5.sql
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev6ToRev5.sql	Mon Aug 11 13:14:00 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev6ToRev5.sql	Mon Aug 11 15:20:16 2025 +0200
@@ -242,6 +242,34 @@
 DROP INDEX IF EXISTS AuditLogsSourcePlugin;
 DROP TABLE IF EXISTS AuditLogs;
 
+-- Remove the InvlalidChildCountsId index
+DROP INDEX IF EXISTS InvlalidChildCountsId;
+
+-- Restore the previous UpdateSingleStatistics function
+CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
+    IN statistics_key INTEGER,
+    OUT new_value BIGINT
+) AS $body$
+BEGIN
+
+  -- Delete the current changes, sum them and update the GlobalIntegers row.
+  -- New rows can be added in the meantime, they won't be deleted or summed.
+  WITH deleted_rows AS (
+      DELETE FROM GlobalIntegersChanges
+      WHERE GlobalIntegersChanges.key = statistics_key
+      RETURNING value
+  )
+  UPDATE GlobalIntegers
+  SET value = value + (
+      SELECT COALESCE(SUM(value), 0)
+      FROM deleted_rows
+  )
+  WHERE GlobalIntegers.key = statistics_key
+  RETURNING value INTO new_value;
+
+END;
+$body$ LANGUAGE plpgsql;
+
 
 ----------
 
diff -r 579c1c1a2879 -r 9734488b55c0 PostgreSQL/Plugins/SQL/PrepareIndex.sql
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Aug 11 13:14:00 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Aug 11 15:20:16 2025 +0200
@@ -411,9 +411,15 @@
 
   -- Delete the current changes, sum them and update the GlobalIntegers row.
   -- New rows can be added in the meantime, they won't be deleted or summed.
-  WITH deleted_rows AS (
+  WITH rows_to_delete AS (
+    SELECT ctid
+    FROM GlobalIntegersChanges
+    WHERE GlobalIntegersChanges.key = statistics_key
+    LIMIT 10000                  -- by default, the UpdateSingleStatistics is called every seconds -> we should never get more than 10000 entries to compute so this is mainly useful to catch up with long standing entries from previous plugins version without the Housekeeping thread (see https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6)
+  ), 
+  deleted_rows AS (
       DELETE FROM GlobalIntegersChanges
-      WHERE GlobalIntegersChanges.key = statistics_key
+      WHERE GlobalIntegersChanges.ctid IN (SELECT ctid FROM rows_to_delete)
       RETURNING value
   )
   UPDATE GlobalIntegers
@@ -701,6 +707,8 @@
     id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
     updatedAt TIMESTAMP DEFAULT NOW());
 
+-- note: an index has been added in rev6
+
 -- Updates the Resources.childCount column with the delta that have not been committed yet.
 -- A thread will call this function at regular interval to update all pending values.
 CREATE OR REPLACE FUNCTION UpdateInvalidChildCounts(
@@ -839,6 +847,8 @@
 CREATE INDEX IF NOT EXISTS AuditLogsAction ON AuditLogs (action);
 CREATE INDEX IF NOT EXISTS AuditLogsSourcePlugin ON AuditLogs (sourcePlugin);
 
+CREATE INDEX IF NOT EXISTS InvalidChildCountsId ON InvalidChildCounts (id); -- see https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6
+
 
 
 -- set the global properties that actually documents the DB version, revision and some of the capabilities