Comments 16
>> запрос находит индексы, которые созданы, но не использовались в SQL-запросах.
За какой период? :) А то вдруг этот индекс чтоб раз в месяц запрос для бухгалтерии для отчёта делать, а тут дропнули и создали проблем на голову в конце квартала.
Обычно такая статистика ведется с момента старта инстанса постгрес.
И как верно отметили ниже - в бд не должно быть индексов, использующихся раз в квартал. Если таблица небольшая, то просто пусть будет фул скан. Если таблица такая огромная, что фул скан уходит в себя навечно, и нет подходящего частичного индекса - у вас, вероятно, проблемы с дизайном бд. Может нужно думать о секционировании и/или отгрузке данных в специализированные бд для быстрого построения отчетности.
Статистика накапливается с момента её соседнего сброса. Смотрите pg_stat_reset и аналогичные ей функции.
Делать индекс, который обслуживается 24/7, а используется раз в месяц/квартал не самая лучшая идея.
Либо просто запустить pgwatch2 Postgres.ai Edition - https://gitlab.com/postgres-ai/pgwatch2 который уже содержит множество "полезных SQL-запросов" и получить графики состояния базы данных (в Grafana) за неделю, месяц, и год (при необходимости).
Demo: https://pgwatch.postgres.ai
demo/demo
а можно детальнее о причинах форка? посмотрел историю коммитов (очень бегло), похоже патчи из апстрима вы не подтягиваете
Посмотрел предложенный вами вариант. В целом достаточно интересно, но я бы вместо "либо" написал бы "можно ещё". Статистика приведенная в графане охватывает не только пересекающиеся темы, но и то, что не описано у меня в статье. Но все же, во-первых там нет половины рассматриваемых запросов из моей статьи, а во вторых там собрана общая статистика, а мои запросы приводят информацию по отдельным элементам базы данных.
Буквально на днях пользовался таким запросом для отладки блокировок. Он покажет чуть больше инфы, чем приведенный вами в статье, например имя пользователя и его ip, если есть разграничение доступа в вашей БД, время старта запроса и тело запроса, что поможет однозначно выявить неполадку.
select
sa.usename as username,
sa.client_addr,
sa.backend_start,
sa.query_start,
sa.wait_event_type,
sa.state,
sa.query,
lock.locktype,
lock.relation::regclass as rel,
lock.mode,
lock.transactionid as tid,
lock.virtualtransaction as vtid,
lock.pid,
lock.granted
from pg_catalog.pg_locks lock
left join pg_catalog.pg_database db
on db.oid = lock.database
left join pg_catalog.pg_stat_activity sa
on lock.pid = sa.pid
where not lock.pid = pg_backend_pid()
order by lock.pid;Также пользуюсь вот таким дашбордом с postgres exporter.
Дополнение к размерам:
Размер таблиц и индексов, единым плоским списком по всей СУБД (чтобы понять что кушает больше всего)
SELECT concat(schemaname, '.', tablename, ':', indexname),
pg_relation_size(concat(schemaname, '.', indexname)::regclass) as size,
pg_size_pretty(pg_relation_size(concat(schemaname, '.', indexname)::regclass)) AS pretty_size
FROM pg_indexes
UNION
SELECT concat(schemaname, '.', relname),
pg_table_size(relid) as size,
pg_size_pretty(pg_table_size(relid)) AS pretty_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY 2 DESC
Размер одной таблицы суммарный и в пересчёте на одну строку (удобно для прикинуть сколько занимает одна запись на диске)
SELECT l.metric, l.nr AS bytes
, CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
, CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM (
SELECT min(tableoid) AS tbl -- = 'public.tbl'::regclass::oid
, count(*) AS ct
, sum(length(t::text)) AS txt_len -- length in characters
FROM partners t -- заменить здесь на имя таблицы, которую нужно проанализировать
) x
CROSS JOIN LATERAL (
VALUES
(true , 'core_relation_size' , pg_relation_size(tbl))
, (true , 'visibility_map' , pg_relation_size(tbl, 'vm'))
, (true , 'free_space_map' , pg_relation_size(tbl, 'fsm'))
, (true , 'table_size_incl_toast' , pg_table_size(tbl))
, (true , 'indexes_size' , pg_indexes_size(tbl))
, (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
, (true , 'live_rows_in_text_representation' , txt_len)
, (false, '------------------------------' , NULL)
, (false, 'row_count' , ct)
, (false, 'tuples' , (SELECT reltuples::int FROM pg_class WHERE pg_class.oid = x.tbl))
, (false, 'pages' , (SELECT relpages::int FROM pg_class WHERE pg_class.oid = x.tbl))
) l(is_size, metric, nr);
Дополнение к статистике:
Получение статистики выполнявшихся запросов - по суммарно потраченному СУБД времени на все запросы / по потраченному на выполнение одного запроса времени.
Для Postgres 13 и выше нужно заменить total_time на total_exec_time.
SELECT round(total_time::numeric, 2) AS total_time,
calls,
ROWS,
round(total_time::numeric / calls, 2) AS avg_time,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu,
query
FROM pg_stat_statements
ORDER BY total_time DESC -- для сортировки по суммарному времени выполнению всех копий запроса
-- ORDER BY avg_time DESC -- для сортировки по времени выполнения одного запроса
LIMIT 20;
Топ полезных SQL-запросов для PostgreSQL