При выполнении запросов современные СУБД используют модель оптимизации затрат — на основе коэффициентов, хранящихся в файлах конфигурации, и собранной статистики рассчитывают «цену» получения и объем результирующих наборов строк.
При повторном выполнении запросов стоимость и селективность пересчитываются.
Вы можете выполнить запрос и посмотреть реальные значения этих параметров, однако в процессе (стандартного) перепланирования оптимизатор СУБД никак не использует эту информацию.
Что, если бы оптимизатор сохранял реальные значения стоимости, селективности и других необходимых параметров для выполнения запроса и при его повторном выполнении опирался не только на стандартную собранную статистику, но и на сохраненную после предыдущего выполнения? Это называется адаптивной оптимизацией запросов, и этот метод оптимизации является многообещающим.
Некоторые СУБД уже используют такие технологии.
Компания Постгрес Профессиональный несколько лет работал над расширением AQO для PostgreSQL, которое реализует (в той или иной форме) адаптивную оптимизацию.
Работы еще ведутся, но уже есть что протестировать.
Для начала давайте подробнее рассмотрим предметную область оптимизации запросов.
Почему планировщик может выбрать неоптимальный план?
SQL-запрос может быть выполнен разными способами.Например, когда две таблицы соединяются, это можно сделать несколькими разными способами — с помощью вложенных циклов, слияния, хеширования.
Чем больше таблиц задействовано в запросе, тем больше вариаций их связей.
Задача планировщика — выбрать из множества вариантов план выполнения запроса с минимальной стоимостью.
Как уже говорилось, в своей работе планировщики многих СУБД используют статистическую информацию, собираемую как автоматически, так и вручную.
Планировщик рассчитывает ориентировочную стоимость на основе этой статистики.
В целом современные планировщики СУБД хорошо работают в большинстве ситуаций.
Однако в некоторых случаях выбранный план может быть очень далек от оптимального.
Например, отсутствие актуальной статистической информации приводит к тому, что планировщик руководствуется в своей работе (скорее всего) неверными данными о количестве строк в объединяемых таблицах.
Чрезмерное занижение (или завышение) мощности приводит к выбору неоптимальных методов доступа к данным в таблицах.
Еще одной важной причиной может быть отсутствие необходимых индексов .
Без индексов планировщик имеет ограниченный выбор способов доступа к данным.
Использование зависимых (коррелированных) условий также может негативно повлиять на работу СУБД.
Планировщик (по умолчанию) считает, что все условия в запросе независимы друг от друга, то есть значение одного условия никак не влияет на другое.
Это делается не всегда.
Если используются зависимые условия (например, почтовый индекс и город), планировщик также посчитает неправильную стоимость и мощность соединений.
На планировщик можно влиять использование функций в условиях .
Для планировщика функция — это «черный ящик»; он не знает, сколько строк вернет функция, что также может привести к ошибочной стоимости плана.
Способы повлиять на работу планировщика
Актуальная статистика является обязательным условием адекватной работы планировщика.Есть несколько способов исправить описанные выше ситуации и помочь планировщику выбрать более оптимальные планы выполнения запросов.Прежде всего убедитесь, что система настроена на регулярный сбор статистической информации.
Без индексов у планировщика есть только один способ получения данных — последовательное сканирование таблицы (и это не всегда плохо или дорого).
В некоторых случаях создание необходимых индексов помогает ускорить доступ к данным – не нужно сканировать всю таблицу.
Но использование индексов (поиск нужных, их создание, поддержание в работе) – удовольствие не бесплатное.
В идеале их следует использовать именно там, где это необходимо.
А там, где он не нужен, не используйте его.
При использовании коррелированных условий соединения в запросах вы можете форма расширенная статистика — явно «сообщить» оптимизатору, что условия связаны друг с другом.
Для этого администратору базы данных (или разработчику) необходимо хорошо знать свои данные и отслеживать зависимые условия в запросах, поскольку количество комбинаций зависимых столбцов заранее предсказать сложно.
Расширенную статистику придется создавать вручную для каждого такого варианта.
При создании функции вы можете указать примерную стоимость внедрения и/или оценку количества строк, созданных функцией.
В версии 12 появился возможность использовать вспомогательные функции для улучшения оценок планировщика в зависимости от аргументов.
Это тоже ручной метод, который не всегда дает оптимальный результат. Если все остальное не помогло, вы можете вручную переписать запрос , например, с помощью материализованных представлений, общих табличных выражений (Common Table Expressions, CTE).
Или уточнить требования предметной области и, возможно, радикально переписать логику запросов.
И есть еще один способ «подсказать» планировщику — адаптивная оптимизация запросов ( а адаптивный д очень о оптимизация).
Идея этого метода заключается в том, что после выполнения запроса сохраняется реальная статистическая информация и при повторении этого (или аналогичного) запроса оптимизатор может на нее положиться.
В СУБД PostgreSQL предприятие Существует расширение для адаптивной оптимизации запросов, называемое АКО .
Это расширение размещено на Github: github.com/postgrespro/aqo , вы также можете попробовать это с ванильным PostgreSQL, подробнее об этом ниже.
Принцип работы модуля
Модуль AQO использует в своей работе машинное обучение.Подробнее о принципе работы можно прочитать в статье Олега Иванова.
Использование машинного обучения для повышения производительности PostgreSQL и подробнее в презентации Адаптивная оптимизация запросов (доклад о YouTube ).
Суть этого метода кратко описана ниже: Для оценки стоимости планировщику необходима оценка мощности, а это, в свою очередь, требует оценки избирательности условий.
Для простых условий (таких как «атрибут = константа» или «атрибут > константа») у планировщика есть модель, по которой он оценивает избирательность.
Для этого он использует статистическую информацию: количество уникальных значений атрибутов, гистограммы и т. д. Для условий, состоящих из простых элементов с использованием логических связок, планировщик применяет легко вычисляемые формулы:
- сел(не А) = 1 - сел(А)
- sel(A и B) = sel(A) * sel(B)
- sel(A или B) = sel( not(не A и не B) ) = 1 - (1 - sel(A)) * (1 - sel(B))
AQO усложняет формулу: для каждого простого условия вводит свой коэффициент. Используя машинное обучение (с использованием регрессии ближайшего соседа), AQO корректирует эти коэффициенты так, чтобы селективность, рассчитанная по формуле, лучше всего соответствовала фактической селективности, которую AQO наблюдал ранее.
Для этого в модуле хранятся:
- избирательность простых условий, рассчитываемых штатным планировщиком;
- реальная избирательность условия по результатам выполнения запроса.
Ситуация, в которой действительно происходит потеря, представляет собой состояние, которое оценивается константой независимо от конкретных значений.
Например, для некоторых условий планировщик не может сделать какие-либо разумные оценки и выбирает константу по умолчанию (например, избирательность условия «выражение1 = выражение2» всегда оценивается как 0,005, а избирательность «выражение1 > выражение2» всегда оценивается как как 1/3).
Таким образом, AQO позволяет улучшить оценку избирательности сложных условий (и, как следствие, оценку стоимости, что может привести к выбору более адекватного плана выполнения).
Установка модуля
Чтобы опробовать функционал модуля на ванильном PostgreSQL, нужно использовать специальный патч, а затем собрать систему из исходного кода.Подробнее описано в файле ПРОЧТИ МЕНЯ на гитхабе.
Если используется PostgreSQL Enterprise, модуль AQO будет установлен в стандартном режиме:
После этого вы можете создать расширение в необходимой базе данных.shared_preload_libraries = 'aqo'
Подготовка базы данных
Рассмотрим конкретный пример работы модуля AQO в демонстрационная база данных .Мы будем использовать большую базу данных, содержащую информацию о рейсах за год, с сентября 2016 по сентябрь 2017 года.
Сначала давайте создадим расширение: CREATE EXTENSION aqo;
Далее отключим параллельную обработку запросов, чтобы отображение параллельных планов не отвлекало от основной задачи: max_parallel_workers_per_gather = 0;
Чтобы планировщик PostgreSQL имел больше возможностей для объединения таблиц, создадим два индекса: CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );
При анализе результатов мы будем ориентироваться на значение BUFFERS как количество страниц, которые необходимо прочитать для завершения работы.
Еще посмотрим на время выполнения (но время на загруженной системе и на домашнем ноутбуке может сильно отличаться).
Увеличим буферный кеш и work_mem, чтобы вся работа выполнялась в оперативной памяти: shared_buffers = '256MB';
work_mem = '256MB';
Использование модуля AQO
Создадим запрос: нам нужно получить пассажиров, которые вылетели бизнес-классом начиная с определенной даты и опоздали не более чем на час.
Выполним запрос без использования AQO (здесь и далее из планов удалена некоторая информация, не влияющая на понимание работы модуля): EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
FROM flights f
Теги: #postgresql #sql #planner #optimizer
-
Как Сделать Ремонт Iphone Не Выходя Из Дома?
19 Oct, 24 -
Как Остановить Зависание В Дата-Центре?
19 Oct, 24
