Введение в анализ производительности запросов

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

Анализ производительности запросов — это как медицинское обследование вашего PostgreSQL. Он помогает находить «узкие места» (боли) и исправлять их для улучшения пользовательского опыта и экономии системных ресурсов.

Как запросы работают под капотом PostgreSQL?

Когда вы пишете простой запрос вроде:

SELECT * FROM products WHERE price > 100;

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

Основные этапы выполнения запросов:

1. Парсинг. PostgreSQL проверяет ваш запрос на синтаксические ошибки и преобразует его в промежуточное представление.
2. Оптимизация. Оптимизатор запросов оценивает несколько вариантов выполнения запроса и выбирает самый «дешёвый» (по времени и ресурсам).
3. Выполнение. Сервер действует согласно выбранному плану, извлекая данные.

Что такое «узкое место»?

«Узкое место» — это часть запроса, которая тормозит всё остальное. Это может быть операция, которая неожиданно съедает больше всего времени или ресурсов. Например, если PostgreSQL вместо быстрого обращения по индексу делает полное сканирование таблицы (Seq Scan), запрос замедляется. Или если данных оказалось гораздо больше, чем планировалось, и сервер тратит уйму времени на сортировку, объединение, фильтрацию.

Такие моменты и называются узкими местами — именно их стоит искать и оптимизировать в первую очередь.

Инструменты анализа производительности запросов

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

1. EXPLAIN и EXPLAIN ANALYZE. Эти команды показывают вам, как PostgreSQL собирается выполнять запрос, или даже выполняют его, чтобы измерить фактическую производительность.

• EXPLAIN: отображает план выполнения запроса без реального его выполнения.
• EXPLAIN ANALYZE: выполняет запрос и отображает реальный план выполнения с фактическими временными метриками.

Пример использования EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Вывод:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Здесь показано, что запрос выполняет «Seq Scan» — полное сканирование таблицы, что неэффективно для больших таблиц.

2. pg_stat_statements. Это дополнительное расширение, которое ведёт учёт выполняемых запросов. Оно показывает:

• Какие запросы выполняются на сервере.
• Сколько времени затрачивается на каждый запрос.
• Сколько строк возвращает запрос и сколько ресурсов потребляет.

Чтобы включить pg_stat_statements, нужно:

1. Включить расширение:

CREATE EXTENSION pg_stat_statements;

2. Настроить конфигурацию PostgreSQL: В файле postgresql.conf добавьте:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Перезапустите PostgreSQL.

Теперь вы можете анализировать запросы:

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

Это покажет 5 самых «тяжёлых» запросов в порядке убывания общего времени выполнения.

3. Логирование медленных запросов. Вы можете настроить PostgreSQL на запись запросов, которые выполняются слишком долго (например, более 1 секунды), в лог-файл.

Для этого в postgresql.conf установите:

log_min_duration_statement = 1000  # Время в миллисекундах (1 секунда)

Теперь медленные запросы будут сохраняться в логи PostgreSQL.

Основные метрики для анализа производительности

Когда вы анализируете производительность запросов, обращайте внимание на следующие ключевые метрики:

1. Время выполнения. Основной показатель времени, затраченного на выполнение запроса. Чем быстрее, тем лучше.
2. Количество строк. Если ваш запрос возвращает или сканирует больше строк, чем вы ожидали, это может быть проблемой.
3. Использование индексов. Если запрос должен использовать индекс, а вместо этого выполняет последовательное сканирование (Seq Scan), это сигнал для оптимизации.
4. Буферы и дисковые операции. Запросы, которые активно взаимодействуют с диском, работают медленнее, чем те, которые используют данные из памяти.

Как эти знания применяются на практике?

Пример 1: Медленный запрос

Вы пишете запрос для выборки всех товаров дороже 100:

SELECT * FROM products WHERE price > 100;

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

Проблема: запрос делает полное сканирование таблицы, потому что нет индекса для колонки price.

Решение:

Создайте индекс:

CREATE INDEX idx_price ON products(price);

Теперь запрос использует Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Пример 2: Выявление медленных запросов с pg_stat_statements

С помощью команды:

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

Вы обнаруживаете запрос, который тратит большое количество времени на выполнение. Вы открываете его с помощью EXPLAIN ANALYZE, исправляете, и он становится быстрее.

Когда вы начнёте использовать EXPLAIN, pg_stat_statements и другие инструменты, ваши запросы станут работать быстрее, а ваш PostgreSQL-сервер будет работать как швейцарские часы. В следующей лекции мы окунёмся в детали параметров EXPLAIN, таких как cost, rows и width, чтобы начать читать планы выполнения запроса, как открытую книгу.

2

Задача

SQL SELF, 41 уровень, 0 лекция

Недоступна

Использование EXPLAIN для анализа плана выполнения запроса

Использование EXPLAIN для анализа плана выполнения запроса

Открыть