Медленный SQL-запрос и что с этим можно сделать

У меня получилось написать SQL-запрос, который возвращает именно то, что имел ввиду мой тимлид. Ура! Но..

Почему мой SQL-запрос работает так долго? Как вообще выполняются SQL-запросы? Могу ли я повлиять на скорость выполнения или Postgres сам решает как ему выполнить запрос?

Итак, обо всем по порядку (или не совсем).

SQL – структурированный язык запросов, и язык этот – декларативный. Это означает, что при помощи выражений, написанных на этом языке, клиент (давайте так назовем того, кто пишет запросы) описывает желаемый результат, т. е. то, что он хочет получить в конечном итоге.

Получается, что при всем этом, клиент никак не сообщает исполнителю (postgres), каким образом все-таки выполнить запрос, что сделать сначала, а что потом, какой метод использовать для сбора данных.

Как раз на эти вопросы отвечает планировщик запросов postgres (query planner/optimizer).

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

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

Схематично это можно изобразить следующим образом:

Запрос поступает на обработку к планировщику, он составил несколько возможных вариантов и в результате сравнения выявил, что Query plan 2 превосходит остальные, следовательно его можно передавать в Executor.

Общее понимание этапов выполнения запроса прояснилось, давайте пойдем дальше.

Теперь нам необходимо понять, какой план будет выбран планировщиком для выполнения. Для этой цели есть специальный инструмент – EXPLAIN. У него есть много различных опций.

1. ANALYZE – посмотреть фактическое время выполнения (default:false),
2. VERBOSE – посмотреть дополнительную информацию (default: false),
3. COSTS – посмотреть стоимости выполнения операций (default: true),
4. TIMING – фактическое время запуска и время на каждый узел (только с ANALYZE, default: true),
5. BUFFERS – использование буфера, чтобы определить насколько удалось избежать походов на диск, показывает количество (hit) найденных в кэше данных и (read) кол-во походов на диск (только с ANALYZE, default: false),
6. FORMAT – формат вывода (text/xml/json/yaml default:text).

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

Обращаю внимание, что следует быть аккуратным при использовании ANALYZE с критически важными запросами, например, на удаление (DROP) чего-либо, так как ресурс будет фактически удален. Но для обхода этой особенности есть трюк – можно обернуть выполнение explain в транзакцию и сразу же откатить ее (ROLLBACK).

EXPLAIN может быть использован не со всеми типами запросов. Вот список доступных для анализа: INSERT, UPDATE, DELETE, SELECT, EXECUTE (подготовленного выражения), CREATE TABLE … AS и DECLARE (курсора).

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

Допустим, у нас есть таблица Users:

Значения всех полей достаточно понятны, единственное – score, это рейтинг пользователя, определяющий уровень доверия к нему.

Заполним ее данными, добавив 10.000.000 строк, так, чтобы можно было ощутить скорость выполнения запросов:

Давайте посмотрим, какой план выберет планировщик, например, при таком запросе:

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

Первое на что обратим внимание – это структура вывода, отступами обозначены узлы плана (nodes), и для каждого узла указана оценка стоимости.

В нашем случае узел один, но для более сложных запросов их может быть множество. И в таком случае важно понимать, что выполняться план будет снизу вверх (если смотреть на результат explain), от вложенных узлов к корневым и при этом корневые включают стоимости вложенных узлов в свою стоимость.

Также подчеркну, что результат explain может значительно измениться после первого запуска, так как результаты могут лечь в кэш, поэтому, прежде чем окончательно давать оценку результату, следует несколько раз выполнить explain.

Рассмотрим основные значения:

1. Execution Time: 1697.952 ms – это фактическое время выполнения анализируемого запроса,
2. Seq Scan – какой будет использован метод поиска нужной строки,
3. cost=1000.00..166712.63 – startup cost .. total cost первое значение это стоимость получения первой строки, второе – стоимость получения всех строк. Стоимость это относительное понятие, в целом, 1 единица стоимости отражает время получения одной страницы (8kB) данных из памяти,
4. rows – количество строк в результате,
5. width – средняя длина строки,
6. Buffers: hit= … read= – походы в кэш и на диск.

На что мне как разработчику обращать внимание:

• Время выполнения запроса (Execution Time) – основная характеристика по которой можно определить что запрос стал выполняться быстрее.
• Метод поиска (Seq Scan/Index Scan/Bitmap Scan) – так мы понимаем какой алгоритм использует executor при выполнении, будет ли он использовать индекс, или последовательно сканировать всю таблицу. Долго выполняющиеся Sec Scan которые отфильтровывают большое кол-во строк – первые кандидаты на оптимизацию.
• Стоимости узлов (cost) – выявив узел с наибольшей стоимостью оптимизируем его.
• Кол-во строк (rows) – узел, у которого оно значительно отличается от других, вероятнее всего является причиной долгого времени выполнения родительских нод.

Теперь, имея понимание плана запроса и какие-то цифры, я как разработчик могу внести корректировки в запрос или даже в структуру таблицы для того, чтобы повлиять на план выполнения. Как пример, один из способов оптимизации – это добавление индекса, на те поля что используются в узле, для которого планировщик использует Seq Scan и отфильтровывает большое кол-во строк. Давайте попробуем это сделать.

Добавим индекс на поле name используя метод доступа hash:

и посмотрим как теперь будет выглядеть explain на тот же запрос

Если обратить внимание на цифры, то результат добавления индекса – значительный.

Снизилось время выполнения с 1697.952 ms до 1.392 ms. Использован другой алгоритм поиска – Index Scan. Значительно снизилось количество походов на диск – всего 1.

Итак, отвечая на вопросы обозначенные в начале статьи:

• Мы разобрались как происходит выполнение SQL запроса.
• Научились пользоваться инструментом, который дает нам цифры, чтобы предметно рассуждать, что не так с запросом.
• Поняли, что на план запроса повлиять мы все-таки можем и значительно. Так что не все зависит от планировщика Postgres, многое лежит на плечах разработчика.