feature: aligned the calculation of pg_wait_sampling and pgpro_stats metrics

cuprumtan · cuprumtan · commit 8e4cd387875a · 2022-04-19T15:42:49.000+03:00
diff --git a/mamonsu/plugins/pgsql/wait_sampling.py b/mamonsu/plugins/pgsql/wait_sampling.py
@@ -27,9 +27,10 @@ class WaitSampling(Plugin):
                     WHEN event_type = 'Lock' THEN 'hwlock'
                     ELSE 'buffer'
                 END,
-                sum(count) AS count
+                sum(count * current_setting('pg_wait_sampling.profile_period')::bigint) AS count
             FROM {extension_schema}.pg_wait_sampling_profile
             WHERE event_type is not null
+            AND queryid IS NOT NULL AND queryid != 0
             GROUP BY 1
             ORDER BY count DESC;
             """,
@@ -93,9 +94,10 @@ class WaitSampling(Plugin):
             """
                 SELECT
                     event,
-                    sum(count) AS count
+                    sum(count * current_setting('pg_wait_sampling.profile_period')::bigint) AS count
                 FROM {extension_schema}.pg_wait_sampling_profile
                 WHERE event_type = 'Lock'
+                AND queryid IS NOT NULL AND queryid != 0
                 GROUP BY 1
                 ORDER BY count DESC;
                 """,
@@ -157,9 +159,10 @@ class WaitSampling(Plugin):
                     WHEN event = 'buffer_mapping' THEN 'buffer'
                     ELSE 'other'
                 END,
-                sum(count) AS count
+                sum(count * current_setting('pg_wait_sampling.profile_period')::bigint) AS count
             FROM {extension_schema}.pg_wait_sampling_profile
             WHERE event_type = 'LWLockTranche' OR event_type = 'LWLockNamed'
+            AND queryid IS NOT NULL AND queryid != 0
             GROUP BY 1
             ORDER BY count DESC;
             """,
