Полезные запросы в PostgreSQL

Оригинал: https://spilva.tk/?p=147

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

-- Список таблиц с размерами в 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,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as totalsize, -- incl. TOASTed and indx
    pg_size_pretty(pg_relation_size(schemaname ||  '.' || tablename)) as relsize -- pure size (w/o TOAST and indexes)
   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;

-- Посмотреть код хранимой процедуры
SELECT prosrc FROM pg_proc WHERE proname = 'procedure_name';
Получить результат запроса без форматирования можно так:

psql -qAtX -F: -c "< query >"