Erushenko · November 1, 2017 14:35 · Jan 30, 2012 · Jan 30, 2012 · Jan 30, 2012 · Jan 30, 2012
diff --git a/basic.sql b/basic.sql
@@ -7,4 +7,4 @@ SELECT pg_size_pretty(pg_database_size('thedbname'));
 SELECT pg_size_pretty(pg_total_relation_size('big_table'));
 SELECT pg_size_pretty(pg_relation_size('big_table')); /* without index */
 
-/* See indexes on a table with `\d feed_items` */
+/* See indexes on a table with `\d tablename` */
diff --git a/basic.sql b/basic.sql
@@ -7,6 +7,4 @@ SELECT pg_size_pretty(pg_database_size('thedbname'));
 SELECT pg_size_pretty(pg_total_relation_size('big_table'));
 SELECT pg_size_pretty(pg_relation_size('big_table')); /* without index */
 
-/* See indexes on a table */
-
-\d feed_items
+/* See indexes on a table with `\d feed_items` */
diff --git a/basic → basic.sql b/basic → basic.sql
diff --git a/basic b/basic
@@ -0,0 +1,12 @@
+/* How to calculate postgreSQL database size in disk ? */
+
+SELECT pg_size_pretty(pg_database_size('thedbname'));
+
+/* Calculate size of a table including or excluding the index */
+
+SELECT pg_size_pretty(pg_total_relation_size('big_table'));
+SELECT pg_size_pretty(pg_relation_size('big_table')); /* without index */
+
+/* See indexes on a table */
+
+\d feed_items
diff --git a/biggest_tables.txt → biggest_tables.sql b/biggest_tables.txt → biggest_tables.sql
@@ -1,4 +1,4 @@
-# Finding the total size of your biggest tables
+/* Finding the total size of your biggest tables */
 
 SELECT nspname || '.' || relname AS "relation",
     pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"

diff --git a/index_size.txt → index_size.sql b/index_size.txt → index_size.sql
@@ -1,4 +1,4 @@
-# Table & index sizes along which indexes are being scanned and how many tuples are fetched
+/* Table & index sizes along which indexes are being scanned and how many tuples are fetched */
 
 SELECT
     t.tablename,

diff --git a/index_summary.txt → index_summary.sql b/index_summary.txt → index_summary.sql
@@ -1,4 +1,4 @@
-# Pull the number of rows, indexes, and some info about those indexes for each table
+/* Pull the number of rows, indexes, and some info about those indexes for each table */
 
 SELECT
     pg_class.relname,

diff --git a/index_useless.txt → index_useless.sql b/index_useless.txt → index_useless.sql
@@ -1,3 +1,5 @@
+/* Finds indices that have not been used (the ones with 0 scans) */
+
 SELECT
  schemaname,
  relname,

diff --git a/biggest_relations.sql b/biggest_relations.sql
@@ -1,4 +1,4 @@
-// Finding the total size of your biggest tables
+/* Finding the total size of your biggest tables */
 
 SELECT nspname || '.' || relname AS "relation",
     pg_size_pretty(pg_relation_size(C.oid)) AS "size"

diff --git a/biggest_relations.sql b/biggest_relations.sql
@@ -1,4 +1,4 @@
-# Finding the total size of your biggest tables
+// Finding the total size of your biggest tables
 
 SELECT nspname || '.' || relname AS "relation",
     pg_size_pretty(pg_relation_size(C.oid)) AS "size"

diff --git a/biggest_relations.txt → biggest_relations.sql b/biggest_relations.txt → biggest_relations.sql
diff --git a/index_useless.txt b/index_useless.txt
@@ -1,21 +1,12 @@
-SELECT idstat.relname AS table_name, 
-
-    indexrelname AS index_name, 
-
-    idstat.idx_scan AS times_used,
-
-pg_size_pretty(pg_table_size(idstat.relname)) AS table_size,	pg_size_pretty(pg_table_size(indexrelname)) AS index_size,
-
-    n_tup_upd + n_tup_ins + n_tup_del as num_writes,
-
-    indexdef AS definition
-
-FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
-
-    JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
-
-WHERE idstat.idx_scan < 200
-
-    AND indexdef !~* 'unique'
-
-ORDER BY idstat.relname, indexrelname;
+SELECT
+ schemaname,
+ relname,
+ indexrelname,
+ idx_scan,
+ pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
+FROM
+ pg_stat_user_indexes i
+ JOIN pg_index USING (indexrelid)
+WHERE
+ indisunique IS false
+ORDER BY idx_scan,relname;
diff --git a/index_useless.txt b/index_useless.txt
@@ -1,9 +1,21 @@
-# Finding unused indexes
-
-select 
-    indexrelid::regclass as index, relid::regclass as table 
-from 
-    pg_stat_user_indexes 
-    JOIN pg_index USING (indexrelid) 
-where 
-    idx_scan = 0 and indisunique is false;
+SELECT idstat.relname AS table_name, 
+
+    indexrelname AS index_name, 
+
+    idstat.idx_scan AS times_used,
+
+pg_size_pretty(pg_table_size(idstat.relname)) AS table_size,	pg_size_pretty(pg_table_size(indexrelname)) AS index_size,
+
+    n_tup_upd + n_tup_ins + n_tup_del as num_writes,
+
+    indexdef AS definition
+
+FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
+
+    JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
+
+WHERE idstat.idx_scan < 200
+
+    AND indexdef !~* 'unique'
+
+ORDER BY idstat.relname, indexrelname;
diff --git a/index_useless.txt b/index_useless.txt
@@ -0,0 +1,9 @@
+# Finding unused indexes
+
+select 
+    indexrelid::regclass as index, relid::regclass as table 
+from 
+    pg_stat_user_indexes 
+    JOIN pg_index USING (indexrelid) 
+where 
+    idx_scan = 0 and indisunique is false;
diff --git a/biggest_relations.txt b/biggest_relations.txt
@@ -0,0 +1,9 @@
+# Finding the total size of your biggest tables
+
+SELECT nspname || '.' || relname AS "relation",
+    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
+  FROM pg_class C
+  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
+  ORDER BY pg_relation_size(C.oid) DESC
+  LIMIT 20;
diff --git a/biggest_tables.txt b/biggest_tables.txt
@@ -0,0 +1,11 @@
+# Finding the total size of your biggest tables
+
+SELECT nspname || '.' || relname AS "relation",
+    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
+  FROM pg_class C
+  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
+    AND C.relkind <> 'i'
+    AND nspname !~ '^pg_toast'
+  ORDER BY pg_total_relation_size(C.oid) DESC
+  LIMIT 20;
diff --git a/index_size.txt b/index_size.txt
@@ -0,0 +1,31 @@
+# Table & index sizes along which indexes are being scanned and how many tuples are fetched
+
+SELECT
+    t.tablename,
+    indexname,
+    c.reltuples AS num_rows,
+    pg_size_pretty(pg_relation_size(t.tablename::text)) AS table_size,
+    pg_size_pretty(pg_relation_size(indexrelname::text)) AS index_size,
+    CASE WHEN x.is_unique = 1  THEN 'Y'
+       ELSE 'N'
+    END AS UNIQUE,
+    idx_scan AS number_of_scans,
+    idx_tup_read AS tuples_read,
+    idx_tup_fetch AS tuples_fetched
+FROM pg_tables t
+LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
+LEFT OUTER JOIN
+       (SELECT indrelid,
+           max(CAST(indisunique AS integer)) AS is_unique
+       FROM pg_index
+       GROUP BY indrelid) x
+       ON c.oid = x.indrelid
+LEFT OUTER JOIN
+    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
+           JOIN pg_class c ON c.oid = x.indrelid
+           JOIN pg_class ipg ON ipg.oid = x.indexrelid
+           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
+    AS foo
+    ON t.tablename = foo.ctablename
+WHERE t.schemaname='public'
+ORDER BY 1,2;
diff --git a/index_summary.txt b/index_summary.txt
@@ -0,0 +1,36 @@
+# Pull the number of rows, indexes, and some info about those indexes for each table
+
+SELECT
+    pg_class.relname,
+    pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
+    pg_class.reltuples AS num_rows,
+    count(indexname) AS number_of_indexes,
+    CASE WHEN x.is_unique = 1 THEN 'Y'
+       ELSE 'N'
+    END AS UNIQUE,
+    SUM(case WHEN number_of_columns = 1 THEN 1
+              ELSE 0
+            END) AS single_column,
+    SUM(case WHEN number_of_columns IS NULL THEN 0
+             WHEN number_of_columns = 1 THEN 0
+             ELSE 1
+           END) AS multi_column
+FROM pg_namespace 
+LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
+LEFT OUTER JOIN
+       (SELECT indrelid,
+           max(CAST(indisunique AS integer)) AS is_unique
+       FROM pg_index
+       GROUP BY indrelid) x
+       ON pg_class.oid = x.indrelid
+LEFT OUTER JOIN
+    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns FROM pg_index x
+           JOIN pg_class c ON c.oid = x.indrelid
+           JOIN pg_class ipg ON ipg.oid = x.indexrelid  )
+    AS foo
+    ON pg_class.relname = foo.ctablename
+WHERE 
+     pg_namespace.nspname='public'
+AND  pg_class.relkind = 'r'
+GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
+ORDER BY 2;