Освойте инструменты и лучшие практики DevOps — Центр знаний DevOps Освойте инструменты и лучшие практики DevOps — Центр знаний DevOps

Систематическое руководство по отладке медленных запросов PostgreSQL

Отлаживайте медленные запросы PostgreSQL с помощью pg_stat_statements, EXPLAIN ANALYZE, чтения буферов, оценок строк, индексов и верификации.

Систематическое руководство по отладке медленных запросов PostgreSQL

Медленные запросы PostgreSQL легче исправить, если перестать относиться к ним как к загадкам. База данных обычно может показать вам выбранный путь, сколько строк она ожидала, сколько строк фактически затронула, читала ли она из кэша или с диска и ожидала ли другую сессию.

Ошибка, которую я вижу чаще всего, — это переход от «этот endpoint медленный» сразу к «создать индекс». Иногда это работает. Иногда индекс игнорируется, потому что предикат написан так, что его нельзя использовать. Иногда запрос в порядке, но блокируется транзакцией, которая открыта уже двадцать минут. Систематический подход экономит время, так как разделяет форму запроса, оценки планировщика, ввод-вывод, память и блокировки.

Понимание узких мест производительности запросов

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

  • Отсутствующие или неэффективные индексы: База данных вынуждена выполнять последовательное сканирование больших таблиц, хотя индекс мог бы обеспечить быстрый доступ.
  • Неоптимальная структура запроса: Сложные соединения, ненужные подзапросы или неудачное использование функций могут запутать планировщик.
  • Устаревшая статистика: PostgreSQL полагается на статистику для построения эффективных планов выполнения. Если статистика устарела, планировщик может выбрать неэффективный путь.
  • Конкуренция за ресурсы: Проблемы, такие как высокое время ожидания ввода-вывода, чрезмерная блокировка или недостаточный объем памяти, выделенный PostgreSQL.

Шаг 1: Определение медленного запроса

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

Использование pg_stat_statements

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

Включение расширения (требуются права суперпользователя и перезагрузка конфигурации):

-- 1. Убедитесь, что оно указано в postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- 2. Подключитесь к базе данных и создайте расширение
CREATE EXTENSION pg_stat_statements;

Запрос для поиска главных нарушителей:

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

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    total_exec_time DESC
LIMIT 10;

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

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

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

SELECT pid, now() - query_start AS age, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Шаг 2: Анализ плана выполнения с помощью EXPLAIN ANALYZE

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

Синтаксис и использование

Всегда оборачивайте ваш медленный запрос в EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) для наиболее детального вывода. Опция BUFFERS критически важна, так как показывает активность дискового ввода-вывода.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM large_table lt 
JOIN other_table ot ON lt.id = ot.lt_id
WHERE lt.status = 'active' AND lt.created_at > NOW() - INTERVAL '1 day';

Интерпретация вывода

Вывод читается снизу вверх и справа налево, так как самые внутренние узлы выполняются первыми. Ключевые метрики, на которые следует обратить внимание:

  1. cost=: Оценка стоимости планировщика, а не время настенных часов. Используйте ее для сравнения вариантов плана, а не как миллисекунды.
  2. rows=: Предполагаемое количество строк, обработанных этим узлом.
  3. actual time=: Фактическое время, затраченное в миллисекундах на эту конкретную операцию.
  4. rows= (Фактическое): Фактическое количество строк, возвращенных этим узлом.
  5. loops=: Сколько раз выполнялся этот узел (часто высокое значение во вложенных циклах).

Выявление неэффективности:

  • Последовательное сканирование больших таблиц: Если доступ к большой таблице использует Seq Scan вместо Index Scan или Bitmap Index Scan, вероятно, вам нужен лучший индекс.
  • Большое расхождение между предполагаемым и фактическим количеством строк: Если планировщик оценил 10 строк, но узел фактически обработал 1 000 000 строк, статистика устарела, или планировщик сделал плохой выбор.
  • Высокое actual time при соединениях/сортировке: Чрезмерное время, затраченное на операции Hash Join, Merge Join или Sort, часто указывает на недостаточный объем памяти (work_mem) или неспособность эффективно использовать индексы.

Также следите за строками Buffers. shared hit означает, что PostgreSQL нашел страницы в кэше. shared read означает, что ему пришлось читать страницы с хранилища. Запрос может быть медленным из-за плохого плана или из-за того, что план разумен, но он читает большой объем холодных данных с диска.

Совет: Для сложных планов используйте онлайн-инструменты, такие как explain.depesz.com или визуальный просмотрщик планов pgAdmin, чтобы интерпретировать результаты графически.

Шаг 3: Устранение распространенных узких мест

Основываясь на результатах EXPLAIN ANALYZE, примените целенаправленные исправления.

Оптимизация индексов

Если Seq Scan доминирует в большой таблице, а запрос является селективным, рассмотрите индексы на столбцах, используемых в предложениях WHERE, JOIN и ORDER BY. Последовательное сканирование не обязательно плохо; PostgreSQL может правильно выбрать его, когда требуется большинство строк.

Пример: Если запрос фильтрует по status, а затем соединяется по user_id:

-- Создайте составной индекс для более быстрого поиска и соединений
CREATE INDEX idx_large_table_status_user_id ON large_table (status, user_id);

Для производственных систем используйте CREATE INDEX CONCURRENTLY, когда нужно избежать блокировки записи, и помните, что он не может выполняться внутри обычного блока транзакции:

CREATE INDEX CONCURRENTLY idx_large_table_status_user_id
ON large_table (status, user_id);

Обновление статистики (VACUUM ANALYZE)

Если планировщик делает крайне неточные оценки (несоответствие между предполагаемыми и фактическими строками), принудительно обновите статистику таблицы.

ANALYZE VERBOSE table_name;
-- Для высокоактивных таблиц рассмотрите снижение порогов autovacuum/analyze для каждой таблицы.

Настройка памяти

Если операции сортировки или хеширования сбрасываются на диск (часто на это указывает высокий ввод-вывод в выводе BUFFERS или медленная сортировка), увеличьте доступную рабочую память PostgreSQL.

-- Увеличьте work_mem на уровне сессии для тестирования конкретного запроса
SET work_mem = '128MB'; 
-- Или глобально в postgresql.conf для устойчивого повышения производительности

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

Ищите детали сброса сортировки или хеша в плане. В более новых версиях PostgreSQL вывод может показывать методы сортировки, такие как external merge Disk: ..., что является явным признаком того, что операция превысила доступную память и записала временные файлы.

Переписывание запросов

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

Неэффективно (препятствует использованию индекса):

WHERE DATE(created_at) = '2023-10-01'

Эффективно (позволяет использовать индекс):

WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-10-02 00:00:00'

Другой распространенный шаблон — выбор гораздо большего количества столбцов, чем нужно приложению. SELECT * усложняет оптимизацию планов, увеличивает использование памяти и может заставить выполнять дополнительные чтения кучи, когда в противном случае могло бы сработать сканирование только индекса. Для горячих путей перечисляйте столбцы намеренно.

Проверка блокировок

Если EXPLAIN ANALYZE выполняется быстро в вашей сессии, но приложение медленное, запрос может ожидать, прежде чем получит полезное время выполнения. Проверьте ожидания блокировок:

SELECT pid, wait_event_type, wait_event, now() - query_start AS age, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Затем найдите блокирующий запрос:

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       blocked.query AS blocked_query,
       blocker.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
  ON blocked_locks.pid = blocked.pid AND NOT blocked_locks.granted
JOIN pg_locks blocker_locks
  ON blocker_locks.locktype = blocked_locks.locktype
 AND blocker_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocker_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocker_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocker_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocker
  ON blocker.pid = blocker_locks.pid;

Исправление может быть на уровне приложения: более короткие транзакции, вызов медленных внешних API за пределами транзакции, избегание ненужных SELECT ... FOR UPDATE или изменение порядка обновления таблиц, чтобы конкурирующие транзакции не взаимоблокировались.

Небольшой пример: Медленный запрос панели мониторинга

Предположим, панель мониторинга выполняет этот запрос каждые несколько секунд:

SELECT *
FROM orders
WHERE DATE(created_at) = CURRENT_DATE
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Таблица содержит миллионы строк. EXPLAIN ANALYZE показывает последовательное сканирование, большое количество строк, удаленных фильтром, и сортировку. Первым инстинктом может быть индексирование created_at, но предикат оборачивает столбец в DATE(created_at), поэтому обычный индекс на created_at менее полезен.

Перепишите фильтр даты как диапазон:

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Затем рассмотрите индекс, соответствующий фильтру и сортировке:

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

Это не универсальный рецепт индекса. Он работает, когда paid является распространенным фильтром панели мониторинга и когда приложение обычно запрашивает последние оплаченные заказы. Если приложение также сильно фильтрует по аккаунту, лучший индекс может начинаться с account_id. Суть в том, чтобы разработать индекс на основе фактического шаблона доступа, а не на основе одного столбца, упомянутого в запросе.

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

Шаг 4: Верификация и мониторинг

После внедрения изменения повторно запустите EXPLAIN ANALYZE для того же самого запроса со сравнимыми параметрами. Цель не всегда в том, чтобы увидеть сканирование индекса. Цель — увидеть меньше работы: меньше строк, удаленных фильтрами, меньше прочитанных буферов, отсутствие сброса на диск, лучшие оценки строк или меньше времени, затраченного на дорогой узел.

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

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

Еще одна полезная привычка во время реальных инцидентов: тестировать с реалистичными значениями параметров. PostgreSQL может выбирать разные планы для клиента с десятью строками и клиента с десятью миллионами строк. Если приложение использует подготовленные операторы, общие планы также могут вести себя иначе, чем разовый запрос, который вы вставляете в psql. Когда производственная проблема затрагивает одного арендатора, один аккаунт или один диапазон дат, воспроизведите эту форму как можно точнее в безопасной среде.

Если запрос является деструктивным или слишком дорогим для выполнения с EXPLAIN ANALYZE, начните с обычного EXPLAIN, выполните его на стенде или оберните тест в транзакцию, которую вы откатите. Для UPDATE и DELETE вы все еще можете многое узнать из части плана, касающейся сканирования и соединения, прежде чем вносить какие-либо изменения в производство.

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