adding in active queries and table sizes

cmgoffena13 · May 13, 2023 · fe49d63 · fe49d63
1 parent bd4e709
commit fe49d63
Show file tree

Hide file tree

Showing 2 changed files with 55 additions and 0 deletions.
diff --git a/Active_Query_Report.sql b/Active_Query_Report.sql
@@ -0,0 +1,26 @@
+/*====================================================================================================
+Active Queries - Cortland Goffena
+
+Shows information about current running queries in all databases.
+====================================================================================================*/
+
+SELECT
+datname AS "DatabaseName",
+pid AS "PID",
+usename AS "Username",
+application_name AS "ApplicationName",
+--client_addr AS "ClientIPAddress",
+--client_hostname AS "ClientHostName",
+client_port AS "ClientPort",
+state AS "ProcessState",
+backend_start AS "ProcessStart",
+xact_start AS "TransactionStart",
+query_start AS "QueryStart",
+--query_id AS "QueryID",
+query AS "QueryText",
+wait_event_type AS "WaitEventType",
+wait_event AS "WaitEvent"
+FROM pg_stat_activity
+WHERE pid != pg_backend_pid()
+AND wait_event_type != 'Activity'
+AND state = 'active'
diff --git a/Table_Sizes_Report.sql b/Table_Sizes_Report.sql
@@ -0,0 +1,29 @@
+/*====================================================================================================
+Table Stats - Cortland Goffena
+
+Shows space size and record size.
+Gives index percent of the total space.
+====================================================================================================*/
+
+SELECT
+t.schemaname || '.' || t.relname AS "TableName",
+t.n_live_tup AS "RecordCount",
+pg_size_pretty(pg_total_relation_size(t.schemaname || '.' || t.relname)) AS "TableTotalSize",
+pg_size_pretty(pg_relation_size(t.schemaname || '.' || t.relname)) AS "TableDataSize",
+CASE WHEN pg_total_relation_size(t.schemaname || '.' || t.relname) > 0 
+	 AND pg_relation_size(t.schemaname || '.' || t.relname) > 0
+	THEN CAST(
+			CAST(pg_relation_size(t.schemaname || '.' || t.relname) AS NUMERIC(19,4)) / 
+				CAST(pg_total_relation_size(t.schemaname || '.' || t.relname) AS NUMERIC(19,4)) 
+		AS NUMERIC(19,4))
+	ELSE 0 END AS "DataSizePercent",
+pg_size_pretty(pg_indexes_size(t.schemaname || '.' || t.relname)) AS "TableIndexSize",
+CASE WHEN pg_total_relation_size(t.schemaname || '.' || t.relname) > 0 
+	 AND pg_indexes_size(t.schemaname || '.' || t.relname) > 0
+	THEN CAST(
+			CAST(pg_indexes_size(t.schemaname || '.' || t.relname) AS NUMERIC(19,4)) / 
+				CAST(pg_total_relation_size(t.schemaname || '.' || t.relname) AS NUMERIC(19,4)) 
+		AS NUMERIC(19,4))
+	ELSE 0 END AS "IndexSizePercent"
+FROM pg_stat_user_tables AS t
+ORDER BY t.n_live_tup DESC;