Module: PgHero::Methods::Tables

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

Instance Method Summary collapse

Instance Method Details

#table_caching ⇒ Object 



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

def table_caching
  select_all "    SELECT\n      schemaname AS schema,\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
 
# File 'lib/pghero/methods/tables.rb', line 4

def table_hit_rate
  select_one "    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\nend\n"

#table_stats(schema: nil, table: nil) ⇒ Object 



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

def table_stats(schema: nil, table: nil)
  select_all "    SELECT\n      nspname AS schema,\n      relname AS table,\n      reltuples::bigint AS estimated_rows,\n      pg_total_relation_size(pg_class.oid) AS size_bytes\n    FROM\n      pg_class\n    INNER JOIN\n      pg_namespace ON pg_namespace.oid = pg_class.relnamespace\n    WHERE\n      relkind = 'r'\n      \#{schema ? \"AND nspname = \#{quote(schema)}\" : nil}\n      \#{table ? \"AND relname IN (\#{Array(table).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 AS estimated_rows\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"