WITH ranked AS ( SELECT tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at ASC) AS rn_first, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at DESC) AS rn_last FROM observability.db_top_queries WHERE tenant_key IN ('test') AND collected_at BETWEEN '2026-03-23 17:27:01+01' AND '2026-03-30 17:27:01+02' ), snapshots AS ( SELECT tenant_key, fingerprint, MAX(query) FILTER (WHERE rn_last = 1) AS query, MAX(collected_at) FILTER (WHERE rn_last = 1) AS last_seen, MAX(total_time_ms) FILTER (WHERE rn_first = 1) AS first_total_time_ms, MAX(total_time_ms) FILTER (WHERE rn_last = 1) AS last_total_time_ms, MAX(calls) FILTER (WHERE rn_first = 1) AS first_calls, MAX(calls) FILTER (WHERE rn_last = 1) AS last_calls, MAX(rows_returned) FILTER (WHERE rn_first = 1) AS first_rows_returned, MAX(rows_returned) FILTER (WHERE rn_last = 1) AS last_rows_returned FROM ranked GROUP BY tenant_key, fingerprint ), deltas AS ( SELECT tenant_key, fingerprint, query, last_seen, GREATEST (COALESCE(last_total_time_ms, 0) - COALESCE(first_total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(last_calls, 0) - COALESCE(first_calls, 0), 0) AS calls, GREATEST (COALESCE(last_rows_returned, 0) - COALESCE(first_rows_returned, 0), 0) AS rows_returned FROM snapshots ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-03-30 17:27:03
Duration: 2s108ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-03-30_010000.csv

WITH ranked AS ( SELECT tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at ASC) AS rn_first, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at DESC) AS rn_last FROM observability.db_top_queries WHERE tenant_key IN ('prod') AND collected_at BETWEEN '2026-03-23 17:25:41+01' AND '2026-03-30 17:25:41+02' ), snapshots AS ( SELECT tenant_key, fingerprint, MAX(query) FILTER (WHERE rn_last = 1) AS query, MAX(collected_at) FILTER (WHERE rn_last = 1) AS last_seen, MAX(total_time_ms) FILTER (WHERE rn_first = 1) AS first_total_time_ms, MAX(total_time_ms) FILTER (WHERE rn_last = 1) AS last_total_time_ms, MAX(calls) FILTER (WHERE rn_first = 1) AS first_calls, MAX(calls) FILTER (WHERE rn_last = 1) AS last_calls, MAX(rows_returned) FILTER (WHERE rn_first = 1) AS first_rows_returned, MAX(rows_returned) FILTER (WHERE rn_last = 1) AS last_rows_returned FROM ranked GROUP BY tenant_key, fingerprint ), deltas AS ( SELECT tenant_key, fingerprint, query, last_seen, GREATEST (COALESCE(last_total_time_ms, 0) - COALESCE(first_total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(last_calls, 0) - COALESCE(first_calls, 0), 0) AS calls, GREATEST (COALESCE(last_rows_returned, 0) - COALESCE(first_rows_returned, 0), 0) AS rows_returned FROM snapshots ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-03-30 17:25:43
Duration: 2s102ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-03-30_010000.csv

WITH ranked AS ( SELECT tenant_key, fingerprint, query, total_time_ms, calls, rows_returned, collected_at, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at ASC) AS rn_first, ROW_NUMBER() OVER (PARTITION BY tenant_key, fingerprint ORDER BY collected_at DESC) AS rn_last FROM observability.db_top_queries WHERE tenant_key IN ('prod') AND collected_at BETWEEN '2026-03-23 17:28:21+01' AND '2026-03-30 17:28:21+02' ), snapshots AS ( SELECT tenant_key, fingerprint, MAX(query) FILTER (WHERE rn_last = 1) AS query, MAX(collected_at) FILTER (WHERE rn_last = 1) AS last_seen, MAX(total_time_ms) FILTER (WHERE rn_first = 1) AS first_total_time_ms, MAX(total_time_ms) FILTER (WHERE rn_last = 1) AS last_total_time_ms, MAX(calls) FILTER (WHERE rn_first = 1) AS first_calls, MAX(calls) FILTER (WHERE rn_last = 1) AS last_calls, MAX(rows_returned) FILTER (WHERE rn_first = 1) AS first_rows_returned, MAX(rows_returned) FILTER (WHERE rn_last = 1) AS last_rows_returned FROM ranked GROUP BY tenant_key, fingerprint ), deltas AS ( SELECT tenant_key, fingerprint, query, last_seen, GREATEST (COALESCE(last_total_time_ms, 0) - COALESCE(first_total_time_ms, 0), 0) AS total_time_ms, GREATEST (COALESCE(last_calls, 0) - COALESCE(first_calls, 0), 0) AS calls, GREATEST (COALESCE(last_rows_returned, 0) - COALESCE(first_rows_returned, 0), 0) AS rows_returned FROM snapshots ) SELECT fingerprint, MAX(query) AS query, MAX(last_seen) AS last_seen, SUM(total_time_ms) AS total_time_ms, SUM(calls) AS calls, SUM(rows_returned) AS rows_returned, CASE WHEN SUM(calls) > 0 THEN SUM(total_time_ms) / SUM(calls) ELSE 0 END AS mean_time_ms, array_agg(DISTINCT tenant_key ORDER BY tenant_key) AS tenant_keys FROM deltas WHERE total_time_ms > 0 OR calls > 0 GROUP BY fingerprint ORDER BY total_time_ms DESC, calls DESC LIMIT '20' OFFSET '0';

Date: 2026-03-30 17:28:23
Duration: 1s608ms
Database: zemm_monitoring
User: labanimal
Log file: /var/log/postgresql/postgresql-2026-03-30_010000.csv

x Hide