Получение плана выполнения запроса в PostgreSQL
Пересказ статьи sabyda. Getting a Query Execution Plan in PostgreSQL
Введение
Часто необходимо проверить производительность только что написанного запроса в PostgreSQL в поисках способа улучшить его производительность. Для этого вам нужно получить отчет о выполнении запроса, который называется планом выполнения. План выполнения запроса дает суммарную информацию о выполнении запроса с подробным отчетом о времени, потраченном на каждом шаге, и затратах на его выполнение.
Сгенерировать план выполнения запроса позволяет ключевое слово EXPLAIN в PostgreSQL. Синтаксис создания плана в PostgreSQL имеет вид:
Для этой команды OPTION имеется много вариантов. Множественный выбор осуществляется перечислением через запятую. Вот эти варианты:
Значение Boolean может быть TRUE или FALSE. Вместо TRUE можно использовать ON или 1. Аналогично для FALSE используются OFF или 0.
Очень простой план выполнения запроса выглядит так:
Прежде чем использовать EXPLAIN для генерации плана выполнения запроса, необходимо узнать об особенностях синтаксиса.
Когда вы используете ключевое слово EXPLAIN, сначала выполняется ваш запрос PostgreSQL. После успешного выполнения запроса возвращается вся статистика времени выполнения, включая полное время на каждый узел плана и общее число строк, прочитанных запросом. Ключевое слово ANALYZE будет фактически выполнять запрос в реальном времени для сбора и подготовки плана выполнения. Поэтому, если вы выполняете следующий запрос:
то строки на самом деле вставляются в таблицу:
Это ключевое слово покажет дополнительную информацию, связанную с планом выполнения запроса. Эта опция по умолчанию имеет значение FALSE. Чтобы установить её в TRUE, можно написать:
Опция COSTS вернет значение стоимости каждого шага в запросе. Сделанные оценки представляют собой произвольные значения, которые присваиваются каждому шагу при выполнении любого запроса на основе ожидаемой нагрузки на ресурсы, которую он может создать. Значение по умолчанию всегда установлено в TRUE. Вы можете использовать это ключевое слово в плане выполнения своего запроса так:
BUFFERS является одним из наиболее интересных ключевых слов для проверки в плане выполнения запроса. Она в основном состоит из 2 частей - разделяемых чтений (shared read) и разделяемых обращений (shared hit). Разделяемые чтения - это число блоков, которые PostgreSQL читает с диска. Разделяемые обращения - это число блоков, которые PostgreSQL читает из кэша. PostgreSQL поддерживает свой собственный кэш. Это вид памяти для запросов, которые выполнялись ранее. Всякий раз, когда вы выполняете запрос, PostgreSQL сначала смотрит в свой кэш и, если необходимо, читает данные с диска.
Это ключевое слово имеет зависимость от ключевого слова ANALYZE, и может использоваться только вместе с ним. Значением по умолчанию является FALSE. Вы можете использовать его в плане выполнения своего запроса таки образом:
Замечание. Так как мы ранее выполняли этот запрос многократно (обсуждая другие ключевые слова), буферы показывают только разделяемые обращения, т.к. результаты находились в кэше. Если выполнить запрос с новым предложением WHERE, появятся также и разделяемые чтения.
TIMING детализирует время запуска и время выполнения на каждом узле. Значением по умолчанию является TRUE. Для его использования должно применяться ключевое слово ANALYZE. Если вы попытаетесь использовать ключевое слово TIMING без ANALYZE, то получите следующую ошибку:
План выполнения с включенным TIMING будет выглядеть так:
План выполнения с выключенным TIMING имеет вид:
Это ключевое слово добавляет итоговую информацию в план выполнения запроса. Вы можете использовать его вместе с ключевым словом ANALYZE. По умолчанию план запроса включает его. Если вы захотите выключить эту опцию, сделайте так:
Это ключевое слово представляет большой интерес, если вам требуется подготовить отчет о производительности запроса или сохранить детали плана выполнения запроса для последующих ссылок. Вам потребуется указать формат для представления результата. TEXT - это значение по умолчанию. Другими вариантами являются XML, JSON и YAML. Для генерации вывода в JSON можно написать так:
Мы уже представили много примеров выше. При этом использовался очень простой запрос. Давайте воспользуемся более близкими к реальным запросами, как те, которые используют предложение WHERE или JOIN.
Предположим, что нам нужно посмотреть план выполнения для запроса, который выводит информацию о студенте с номером 5. План выглядит так:
Выясним, нужны ли вам VERBOSE и BUFFERS в плане выполнения. Если необходимо проверить выходные столбцы, вы можете использовать VERBOSE. Если требуется проверить, сколько блоков было прочитано с диска, а сколько из кэша, можно использовать BUFFERS.
Теперь предположим, что нам требуется соединить 2 таблицы (например, student [содержащую номер и оценки] и home [содержащую номер, город проживания и штат]) и вернуть информацию пользователю. Тогда план выполнения будет такой:
С помощью ключевого слова EXPLAIN вы можете многое сделать для определения стоимости и эффективности своих запросов. Оно помогает найти места, где потребуется тонкая настройка запросов. В результате это поможет вам также идентифицировать запросы, которые будут потреблять значительное количество времени на рабочем сервере. Это поможет вам выявить такие запросы заранее и обезопасить сервер от проблем зависания на ранней стадии.
EXPLAIN [ ( OPTION [, ...] ) ] YOUR_SQL_QUERY;
Для этой команды OPTION имеется много вариантов. Множественный выбор осуществляется перечислением через запятую. Вот эти варианты:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
Значение Boolean может быть TRUE или FALSE. Вместо TRUE можно использовать ON или 1. Аналогично для FALSE используются OFF или 0.
Очень простой план выполнения запроса выглядит так:
Объяснение синтаксиса
Прежде чем использовать EXPLAIN для генерации плана выполнения запроса, необходимо узнать об особенностях синтаксиса.
ANALYZE
Когда вы используете ключевое слово EXPLAIN, сначала выполняется ваш запрос PostgreSQL. После успешного выполнения запроса возвращается вся статистика времени выполнения, включая полное время на каждый узел плана и общее число строк, прочитанных запросом. Ключевое слово ANALYZE будет фактически выполнять запрос в реальном времени для сбора и подготовки плана выполнения. Поэтому, если вы выполняете следующий запрос:
то строки на самом деле вставляются в таблицу:
VERBOSE
Это ключевое слово покажет дополнительную информацию, связанную с планом выполнения запроса. Эта опция по умолчанию имеет значение FALSE. Чтобы установить её в TRUE, можно написать:
COSTS
Опция COSTS вернет значение стоимости каждого шага в запросе. Сделанные оценки представляют собой произвольные значения, которые присваиваются каждому шагу при выполнении любого запроса на основе ожидаемой нагрузки на ресурсы, которую он может создать. Значение по умолчанию всегда установлено в TRUE. Вы можете использовать это ключевое слово в плане выполнения своего запроса так:
BUFFERS
BUFFERS является одним из наиболее интересных ключевых слов для проверки в плане выполнения запроса. Она в основном состоит из 2 частей - разделяемых чтений (shared read) и разделяемых обращений (shared hit). Разделяемые чтения - это число блоков, которые PostgreSQL читает с диска. Разделяемые обращения - это число блоков, которые PostgreSQL читает из кэша. PostgreSQL поддерживает свой собственный кэш. Это вид памяти для запросов, которые выполнялись ранее. Всякий раз, когда вы выполняете запрос, PostgreSQL сначала смотрит в свой кэш и, если необходимо, читает данные с диска.
Это ключевое слово имеет зависимость от ключевого слова ANALYZE, и может использоваться только вместе с ним. Значением по умолчанию является FALSE. Вы можете использовать его в плане выполнения своего запроса таки образом:
TIMING
TIMING детализирует время запуска и время выполнения на каждом узле. Значением по умолчанию является TRUE. Для его использования должно применяться ключевое слово ANALYZE. Если вы попытаетесь использовать ключевое слово TIMING без ANALYZE, то получите следующую ошибку:
План выполнения с включенным TIMING будет выглядеть так:
План выполнения с выключенным TIMING имеет вид:
SUMMARY
Это ключевое слово добавляет итоговую информацию в план выполнения запроса. Вы можете использовать его вместе с ключевым словом ANALYZE. По умолчанию план запроса включает его. Если вы захотите выключить эту опцию, сделайте так:
FORMAT
Это ключевое слово представляет большой интерес, если вам требуется подготовить отчет о производительности запроса или сохранить детали плана выполнения запроса для последующих ссылок. Вам потребуется указать формат для представления результата. TEXT - это значение по умолчанию. Другими вариантами являются XML, JSON и YAML. Для генерации вывода в JSON можно написать так:
Примеры
Мы уже представили много примеров выше. При этом использовался очень простой запрос. Давайте воспользуемся более близкими к реальным запросами, как те, которые используют предложение WHERE или JOIN.
Предположим, что нам нужно посмотреть план выполнения для запроса, который выводит информацию о студенте с номером 5. План выглядит так:
Выясним, нужны ли вам VERBOSE и BUFFERS в плане выполнения. Если необходимо проверить выходные столбцы, вы можете использовать VERBOSE. Если требуется проверить, сколько блоков было прочитано с диска, а сколько из кэша, можно использовать BUFFERS.
Теперь предположим, что нам требуется соединить 2 таблицы (например, student [содержащую номер и оценки] и home [содержащую номер, город проживания и штат]) и вернуть информацию пользователю. Тогда план выполнения будет такой:
Заключение
С помощью ключевого слова EXPLAIN вы можете многое сделать для определения стоимости и эффективности своих запросов. Оно помогает найти места, где потребуется тонкая настройка запросов. В результате это поможет вам также идентифицировать запросы, которые будут потреблять значительное количество времени на рабочем сервере. Это поможет вам выявить такие запросы заранее и обезопасить сервер от проблем зависания на ранней стадии.
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой