indexes.rb in pghero-2.0.3

- old
+ new

@@ -66,12 +66,12 @@
             n_live_tup DESC,
             relname ASC
          SQL
       end
 
-      def unused_indexes(max_scans: 50)
-        select_all_size <<-SQL
+      def unused_indexes(max_scans: 50, across: [])
+        result = select_all_size <<-SQL
           SELECT
             schemaname AS schema,
             relname AS table,
             indexrelname AS index,
             pg_relation_size(i.indexrelid) AS size_bytes,
@@ -85,10 +85,19 @@
             AND idx_scan <= #{max_scans.to_i}
           ORDER BY
             pg_relation_size(i.indexrelid) DESC,
             relname ASC
         SQL
+
+        across.each do |database_id|
+          database = PgHero.databases.values.find { |d| d.id == database_id }
+          raise PgHero::Error, "Database not found: #{database_id}" unless database
+          across_result = Set.new(database.unused_indexes(max_scans: max_scans).map { |v| [v[:schema], v[:index]] })
+          result.select! { |v| across_result.include?([v[:schema], v[:index]]) }
+        end
+
+        result
       end
 
       def reset_stats
         execute("SELECT pg_stat_reset()")
         true
@@ -106,11 +115,13 @@
       end
 
       def invalid_indexes
         select_all <<-SQL
           SELECT
-            c.relname AS index
+            n.nspname AS schema,
+            c.relname AS index,
+            pg_get_indexdef(i.indexrelid) AS definition
           FROM
             pg_catalog.pg_class c,
             pg_catalog.pg_namespace n,
             pg_catalog.pg_index i
           WHERE
@@ -295,12 +306,15 @@
             nspname AS schema,
             table_name AS table,
             index_name AS index,
             wastedbytes AS bloat_bytes,
             totalbytes AS index_bytes,
-            pg_get_indexdef(indexrelid) AS definition
+            pg_get_indexdef(rb.indexrelid) AS definition,
+            indisprimary AS primary
           FROM
-            raw_bloat
+            raw_bloat rb
+          INNER JOIN
+            pg_index i ON i.indexrelid = rb.indexrelid
           WHERE
             wastedbytes >= #{min_size.to_i}
           ORDER BY
             wastedbytes DESC,
             index_name