Module: PgHero::Methods::Tables

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

Instance Method Summary collapse

Instance Method Details

#table_caching ⇒ Object 



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

def table_caching
  select_all "    SELECT\n      relname AS table,\n      CASE WHEN heap_blks_hit + heap_blks_read = 0 THEN\n        0\n      ELSE\n        ROUND(1.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 2)\n      END AS hit_rate\n    FROM\n      pg_statio_user_tables\n    ORDER BY\n      2 DESC, 1\n  SQL\nend\n"

#table_hit_rate ⇒ Object 



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

def table_hit_rate
  select_all("    SELECT\n      sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS rate\n    FROM\n      pg_statio_user_tables\n  SQL\n  ).first[\"rate\"].to_f\nend\n"

#table_stats(options = {}) ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/pghero/methods/tables.rb', line 46

def table_stats(options = {})
  schema = options[:schema]
  tables = options[:table] ? Array(options[:table]) : nil
  select_all "    SELECT\n      nspname AS schema,\n      relname AS table,\n      reltuples::bigint\n    FROM\n      pg_class\n    INNER JOIN\n      pg_namespace ON pg_namespace.oid = pg_class.relnamespace\n    WHERE\n      relkind = 'r'\n      AND nspname = \#{quote(schema)}\n      \#{tables ? \"AND relname IN (\#{tables.map { |t| quote(t) }.join(\", \")})\" : nil}\n    ORDER BY\n      1, 2\n  SQL\nend\n"

#unused_tables ⇒ Object 



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

def unused_tables
  select_all "    SELECT\n      schemaname AS schema,\n      relname AS table,\n      n_live_tup rows_in_table\n    FROM\n      pg_stat_user_tables\n    WHERE\n      idx_scan = 0\n    ORDER BY\n      n_live_tup DESC,\n      relname ASC\n   SQL\nend\n"