SQL запросы для анализа PostgreSQL

Краткие запросы для анализа Использование и настройка PostgreSQL. Расшифровка здесь: Views сборщик статистики.

• Вывести запросы отсортированные по длительности выполнения

  SELECT datname,client_addr,now()-query_start as duration,current_query from pg_stat_activity 
  order by duration DESC;

• Вывести соотношение hit/read. Отношение должно лежать в пределах 5000-10000 для небольших баз

  SELECT datname, case when blks_read = 0 then 0 else blks_hit/blks_read end as ratio from pg_stat_database;

• Отсортировать список наиболее загруженных таблиц по операциям insert, update, delete

  SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables order by n_tup_ins desc;

• Вывести статистику по индексам. Если разница(столбец diff) большой значит индекс устарел и ссылается на уже несуществующие данные - необходимо перестроить данный индекс.

  SELECT relname,indexrelname,idx_tup_read,idx_tup_fetch,(idx_tup_read-idx_tup_fetch) as diff, 
  CASE WHEN idx_tup_read=0 THEN 0 ELSE (idx_tup_read::float4-idx_tup_fetch)/idx_tup_read END as r
  FROM pg_stat_user_indexes ORDER BY r desc;

• Когда происходили последний раз процессы autovacuum, autoanalyze:

  SELECT relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze 
  FROM pg_stat_all_tables where schemaname='public' ORDER BY relname;

  SELECT schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze
  FROM pg_stat_all_tables ORDER BY schemaname, relname;

• Индекс в базах данных Postgresql: INDEX, REINDEX. HOT. Данный запрос показывает размер объектов в базе данных (например, таблиц и индексов), исключая pg_catalog information_schema.

  SELECT C.oid,C.relfilenode, nspname, relname AS "relation",
  relkind, pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC;