Топ полезных SQL-запросов для PostgreSQL

[lokks]

>> запрос находит индексы, которые созданы, но не использовались в SQL-запросах.

За какой период? :) А то вдруг этот индекс чтоб раз в месяц запрос для бухгалтерии для отчёта делать, а тут дропнули и создали проблем на голову в конце квартала.

[Hamletghost]

Обычно такая статистика ведется с момента старта инстанса постгрес.

И как верно отметили ниже - в бд не должно быть индексов, использующихся раз в квартал. Если таблица небольшая, то просто пусть будет фул скан. Если таблица такая огромная, что фул скан уходит в себя навечно, и нет подходящего частичного индекса - у вас, вероятно, проблемы с дизайном бд. Может нужно думать о секционировании и/или отгрузке данных в специализированные бд для быстрого построения отчетности.

[IvanVakhrushev]

Статистика накапливается с момента её соседнего сброса. Смотрите pg_stat_reset и аналогичные ей функции.

[lokks]

собсно я это и хотел бы видеть в статье, а не в комменте)

[XelaVopelk]

Делать индекс, который обслуживается 24/7, а используется раз в месяц/квартал не самая лучшая идея.

[Vitaly2606]

Либо просто запустить pgwatch2 Postgres.ai Edition - https://gitlab.com/postgres-ai/pgwatch2 который уже содержит множество "полезных SQL-запросов" и получить графики состояния базы данных (в Grafana) за неделю, месяц, и год (при необходимости).

Demo: https://pgwatch.postgres.ai

demo/demo

[edo1h]

а можно детальнее о причинах форка? посмотрел историю коммитов (очень бегло), похоже патчи из апстрима вы не подтягиваете

[Vitaly2606]

у проекта pgwatch2 свое видение дашбордов, у меня другое. Переделывал лично для себя, и уже несколько лет развивается параллельно, по части метрик и дашбордов, сам код сборщика не менял.

[alekskram]

Посмотрел предложенный вами вариант. В целом достаточно интересно, но я бы вместо "либо" написал бы "можно ещё". Статистика приведенная в графане охватывает не только пересекающиеся темы, но и то, что не описано у меня в статье. Но все же, во-первых там нет половины рассматриваемых запросов из моей статьи, а во вторых там собрана общая статистика, а мои запросы приводят информацию по отдельным элементам базы данных.

[versus666]

Буквально на днях пользовался таким запросом для отладки блокировок. Он покажет чуть больше инфы, чем приведенный вами в статье, например имя пользователя и его 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.

[wapmorgan]

Дополнение к размерам:

5. Размер таблиц и индексов, единым плоским списком по всей СУБД (чтобы понять что кушает больше всего)

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

6. Размер одной таблицы суммарный и в пересчёте на одну строку (удобно для прикинуть сколько занимает одна запись на диске)

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

Дополнение к статистике:

1. Получение статистики выполнявшихся запросов - по суммарно потраченному СУБД времени на все запросы / по потраченному на выполнение одного запроса времени.

Для 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;  

[1nd1go]

Спасибо, а по ораклу такого же нет, часом? :)

[alekskram]

Есть ещё по mysql и oracle, со старого проекта записи остались. Оформлю в статью чуть позже)