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 <<~SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      CASE WHEN heap_blks_hit + heap_blks_read = 0 THEN
        0
      ELSE
        ROUND(1.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read), 2)
      END AS hit_rate
    FROM
      pg_statio_user_tables
    ORDER BY
      2 DESC, 1
  SQL
end

#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 <<~SQL
    SELECT
      sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS rate
    FROM
      pg_statio_user_tables
  SQL
end

#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 <<~SQL
    SELECT
      nspname AS schema,
      relname AS table,
      reltuples::bigint AS estimated_rows,
      pg_total_relation_size(pg_class.oid) AS size_bytes
    FROM
      pg_class
    INNER JOIN
      pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE
      relkind = 'r'
      #{schema ? "AND nspname = #{quote(schema)}" : nil}
      #{table ? "AND relname IN (#{Array(table).map { |t| quote(t) }.join(", ")})" : nil}
    ORDER BY
      1, 2
  SQL
end

#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 <<~SQL
    SELECT
      schemaname AS schema,
      relname AS table,
      n_live_tup AS estimated_rows
    FROM
      pg_stat_user_tables
    WHERE
      idx_scan = 0
    ORDER BY
      n_live_tup DESC,
      relname ASC
   SQL
end