Skip to content

postgresql top 10 tables by io operations

postgresql top 10 tables by io operations published on Комментариев к записи postgresql top 10 tables by io operations нет

helps to keep focus on «hot» tables
top 10 tables by sum of io operatoins:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
st.schemaname||'.'||st.relname,
round (100*(
        sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0))
)
    /
(
        sum (coalesce(st.n_tup_ins,0)+coalesce(st.n_tup_upd,0)-coalesce(st.n_tup_hot_upd,0)+coalesce(st.n_tup_del,0)) over ()
),2) as pct_io_ops
 from pg_stat_user_tables st
JOIN pg_class c ON c.oid=st.relid
LEFT JOIN pg_tablespace t ON t.oid=c.reltablespace
WHERE
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)-coalesce(n_tup_hot_upd,0)+coalesce(n_tup_del,0)>100
group by st.schemaname||'.'||st.relname,st.n_tup_ins,st.n_tup_upd,st.n_tup_hot_upd,st.n_tup_del
order by pct_io_ops desc
limit 10;

top 10 tables by index scans:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
     pg_stat_all_tables.schemaname||'.'||relid::regclass AS table,
     round (100* (sum(pg_stat_user_indexes.idx_scan))
                   / (sum(pg_stat_user_indexes.idx_scan) over () )
                    ,2) pct_indx_scans
 FROM
     pg_stat_user_indexes
     JOIN pg_index USING (indexrelid)
     join pg_stat_all_tables using (relid)
     group by pg_stat_all_tables.schemaname||'.'||relid::regclass,pg_stat_user_indexes.idx_scan
     order by 2 desc
     limit 10;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Primary Sidebar