Postgres: Полезные системные запросы

Большая часть данного материала взята мной с замечательного сайта -
http://wiki.postgresqlrussia.org/

Однако некоторые запросы были собраны и из других источников.

-- Размер БД
SELECT pg_size_pretty(pg_database_size('<db_name>'));

-- Список таблиц с размерами в kB, MB
SELECT tableName,
    pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size
    FROM pg_tables
    WHERE tableName NOT LIKE 'sql_%'
    ORDER BY size;

-- Список таблиц со схемами и размерами
SELECT schemaname || '.' || tablename as name,
-- incl. toasted and indx
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as totalsize,
-- pure size (w/o toast and indexes)
    pg_size_pretty(pg_relation_size(schemaname ||  '.' || tablename)) as relsize
   FROM pg_tables
   WHERE schemaname <> 'information_schema'

-- Список индексов со схемами и размерами
SELECT schemaname || '.' || indexname as name,
    pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) as idxsize
    FROM pg_indexes
    WHERE schemaname <> 'information_schema';


-- Список текущих блокировок
SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid
    FROM pg_locks AS l
    LEFT JOIN pg_database AS d ON l.database= d.oid
    LEFT JOIN pg_class AS c ON l.relation = c.oid;

-- Список блокировок БД по пользователям
SELECT a.usename, count(l.pid) FROM pg_locks l
    INNER JOIN pg_stat_activity a
    ON a.procpid = l.pid
    WHERE NOT (mode = 'AccessShareLock')
    GROUP BY a.usename;

-- Список блокировок по пользователям и по типам
SELECT a.usename, count(l.pid), mode
    FROM pg_locks l INNER JOIN pg_stat_activity a
    ON a.procpid = l.pid
    GROUP BY a.usename, mode order by mode, count(l.pid);

-- Список запросов с эксклюзивными блокировками
SELECT a.usename, a.current_query, mode
    FROM pg_locks l
    INNER JOIN pg_stat_activity a
    ON a.procpid = l.pid WHERE mode ILIKE '%exclusive%';

-- Отношение hit / read (включить stats_block_level и stats_row_level)
SELECT datname,
    CASE
      WHEN blks_read = 0 THEN 0
      ELSE blks_hit / blks_read
    END AS ratio
    FROM pg_stat_database;

-- Количество модификаций, произошедших в таблице
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
    FROM pg_stat_user_tables
    ORDER BY n_tup_upd DESC;

-- Статистика seq scan / index scan
SELECT relname, seq_scan, idx_scan,
    CASE
      WHEN idx_scan = 0 THEN 100
      ELSE seq_scan / idx_scan
    END AS ratio
    FROM pg_stat_user_tables
    ORDER BY ratio DESC;

-- Статистика по использованию индексов
SELECT indexrelname, idx_tup_read, idx_tup_fetch,
    (idx_tup_read - idx_tup_fetch),
    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;

-- Выполняющиеся запросы с их продолжительностью
SELECT datname, NOW() - query_start AS duration,
    procpid, current_query
    FROM pg_stat_activity
    ORDER BY duration DESC;

-- Количество модификаций в таблицах
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
    FROM pg_stat_user_tables
    ORDER BY n_tup_upd DESC;