Полезные запросы для PostgreSQL DBA

Материал из Postgresmen Wiki.

Данная страница служит для быстрой справки, некоторые запросы из приведённых ниже подробно разобраны в статье PostgreSQL: аналитика для DBA.

Размеры объектов

• Размер БД

   SELECT pg_size_pretty(pg_database_size('<your_database_name>')); 

• Размеры 10 самых больших таблиц в БД

   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'
   ORDER BY
     pg_total_relation_size(schemaname || '.' || tablename) DESC
   LIMIT 10;

• Размеры 10 самых больших индексов в БД

   SELECT 
     schemaname || '.' || indexname as name, 
     pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) as idxsize
   FROM 
     pg_indexes
   WHERE
     schemaname <> 'information_schema'
   ORDER BY
     pg_relation_size(schemaname || '.' || indexname) DESC
   LIMIT 10;

Статистика использования

Внимание! Для корректной работы запросов этой секции необходимо включить опции stats_block_level и stats_row_level в postgresql.conf, а также настроить параметр stats_reset_on_server_start по своему усмотрению.

• Отношение hit / read

   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 
     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;