# Диагностика производительности PostgreSQL

> [HTML Version](postgresql-performance-diagnostics.html)

В статье рассмотрим методы диагностики производительности [PostgreSQL](postgresql.md), которая состоит из трёх этапов:

1. [Оптимизировать кластер](#optimize-cluster).

2. [Проанализировать логи](#log-analysis).

3. [Проанализировать планы](#analysis-of-plans).

## Шаг 1. Оптимизировать кластер

Для улучшения работы вашей базы данных используйте следующие методы диагностики производительности PostgreSQL:

- примените вертикальное или горизонтальное масштабирование и затем проведите анализ запросов;

- настройте конфигурационные параметры развёрнутого кластера. Например, используйте онлайн генераторы конфигурационных параметров. Подробнее смотрите в сервисе [CYBERTEC](https://pgconfigurator.cybertec-postgresql.com/).

Пример генерации оптимальных настроек для кластера по введённым характеристикам сервера:

**(postgresql-performance-diagnostics-1.png)**

## Шаг 2. Проанализировать логи

Обратите внимание, что включение анализаторов запросов может потреблять около 10% ресурсов. Но после завершения процесса анализа и оптимизации запросов производительность может значительно вырасти.

Вы можете использовать встроенные инструменты для анализа производительности PostgreSQL, чтобы идентифицировать узкие места и медленные запросы:

- [pg\_stat\_statements](#pg-stat-statements);

- [pg\_stat\_kcache](#pg-stat-kcache);

- [auto\_explain](#auto-explain);

- [log\_min\_duration\_statement](#log-min-duration-statement).

### Инструмент «pg\_stat\_statements»

Это один из наиболее полезных инструментов для анализа производительности в PostgreSQL. Этот модуль позволяет собирать статистику о выполненных SQL-запросах, включая их текст, время выполнения и количество вызовов.

1. Чтобы включить \[OBJECT\], задайте параметр в \[OBJECT\].

````
shared\_preload\_libraries = 'pg\_stat\_statements';

2. ````
Перезапустите PostgreSQL и выполните запрос для анализа производительности.

````
SELECT query, total\_time, calls FROM pg\_stat\_statements ORDER BY total\_time DESC LIMIT 10;

### ````
Инструмент «pg\_stat\_kcache»

Это расширение, которое позволяет мониторить состояние кеша операционной системы, используемого базой данных. Инструмент полезен для определения эффективности кеширования и возможных узких мест. 

1. Чтобы включить \[OBJECT\], задайте параметр в \[OBJECT\].

````
shared\_preload\_libraries = 'pg\_stat\_kcache'

2. ````
Перезапустите PostgreSQL и выполните запросы, чтобы анализировать производительность кеша.

````
SELECT \* FROM pg\_stat\_kcache; # Запрос общей статистики  
SELECT \* FROM pg\_stat\_kcache\_buffers; # Статистика по кешу буферов (shared buffers)  
SELECT \* FROM pg\_stat\_kcache\_files; # Статистика по кешу файлов (file cache)  
SELECT \* FROM pg\_stat\_kcache\_directories;  # Статистика по кешу каталогов (directory cache)

### ````
Инструмент «auto\_explain»

Это модуль, который автоматически анализирует запросы и отображение плана выполнения для запросов, выполняющихся с долгим временем выполнения. 

Чтобы включить \[OBJECT\], задайте параметр в \[OBJECT\]:

````
session\_preload\_libraries = 'auto\_explain'  
auto\_explain.log\_min\_duration = 1000;  ## будут записываться запросы, выполняющиеся более 1000 миллисекунд  
auto\_explain.log\_analyze = true; ##  будет записываться вывод команды EXPLAIN ANALYZE  
auto\_explain.log\_buffers = true; ## включение статистики об использовании буферов

### ````
Инструмент «log\_min\_duration\_statement»

Этот параметр позволяет настроить журналирование запросов, выполнение которых занимает больше времени, чем указано в миллисекундах. Например, чтобы журналировать запросы, выполняющиеся более 1000 миллисекунд, задайте параметр в \[OBJECT\]:

````
log\_min\_duration\_statement = 1000; ## Журналирование запросов, выполняющихся более 1000 миллисекунд

## ````
Шаг 3. Проанализировать планы

Оптимизация запросов может быть сложной и часто представляет собой итеративный процесс. Вносите изменения постепенно и измеряйте влияние каждого изменения на производительность запросов. Также рекомендуем протестировать выполненные оптимизации в промежуточной среде, прежде чем применять их к рабочей базе данных. Убедитесь в отсутствии непредвиденных последствий.

План запроса PostgreSQL описывает выполнение SQL-запроса, включая сортировку и фильтрацию, а также использование ресурсов во время выполнения.

Пример плана

````
Query Text: SELECT (main.body) FROM "head"."tasks" AS main WHERE ("head".f\_cast\_isots(main.body#>>'\{\_\_deletedAt\}') IS NULL AND (((main.body#>>'\{\_\_item\}')::jsonb) =  '\{"namespace":"service\_desk","code":"night\_request\_handling","id":"018b822c-e7b0-c751-1914-2b698ad2b01d"\}'::jsonb) AND ((main.body#>>'\{\_\_parentId\}')::text) IS NULL) ORDER BY  "head".f\_cast\_isots(main.body#>>'\{\_\_createdAt\}') DESC, (main.body#>>'\{\_\_id\}') LIMIT 11 OFFSET 0  
 	Limit  (cost=20249.14..235800.34 rows=11 width=1504) (actual rows=3 loops=1)  
 	  Output: body, (head.f\_cast\_isots((body #>> '\{\_\_createdAt\}'::text\[\]))), ((body #>> '\{\_\_id\}'::text\[\]))  
 	  Buffers: shared hit=1066102 read=249864 written=1207  
 	  I/O Timings: read=1293.051 write=17.127  
 	  ->  Incremental Sort  (cost=20249.14..627711.61 rows=31 width=1504) (actual rows=3 loops=1)  
 	        Output: body, (head.f\_cast\_isots((body #>> '\{\_\_createdAt\}'::text\[\]))), ((body #>> '\{\_\_id\}'::text\[\]))  
 	        Sort Key: (head.f\_cast\_isots((main.body #>> '\{\_\_createdAt\}'::text\[\]))) DESC, ((main.body #>> '\{\_\_id\}'::text\[\]))  
 	        Presorted Key: (head.f\_cast\_isots((main.body #>> '\{\_\_createdAt\}'::text\[\])))  
 	        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 31kB  Peak Memory: 31kB  
 	        Buffers: shared hit=1066102 read=249864 written=1207  
 	        I/O Timings: read=1293.051 write=17.127  
 	        ->  Index Scan Backward using "tasks:f\_\_\_createdAt" on head.tasks main  (cost=0.43..627710.21 rows=31 width=1504) (actual rows=3 loops=1)  
 	              Output: body, head.f\_cast\_isots((body #>> '\{\_\_createdAt\}'::text\[\])), (body #>> '\{\_\_id\}'::text\[\])  
 	              Filter: (((main.body #>> '\{\_\_parentId\}'::text\[\]) IS NULL) AND (((main.body #>> '\{\_\_item\}'::text\[\]))::jsonb = '\{"id": "018b822c-e7b0-c751-1914-2b698ad2b01d", "code":  
 "night\_request\_handling", "namespace": "service\_desk"\}'::jsonb) AND (head.f\_cast\_isots((main.body #>> '\{\_\_deletedAt\}'::text\[\])) IS NULL))  
 	              Rows Removed by Filter: 633258  
 	              Buffers: shared hit=1066102 read=249864 written=1207  
 	              I/O Timings: read=1293.051 write=17.127  
 	JIT:  
 	  Functions: 6  
 	  Options: Inlining false, Optimization false, Expressions true, Deforming true
````

````
Разберём различные части плана, указанного в примере выше:

- \[OBJECT\] — анализируемый SQL-запрос. Выбирает данные из таблицы с именем \[OBJECT\] в схеме \[OBJECT\] с определёнными условиями и упорядочивает результаты;

- \[OBJECT\] — информация об ожидаемой стоимости запроса и количестве строк, которые он ожидает вернуть. В указанном примере запрос ожидает вернуть 11 строк;

- \[OBJECT\] — перечисление столбцов, которые будут включены в выходные данные запроса: \[OBJECT\], вычисляемое выражение и другое вычисляемое выражение;

- \[OBJECT\] — показывает применение буферов, количество операций чтения и сколько произошло записей. Определяет общие обращения, чтения и записи;

- \[OBJECT\] — информация о времени, затраченном на операции чтения и записи;

- \[OBJECT\] — операция сортировки, которая является частью выполнения запроса. Определяет критерии сортировки и использование памяти;

- \[OBJECT\] — основная операция при выполнении запроса. Это сканирование индекса \[OBJECT\], которое используется для фильтрации и извлечения строк из таблицы \[OBJECT\] на основе заданных условий. Условия перечислены в разделе \[OBJECT\], где проверяется наличие определённых значений в данных JSON, а также отсутствие значения в поле \[OBJECT\]. Показатель \[OBJECT\] указывает, сколько строк было отфильтровано по этим условиям;

- \[OBJECT\] — информация о JIT-компиляции функций и настройках их оптимизации.

## Визуализация плана

Вы можете также проанализировать выполнение SQL-запроса. С помощью визуализации плана вы получите подробную информацию о том, как PostgreSQL выполняет запрос, а также о возможных проблемах производительности. Для этого можно использовать pgAdmin или сторонние сервисы. 

Пример визуализации:

- [explain.dalibo](https://explain.dalibo.com/):

**(postgresql-performance-diagnostics-2.png)**

- [explain PostgreSQL](https://explain.tensor.ru/):

**(postgresql-performance-diagnostics-3.png)**

Данный ресурс предоставляет рекомендации по оптимизации запросов:

**(postgresql-performance-diagnostics-4.png)**

Для улучшения производительности вашей базы данных и быстрого выполнения запросов вы можете добавить индекс на любом этапе диагностики:

**(postgresql-performance-diagnostics-5.png)**