Module: PgHero::Methods::Indexes

Included in:
Database
Defined in:
lib/pghero/methods/indexes.rb

Instance Method Summary collapse

Instance Method Details

#duplicate_indexes ⇒ Object 



142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/pghero/methods/indexes.rb', line 142

def duplicate_indexes
  indexes = []

  indexes_by_table = self.indexes.group_by { |i| i["table"] }
  indexes_by_table.values.flatten.select { |i| PgHero.falsey?(i["primary"]) && PgHero.falsey?(i["unique"]) && !i["indexprs"] && !i["indpred"] && PgHero.truthy?(i["valid"]) }.each do |index|
    covering_index = indexes_by_table[index["table"]].find { |i| index_covers?(i["columns"], index["columns"]) && i["using"] == index["using"] && i["name"] != index["name"] && i["schema"] == index["schema"] && !i["indexprs"] && !i["indpred"] && PgHero.truthy?(i["valid"]) }
    if covering_index && (covering_index["columns"] != index["columns"] || index["name"] > covering_index["name"])
      indexes << {"unneeded_index" => index, "covering_index" => covering_index}
    end
  end

  indexes.sort_by { |i| ui = i["unneeded_index"]; [ui["table"], ui["columns"]] }
end

#index_caching ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/pghero/methods/indexes.rb', line 14

def index_caching
  select_all "    SELECT\n      indexrelname AS index,\n      relname AS table,\n      CASE WHEN idx_blks_hit + idx_blks_read = 0 THEN\n        0\n      ELSE\n        ROUND(1.0 * idx_blks_hit / (idx_blks_hit + idx_blks_read), 2)\n      END AS hit_rate\n    FROM\n      pg_statio_user_indexes\n    ORDER BY\n      3 DESC, 1\n  SQL\nend\n"

#index_hit_rate ⇒ Object 



4
5
6
7
8
9
10
11
12
 
# File 'lib/pghero/methods/indexes.rb', line 4

def index_hit_rate
  select_all("    SELECT\n      (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) AS rate\n    FROM\n      pg_statio_user_indexes\n  SQL\n  ).first[\"rate\"].to_f\nend\n"

#index_usage ⇒ Object 



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/pghero/methods/indexes.rb', line 31

def index_usage
  select_all "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      CASE idx_scan\n        WHEN 0 THEN 'Insufficient data'\n        ELSE (100 * idx_scan / (seq_scan + idx_scan))::text\n      END percent_of_times_index_used,\n      n_live_tup rows_in_table\n    FROM\n      pg_stat_user_tables\n    ORDER BY\n      n_live_tup DESC,\n      relname ASC\n   SQL\nend\n"

#indexes ⇒ Object

TODO parse array properly stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/pghero/methods/indexes.rb', line 114

def indexes
  select_all("    SELECT\n      schemaname AS schema,\n      t.relname AS table,\n      ix.relname AS name,\n      regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\\\\(]*\\\\((.*)\\\\)$', '\\\\1') AS columns,\n      regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \\\\(.*', '\\\\1') AS using,\n      indisunique AS unique,\n      indisprimary AS primary,\n      indisvalid AS valid,\n      indexprs::text,\n      indpred::text,\n      pg_get_indexdef(i.indexrelid) AS definition\n    FROM\n      pg_index i\n    INNER JOIN\n      pg_class t ON t.oid = i.indrelid\n    INNER JOIN\n      pg_class ix ON ix.oid = i.indexrelid\n    LEFT JOIN\n      pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid\n    ORDER BY\n      1, 2\n  SQL\n  ).map { |v| v[\"columns\"] = v[\"columns\"].sub(\") WHERE (\", \" WHERE \").split(\", \").map { |c| unquote(c) }; v }\nend\n"

#invalid_indexes ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/pghero/methods/indexes.rb', line 92

def invalid_indexes
  select_all "    SELECT\n      c.relname AS index\n    FROM\n      pg_catalog.pg_class c,\n      pg_catalog.pg_namespace n,\n      pg_catalog.pg_index i\n    WHERE\n      i.indisvalid = false\n      AND i.indexrelid = c.oid\n      AND c.relnamespace = n.oid\n      AND n.nspname != 'pg_catalog'\n      AND n.nspname != 'information_schema'\n      AND n.nspname != 'pg_toast'\n    ORDER BY\n      c.relname\n  SQL\nend\n"

#missing_indexes ⇒ Object 



49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/pghero/methods/indexes.rb', line 49

def missing_indexes
  select_all "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      CASE idx_scan\n        WHEN 0 THEN 'Insufficient data'\n        ELSE (100 * idx_scan / (seq_scan + idx_scan))::text\n      END percent_of_times_index_used,\n      n_live_tup rows_in_table\n    FROM\n      pg_stat_user_tables\n    WHERE\n      idx_scan > 0\n      AND (100 * idx_scan / (seq_scan + idx_scan)) < 95\n      AND n_live_tup >= 10000\n    ORDER BY\n      n_live_tup DESC,\n      relname ASC\n   SQL\nend\n"

#unused_indexes ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/pghero/methods/indexes.rb', line 71

def unused_indexes
  select_all "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      indexrelname AS index,\n      pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,\n      idx_scan as index_scans\n    FROM\n      pg_stat_user_indexes ui\n    INNER JOIN\n      pg_index i ON ui.indexrelid = i.indexrelid\n    WHERE\n      NOT indisunique\n      AND idx_scan < 50\n    ORDER BY\n      pg_relation_size(i.indexrelid) DESC,\n      relname ASC\n  SQL\nend\n"