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 <<-SQL
    SELECT
      indexrelname AS index,
      relname AS table,
      CASE WHEN idx_blks_hit + idx_blks_read = 0 THEN
        0
      ELSE
        ROUND(1.0 * idx_blks_hit / (idx_blks_hit + idx_blks_read), 2)
      END AS hit_rate
    FROM
      pg_statio_user_indexes
    ORDER BY
      3 DESC, 1
  SQL
end

#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(<<-SQL
    SELECT
      (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read), 0) AS rate
    FROM
      pg_statio_user_indexes
  SQL
  ).first["rate"].to_f
end

#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 <<-SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      CASE idx_scan
        WHEN 0 THEN 'Insufficient data'
        ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
      END percent_of_times_index_used,
      n_live_tup rows_in_table
    FROM
      pg_stat_user_tables
    ORDER BY
      n_live_tup DESC,
      relname ASC
   SQL
end

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

#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 <<-SQL
    SELECT
      c.relname AS index
    FROM
      pg_catalog.pg_class c,
      pg_catalog.pg_namespace n,
      pg_catalog.pg_index i
    WHERE
      i.indisvalid = false
      AND i.indexrelid = c.oid
      AND c.relnamespace = n.oid
      AND n.nspname != 'pg_catalog'
      AND n.nspname != 'information_schema'
      AND n.nspname != 'pg_toast'
    ORDER BY
      c.relname
  SQL
end

#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 <<-SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      CASE idx_scan
        WHEN 0 THEN 'Insufficient data'
        ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
      END percent_of_times_index_used,
      n_live_tup rows_in_table
    FROM
      pg_stat_user_tables
    WHERE
      idx_scan > 0
      AND (100 * idx_scan / (seq_scan + idx_scan)) < 95
      AND n_live_tup >= 10000
    ORDER BY
      n_live_tup DESC,
      relname ASC
   SQL
end

#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 <<-SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      indexrelname AS index,
      pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
      idx_scan as index_scans
    FROM
      pg_stat_user_indexes ui
    INNER JOIN
      pg_index i ON ui.indexrelid = i.indexrelid
    WHERE
      NOT indisunique
      AND idx_scan < 50
    ORDER BY
      pg_relation_size(i.indexrelid) DESC,
      relname ASC
  SQL
end