Оптимизация запросов

Подходы к настройке

Авторские права

Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов

Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

Некоммерческое использование материалов курса (презентации,

демонстрации) разрешается без ограничений. Коммерческое

использование возможно только с письменного разрешения компании

Postgres Professional. Запрещается внесение изменений в материалы

курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу:

edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за

любые повреждения и убытки, включая потерю дохода, нанесенные

прямым или непрямым, специальным или случайным использованием

материалов курса. Компания Postgres Professional не предоставляет

каких-либо гарантий на материалы курса. Материалы курса

предоставляются на основе принципа «как есть» и компания Postgres

Professional не обязана предоставлять сопровождение, поддержку,

обновления, расширения и изменения.

Темы

Что настраивать?

Настройка сервера

Настройка приложения

Запросы

Что настраивать?

железо или виртуальная среда

операционная система

данные

планировщик

запросы

приложение

Производительность работы информационной системы обеспечивается

на разных уровнях.

Мы совсем не рассматриваем два важных уровня: оборудование

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

еще один слой) и операционную систему. В частности, работа СУБД

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

и надежность операций ввода-вывода зависит от дисковой подсистемы.

Поэтому прежде всего нужно обеспечить необходимые ресурсы

и настроить ОС для их максимального использования.

Сервер баз данных настраивается так, чтобы служебные задачи не

занимали больше ресурсов, чем необходимо, и чтобы большинство

запросов работали приемлемо. На этом уровне мы можем не только

настраивать конфигурационные параметры (из которых рассмотрим

лишь малую часть), но и управлять расположением данных. Наша

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

ресурсов и адекватную работу планировщика.

Далее дело доходит до настройки отдельных запросов. В теме

«Профилирование» мы говорили, что разработчик обычно думает

над оптимизацией запросов, которые в данный момент пишет,

а администратор — над оптимизацией запросов, оказывающих

наибольшее влияние на сервер в целом. В этой теме мы рассмотрим

некоторые приемы оптимизации запросов.

Но запросы, составляющие рабочую нагрузку, инициируются

приложением. Поэтому и настройка приложения не менее важна, чем

настройка СУБД.

Настройка сервера

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

Физическое расположение данных

Статистика и настройки оптимизатора

Ресурсы: CPU и ввод-вывод

Фоновые рабочие процессы

max_parallel_workers_per_gather = 2

max_parallel_workers = 8

max_worker_processes = 8

Ввод-вывод

effective_io_concurrency = 1

maintenance_io_concurrency = 10

Начнем с настроек, которые определяют, какими ресурсами может

распоряжаться СУБД.

PostgreSQL может использовать несколько ядер процессоров при

параллельной обработке. Для этого запускаются дополнительные

фоновые рабочие процессы. Подробно параллельное выполнение

запросов было рассмотрено в теме «Параллельный доступ»,

а использование фоновых процессов для прикладной разработки —

в теме «Фоновые процессы» курса DEV2.

Из настроек, связанных с использованием ввода-вывода, отметим

параметр effective_io_concurrency. Он сообщает системе количество

независимых дисков в дисковом массиве. Фактически этот параметр

влияет только на количество страниц, которые будут предварительно

считываться в кеш при сканировании по битовой карте.

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

называется maintenance_io_concurrency.

Ресурсы: память

Память

work_mem = 4MB

hash_mem_multiplier = 2

maintenance_work_mem = 64MB

shared_buffers = 128MB

effective_cache_size = 4GB

↑

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

Параметр work_mem определяет размер доступной памяти для

отдельных операций (рассматривался в соответствующих темах).

Также он влияет и на выбор плана. Например, при небольших

значениях предпочтение отдается сортировке, которая лучше работает

при недостатке памяти, чем хеширование. Поэтому для узлов,

использующих хеширование, размер рабочей памяти можно увеличить

с помощью параметра-мультипликатора hash_mem_multiplier.

Параметр maintenance_work_mem влияет на скорость построения

индексов и на работу служебных процессов.

Параметр shared_buffers определяет размер буферного кеша

экземпляра. Настройка этого параметра рассматривается в курсе DBA2,

но точно можно сказать, что значение по умолчанию крайне мало.

Параметр effective_cache_size подсказывает PostgreSQL общий объем

кешируемых данных (как в буферном кеше, так и в кеше ОС). Чем

больше его значение, тем более предпочтительным будет индексный

доступ. Этот параметр не влияет на реальное выделение памяти.

Физическое расположение

Табличные пространства

разнесение данных по разным физическим устройствам

ALTER TABLESPACE SET …

Секционирование

разделение таблицы на отдельно управляемые части

для упрощения администрирования и ускорения доступа

Шардирование

размещение секций на разных серверах для масштабирования

нагрузки на чтение и запись

секционирование и расширение postgres-fdw

или сторонние решения

Физическая организация данных может сильно влиять на

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

С помощью табличных пространств можно управлять размещением

объектов по физическим устройствам ввода-вывода. Например,

активно используемые данные хранить на SSD-дисках, а архивные —

на более медленных HDD.

Часть параметров сервера, которые зависят от характеристик носителя,

(например, random_page_cost) можно устанавливать на уровне

табличных пространств.

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

большого объема. Основная выгода для производительности состоит

в замене полного сканирования всей таблицы сканированием

отдельной секции. Заметим, что секции тоже можно размещать

по различным табличным пространствам.

Еще один вариант — размещение секций на разных серверах

(шардирование) с возможностью выполнения распределенных

запросов. Стандартный PostgreSQL содержит только базовые

механизмы, необходимые для организации шардирования:

секционирование и расширение postgres-fdw. Более эффективно

и полноценно шардинг реализуется с помощью внешних решений.

Обзорно этот вопрос рассматривается в последней теме курса DBA3.

https://postgrespro.ru/docs/postgresql/16/ddl-partitioning

https://postgrespro.ru/docs/postgresql/16/postgres-fdw

Оптимизатор: статистика

Актуальность

настройка автоочистки и автоанализа

autovacuum_max_workers, autovacuum_analyze_scale_factor, …

Точность

default_statistics_target = 100

индекс по выражению, расширенная статистика

Планировщик опирается в своих оценках на статистику, поэтому

статистика должна собираться достаточно часто. Достигается это

настройкой процесса автоочистки и автоанализа. Детали настройки

подробно рассматриваются в курсе DBA2.

Кроме того, статистика должна быть достаточно точной. Абсолютной

точности достичь не получится (и не нужно), но погрешности не должны

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

неактуальной, неточной статистики будет серьезное несоответствие

ожидаемого и реального числа строк в листовых узлах плана.

Для увеличения точности может потребоваться изменить значение

default_statistics_target (глобально или для отдельных столбцов таблиц).

Иногда будет полезным индекс по выражению, обладающий

собственной статистикой. В отдельных случаях можно использовать

расширенную статистику.

Оптимизатор: стоимость

Ввод-вывод

seq_page_cost = 1.0

random_page_cost = 4.0

Время процессора

cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost

стоимость пользовательских функций

↓ для SSD

Имеется большое число настроек для стоимости элементарных

операций, из которых, как мы уже видели, в итоге складывается

стоимость плана запроса. Такие настройки имеет смысл изменять, если

запросы, несмотря на точную оценку кардинальности, выполняются не

самым эффективным образом.

Параметры seq_page_cost и random_page_cost связаны со вводом-

выводом и определяют относительную стоимость чтения одной

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

Значение seq_page_cost равно единице и его не стоит изменять.

Высокое значение параметра random_page_cost отражает реалии

HDD-дисков. Для SSD-дисков (а также в случаях, когда все данные

с большой вероятностью будут закешированы), значение этого

параметра необходимо значительно уменьшать, например, до 1.1.

Параметры времени процессора определяют веса для учета стоимости

обработки прочитанных данных. Обычно эти параметры не меняют,

поскольку трудно спрогнозировать эффект изменений.

Но, как мы видели в теме «Функции», иногда полезно задать стоимость

пользовательской функции в единицах cpu_operator_cost.

https://postgrespro.ru/docs/postgresql/16/runtime-config-query#RUNTIME-C

ONFIG-QUERY-CONSTANTS

Настройка приложения

Клиенты и соединения

Схема данных

Клиенты и соединения

Много сеансов

пул соединений

Курсоры

если нужна часть выборки

cursor_tuple_fraction

Много коротких запросов

подготовленные операторы

перенос логики в SQL

Если приложение открывает слишком много соединений или

устанавливает их слишком часто и на короткое время, может

потребоваться установить между приложением и СУБД пул

соединений. Однако пул накладывает на приложение определенные

ограничения. Подробно этот вопрос рассмотрен в курсе DEV2.

Курсоры следует использовать, только если нужно получить небольшую

часть выборки и ее размер зависит от действий пользователя. В этом

случае параметр cursor_tuple_fraction подскажет планировщику, что

нужно оптимизировать получение первых строк выборки.

Если приложение выполняет слишком большое количество мелких

запросов (каждый из которых сам по себе выполняется эффективно),

общая эффективность будет очень невысока. Это часто происходит

при использовании средств объектно-реляционного отображения

(ORM). В теме «Профилирование» мы рассматривали похожий пример

с функцией, которая вызывалась в цикле.

В таких случаях со стороны СУБД практически нет средств для

оптимизации (кроме подготовленных операторов, если запросы

повторяются). Действенный метод — избавление от процедурного кода

в приложении и перенос его на сервер БД в виде небольшого числа

крупных SQL-команд. Это дает планировщику возможность применить

более эффективные способы доступа и соединений и избавляет от

многочисленных пересылок данных от клиента к серверу и обратно.

К сожалению, если приложение уже написано, переделать его бывает

очень затратно.

Схема данных

Нормализация — устранение избыточности в данных

упрощает запросы и проверку согласованности

Денормализация — привнесение избыточности

может повысить производительность, но требует синхронизации

индексы

предрассчитанные поля (генерируемые столбцы или триггеры)

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

кеширование результатов в приложении

На логическом уровне база данных должна быть нормализованной:

неформально, в данных не должно быть избыточности. Если это не так,

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

согласованность данных, возможны различные аномалии при

изменении данных и т. п.

Однако на уровне хранения некоторое дублирование может дать

существенный выигрыш в производительности — ценой того, что

избыточные данные необходимо синхронизировать с основными.

Самый частый способ денормализации — индексы (хотя о них обычно

не думают в таком контексте). Индексы обновляются автоматически.

Можно дублировать некоторые данные (или результаты расчета на

основе этих данных) в столбцах таблиц. Можно использовать

генерируемые столбцы или синхронизировать данные с помощью

триггеров. Так или иначе, за денормализацию отвечает база данных.

Другой пример — материализованные представления. Их также надо

обновлять, например, по расписанию или другим способом. Подробно

это рассматривалось в теме «Материализация».

Дублировать данные можно и на уровне приложения, кешируя

результаты выполнения запросов. Это популярный способ, но часто

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

данных (например, в случае использования ORM). На приложение

ложится задача по своевременному обновлению кеша, обеспечению

разграничения доступа к данным кеша и т. п.

Схема данных

Типы данных

выбор подходящих типов

составные типы (массивы, JSON) вместо отдельных таблиц

Ограничения целостности

помимо обеспечения целостности данных, могут учитываться

планировщиком для устранения ненужных соединений, улучшения

оценок селективности и других оптимизаций

первичный ключ и уникальность — уникальный индекс

внешний ключ

отсутствие неопределенных значений

проверка CHECK (constraint_exclusion)

Важен правильный выбор типов данных из всего многообразия,

предлагаемого PostgreSQL. Например, представление интервалов дат

не двумя отдельными столбцами, а с помощью диапазонных типов

(daterange, tstzrange) позволяет использовать индексы GiST и SP-GiST

для таких операций, как пересечение интервалов.

В ряде случаев эффект дает использование составных типов (таких как

массивы или JSON) вместо классического подхода с созданием

отдельной таблицы. Это позволяет сэкономить на соединении и не

требует хранения большого количества служебной информации

в заголовках версий строк. Но такое решение следует принимать

с большой осторожностью, поскольку оно имеет свои минусы.

Ограничения целостности важны сами по себе, но в некоторых

случаях планировщик может учитывать их и для оптимизации.

●

Ограничения первичного ключа и уникальности поддерживаются

уникальными индексами. Это позволяет строить более точную

статистику и эффективнее выполнять соединения.

●

Наличие внешнего ключа и ограничений NOT NULL позволяет в ряде

случаев устранять лишние соединения (что особенно важно при

использовании представлений), а также улучшает оценку

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

столбцам.

●

Ограничение CHECK с использованием параметра

constraint_exclusion позволяет не сканировать таблицы (или секции),

если в них гарантированно нет требуемых данных.

Запросы

Пути оптимизации

Короткие запросы

Длинные запросы

Пути оптимизации

Цель оптимизации — получить адекватный план

Исправление неэффективностей

каким-то образом найти и исправить узкое место

бывает сложно определить, в чем проблема

часто приводит к борьбе с планировщиком

Правильный расчет кардинальности

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

и положиться на планировщик

если план все еще неадекватный, настраивать глобальные параметры

Цель оптимизации запроса — получить адекватный план выполнения.

Есть разные пути, которыми можно идти к этой цели.

Можно посмотреть в план запроса, понять причину неэффективного

выполнения и сделать что-то, исправляющее ситуацию. К сожалению,

проблема не всегда бывает очевидной, а исправление часто сводится

к борьбе с оптимизатором.

Если идти таким путем, то хочется иметь возможность целиком или

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

Такая возможность называется подсказками (хинтами) и в явном виде

отсутствует в PostgreSQL.

Другой подход состоит в том, чтобы добиться корректного расчета

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

аккуратная статистика, но этого часто бывает недостаточно.

Если идти таким путем, то мы не боремся с планировщиком,

а помогаем ему принять верное решение. К сожалению, это часто

оказывается слишком сложной задачей.

Если при правильно оцененной кардинальности планировщик все

равно строит неэффективный план, это повод заняться настройкой

глобальных конфигурационных параметров.

Обычно имеет смысл применять оба способа, смотря по ситуации

и сообразуясь со здравым смыслом.

Короткие запросы

Читают небольшую часть данных, выдают мало строк

Характерны для OLTP

Важно получать ответ быстро

Характерные особенности плана

условия с высокой селективностью

индексы

соединения методом вложенного цикла

Запросы можно (довольно условно) разделить на две группы, имеющие

свои особенности и требующие разных подходов к оптимизации:

«короткие» и «длинные».

К первой относятся запросы, характерные для OLTP-систем. Такие

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

Они могут обращаться и к большим таблицам, но в этом случае имеют

условия с высокой селективностью, позволяющие читать лишь малую

часть данных.

Обычно короткие запросы обслуживают пользовательский интерфейс,

и поэтому важно, чтобы они выполнялись как можно быстрее.

Приоритет отдается времени отклика.

Это обеспечивается тем, что необходимые запросам данные читаются

либо из очень небольших таблиц (одна-две страницы), либо по индексу.

Соединения, как правило, выполняются методом вложенного цикла,

который не требует подготовительных действий (в отличие от хеш-

соединения) и не вызывает проблем при небольшом количестве

соединяемых строк.

Длинные запросы

Читают большие таблицы целиком

Характерны для OLAP

Важно не читать одни и те же данные повторно

Характерные особенности плана

условия с низкой селективностью

полное сканирование

соединение хешированием

агрегация

параллельное выполнение

Длинные запросы встречаются в OLAP-системах. Они характеризуются

тем, что для получения результата должны прочитать большое

количество данных. При этом не важно, сколько строк возвращает

запрос: в результате агрегации может остаться и одна.

Такие запросы могут читать большие таблицы целиком, а условия

обычно низкоселективны. Поэтому вместо индексного доступа более

эффективным становится последовательное сканирование, а вместо

соединения вложенным циклом — хеш-соединение. Тем более, что для

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

а не первые строки как можно раньше.

Очень важно следить, чтобы одни и те же данные не читались

в запросе несколько раз. Это может происходить по самым разным

причинам (из-за коррелированных подзапросов, из-за использования

функций и т. п.), которые приводят к появлению вложенных циклов,

явных или неявных.

Разумеется, в плане длинного запроса могут встречаться и индексный

доступ (при наличии высокоселективных условий), и соединение

вложенным циклом (при соединении небольшого количества строк).

Благодаря тому, что длинные запросы обрабатывают много данных

и обычно агрегируют их (пользователям редко бывают нужны миллионы

строк), они могут выигрывать от параллельного выполнения.

Подсказки оптимизатору

Отсутствуют в явном виде

хотя средства влияния имеются: конфигурационные параметры,

материализация CTE и другие

Сторонние расширения

pg_hint_plan

Еще один (традиционный для других СУБД) способ влияния —

подсказки оптимизатору — отсутствует в PostgreSQL. Это

принципиальное решение сообщества:

https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion .

На самом деле часть подсказок все-таки неявно существует в виде

конфигурационных параметров и других средств.

Кроме того, есть специальные расширения, например

https://postgrespro.ru/docs/enterprise/16/pg-hint-plan (автор — Киотаро

Хоригучи). Но нельзя забывать, что использование подсказок, сильно

ограничивающих свободу планировщика, может навредить в будущем,

когда распределение данных изменится.

Итоги

Настройка выполняется на разных уровнях системы

Доступен широкий спектр методов влияния

на план выполнения запросов

Разные типы запросов требуют разных подходов

Ничто не заменит голову и здравый смысл

Практика

1. Оптимизируйте запрос, выводящий контактную

информацию пассажиров, купивших билеты бизнес-класса,

рейсы которых были задержаны более чем на 5 часов:

SELECT t.*

FROM tickets t

JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no

JOIN flights f ON f.flight_id = tf.flight_id

WHERE tf.fare_conditions = 'Business'

AND f.actual_departure >

f.scheduled_departure + interval '5 hour';

2. Оптимизируйте запрос, вычисляющий среднюю стоимость

билетов на перелеты, выполняемые различными типами

самолетов.

Начните с варианта, предложенного в комментарии.

SELECT a.aircraft_code, (

SELECT round(avg(tf.amount))

FROM flights f

JOIN ticket_flights tf ON tf.flight_id = f.flight_id

WHERE f.aircraft_code = a.aircraft_code

)

FROM aircrafts a