Мониторинг использования, статистика

Взято с pgtune

select * from pg_stat_user_tables q order by q.seq_scan desc

select * from pg_statio_user_tables t order by t.heap_blks_read desc


Расширение CREATE EXTENSION pg_buffercache;

использование буферов объектами (таблицами, индексами, прочим):

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

relname             |buffers |
--------------------|--------|
sl_user_access_role |35119   |
sl_user_access      |18547   |
t_users             |17817   |
msg_content         |14132   |
sl_report_data      |13379   |


объекты (таблицы и индексы) в кэше:

 SELECT c.relname, count(*) AS buffers,usagecount
 FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

relname                          |buffers |usagecount |
---------------------------------|--------|-----------|
sl_user_access_role              |35119   |5          |
sl_user_access                   |18546   |5          |
t_users                          |17817   |5          |
msg_content                      |14147   |5          |
sl_report_data                   |13396   |5          |
pk_sl_f_cons_rep                 |10942   |5          |
pk_sl_f_form_rep                 |10520   |5          |


Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):

SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20;

relname                    |buffered |buffers_percent |percent_of_relation |
---------------------------|---------|----------------|--------------------|
sl_user_access_role        |274 MB   |6.7             |100.0               |
sl_user_access             |145 MB   |3.5             |100.0               |
t_users                    |139 MB   |3.4             |100.0               |
msg_content                |111 MB   |2.7             |100.0               |
sl_report_data             |105 MB   |2.6             |99.9                |
pk_sl_f_cons_rep           |85 MB    |2.1             |98.3                |
pk_sl_f_form_rep           |82 MB    |2.0             |98.5                |
mv_budgets_last_sum        |82 MB    |2.0             |100.0               |
sl_f_form_rep              |40 MB    |1.0             |99.9                |
sl_report_requisites       |36 MB    |0.9             |20.2                |

sl_f_cons_rep              |39 MB    |0.9             |99.9                |

Далее берем топ таблицу и смотрим в каких процедурах она используется и как, все ли индексы на месте.

select proname,prosrc from pg_proc where proname= your_function_name;