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;
  • indeks_v_bazax_dannyx: 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;
PQ VPS сервера в 28+ странах.