Анализ медленных запросов с pg_stat_statements

Когда вы работаете с реальными проектами, с вашим приложением одновременно могут взаимодействовать тысячи пользователей. Они отправляют запросы к базе данных, добавляют данные, читают их, обновляют... И вот вы обнаруживаете, что ваш сервер начинает "стонать". Это сигнал о том, что ваши запросы далеко не оптимальны. Иногда запрос, который "на бумаге" выглядел милым, на деле может оказаться бедствием для вашей производительности. Вот тут на сцену выходит pg_stat_statements.

pg_stat_statements позволяет вам:

1. Отслеживать медленные запросы.
2. Понимать, сколько раз выполнялись те или иные запросы.
3. Узнавать, сколько времени они занимали.
4. Видеть среднее время выполнения запроса.
5. Не совершить роковую ошибку и переписать всё приложение!

Изучение структуры pg_stat_statements

После активации расширения в вашей базе данных появляется специальное представление pg_stat_statements. Здесь хранятся все данные о выполненных запросах. Для начала разберемся, что в нем содержится:

SELECT * FROM pg_stat_statements LIMIT 1;

Результат может выглядеть так (упрощенная версия):

Краткие пояснения:

• query — сам SQL-запрос.
• calls — сколько раз выполнялся данный запрос.
• total_time — сколько времени, в общей сложности, потратил запрос.
• rows — количество строк, которые вернул запрос.
• shared_blks_read — количество прочитанных блоков (обратимся к базовому диску, если вы не пользуетесь кешем).

Анализ результатов

Теперь, когда pg_stat_statements включено, давайте посмотрим, как найти медленные запросы.

Самые медленные запросы

Чтобы выявить, какие запросы тратят больше всего времени, мы можем использовать запрос:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Здесь:

• mean_time — это среднее время выполнения одного запроса (total_time / calls).
• ORDER BY total_time DESC — сортируем по общему времени выполнения.

Часто выполняемые запросы

Иногда проблема не в медленных запросах, а в запросах, которые выполняются слишком часто. Например:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

Оптимизация запросов

1. Используйте индексацию

Если вы видите, что запросы по определенным столбцам выполняются медленно, проверьте, есть ли индекс для этих столбцов. Допустим, у вас есть таблица students с большим количеством строк, и вы часто обращаетесь к полю last_name. Стоит создать индекс:

CREATE INDEX idx_students_last_name ON students (last_name);

2. Перепишите запрос

Предположим, вы видите, что запрос вроде SELECT * FROM orders WHERE amount > 1000 занимает слишком много времени. Скорее всего, вместо "всё про всё" нужно выбирать только нужные столбцы:

SELECT order_id, amount FROM orders WHERE amount > 1000;

Очистка статистики

Иногда, чтобы увидеть только новые результаты (например, после оптимизации), нужно очистить данные в pg_stat_statements. Это делается с помощью команды:

SELECT pg_stat_statements_reset();

Работает как кнопка "Сбросить" в вашем калькуляторе. После выполнения статистика будет собираться заново.

Поиск проблемных запросов

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

Шаг 1: Поиск самых медленных запросов

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

Вы видите, что запрос вроде SELECT * FROM students WHERE status = 'active' занимает 30 секунд. Вау. Надо срочно что-то делать.

Шаг 2: Проверка индексации Проанализировав таблицу students, вы понимаете, что у столбца status нет индекса. Вы исправляете это:

CREATE INDEX idx_students_status ON students (status);

Шаг 3: Проверка результата После оптимизации вы снова проверяете pg_stat_statements и видите, что запрос стал выполняться за 0.5 секунды. Победа!

Частые ошибки при использовании pg_stat_statements

Иногда администраторы допускают ошибки при анализе запросов:

1. Неактивированное расширение. Если вы забыли включить pg_stat_statements в shared_preload_libraries, статистика просто не соберется.
2. Игнорирование индексации. Даже если запросы выглядят медленно, проблема может быть решена добавлением правильных индексов.
3. Отсутствие сброса статистики. Если вы не выполняете pg_stat_statements_reset(), старые данные мешают анализировать текущие.

Использование pg_stat_statements в вашей работе — это как GPS-навигатор для базы данных: он говорит вам точно, где вы застряли в "пробке", и даже подсказывает, как объехать. Правильно настроив этот инструмент, вы сможете значительно улучшить производительность своих баз данных.

2

Задача

SQL SELF, 45 уровень, 4 лекция

Недоступна

Определение часто вызываемых запросов

Определение часто вызываемых запросов

Открыть

1

Опрос

Мониторинг PostgreSQL, 45 уровень, 4 лекция

Недоступен

Мониторинг PostgreSQL

Мониторинг PostgreSQL

Открыть