Sha256: 18eb956632025887ba9172a2704dbeb68dc54eff7e03a16a0873439151682510
Contents?: true
Size: 1.71 KB
Versions: 9
Compression:
Stored size: 1.71 KB
Contents
module PgHero
module Methods
module Tables
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
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
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
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
end
end
end
Version data entries
9 entries across 9 versions & 1 rubygems