Postresql_Statistic.sql

-- Запросы по сбору статистики PostgreSql 9.3.x

-- Процессы которые висят более 20 секунд
select * from pg_stat_activity where state != 'idle' and current_timestamp - query_start > '20 sec';

-- К-во update-тов за время с последнего сброса статистики
SELECT relname, n_tup_ins + n_tup_upd + n_tup_del from pg_stat_user_tables order by 2 desc limit 5;

-- Сброс статистики
SELECT pg_stat_reset();

-- Наиболее читаемые таблицы в БД, а также использование индексов при чтении
SELECT relname, seq_tup_read, idx_tup_fetch, cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct 
FROM pg_stat_user_tables 
WHERE (idx_tup_fetch + seq_tup_read) > 0 
ORDER BY seq_tup_read desc;
 

-- Вывод самых крупных таблиц в БД, в схеме public
SELECT table_name, pg_relation_size(table_name) as size
FROM information_schema.tables WHERE table_schema IN ('public')
ORDER BY size DESC LIMIT 10;

-- Относительное к-во insert/update/delete запросов к таблицам (доля от 100% по каждому виду запроса)
SELECT relname,cast(n_tup_ins AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS ins_pct,cast(n_tup_upd AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS upd_pct, cast(n_tup_del AS numeric) / (n_tup_ins
+ n_tup_upd + n_tup_del) AS del_pct 
FROM pg_stat_user_tables 
WHERE (n_tup_ins + n_tup_upd + n_tup_del) > 0
ORDER BY relname;

-- Наименее используемые индексы
SELECT idstat.relname AS table_name, indexrelname AS index_name, idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(tabstat.relid)) AS table_size, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes, indexdef AS definition
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200 AND indexdef !~* 'unique'
ORDER BY times_used

-- Размеры индексов
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
  pg_stat_user_indexes i
  JOIN pg_index USING (indexrelid)
WHERE
  indisunique IS false
ORDER BY idx_scan,relname;