/
table_cache_hit.ex
39 lines (36 loc) · 1.08 KB
/
table_cache_hit.ex
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
defmodule EctoPSQLExtras.TableCacheHit do
@behaviour EctoPSQLExtras
def info do
%{
title: "Calculates your cache hit rate for reading tables",
index: 2,
order_by: [ratio: :desc],
columns: [
%{name: :schema, type: :string},
%{name: :name, type: :string},
%{name: :buffer_hits, type: :integer},
%{name: :block_reads, type: :integer},
%{name: :total_read, type: :integer},
%{name: :ratio, type: :numeric}
]
}
end
def query(_args \\ []) do
"""
/* ECTO_PSQL_EXTRAS: Calculates your cache hit rate for reading tables */
SELECT
schemaname AS schema, relname AS name,
heap_blks_hit AS buffer_hits,
heap_blks_read AS block_reads,
heap_blks_hit + heap_blks_read AS total_read,
CASE (heap_blks_hit + heap_blks_read)::float
WHEN 0 THEN NULL
ELSE (heap_blks_hit / (heap_blks_hit + heap_blks_read)::float)
END ratio
FROM
pg_statio_user_tables
ORDER BY
heap_blks_hit / (heap_blks_hit + heap_blks_read + 1)::float DESC;
"""
end
end