# Отладочные запросы

### Вывод списка активных запросов и его длительность

```sql
SELECT
    pid,
    datname,
    usename,
    client_addr,
    xact_start,
    query_start,
    (state_change-query_start) AS query_duration,
    wait_event_type,
    wait_event,
    state,
    query
FROM
    pg_stat_activity
WHERE
    state != 'idle'
AND
    usename NOT IN ('postgres','replicator','zabbix');
```

### 20 самых нагруженных запросов

```sql
SELECT
    substring(query, 1, 50) AS short_query,
    query,
    round(total_time::numeric, 2) AS total_time,
    calls,
    round(mean_time::numeric, 2) AS mean,
    round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM
    pg_stat_statements
ORDER BY
    total_time DESC LIMIT 20;
```

### Общее количество активных и оставшихся соединений

```postgresql
SELECT
  max_conn,
  used,
  res_for_super,
  max_conn-used-res_for_super res_for_normal
FROM
  (SELECT count(*) used FROM pg_stat_activity) t1,
  (SELECT setting::int res_for_super FROM pg_settings WHERE name=$$superuser_reserved_connections$$) t2,
  (SELECT setting::int max_conn FROM pg_settings WHERE name=$$max_connections$$) t3;
```

### Количество подключений в разрезе баз данных и адресов клиентов

```postgresql
SELECT count(*), client_addr, datname FROM pg_stat_activity GROUP BY client_addr, datname;
```

### Количество подключений к базе данных в разрезе адресов клиентов

```postgresql
SELECT count(*), client_addr, datname FROM pg_stat_activity WHERE datname = 'db_name' GROUP BY client_addr, datname;
```