Оптимизация запросов
Приемы оптимизации
13
Авторские права
© Postgres Professional, 2019–2022
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Пути оптимизации
Статистика
Настройки, влияющие на планирование и выполнение
Схема данных
Физическое расположение данных
Изменение запросов
3
Пути оптимизации
Цель оптимизации — получить адекватный план
Исправление неэффективностей
каким-то образом найти и исправить узкое место
бывает сложно определить, в чем проблема
часто приводит к борьбе с планировщиком
Правильный расчет кардинальности
добиться правильного расчета кардинальности в каждом узле
и положиться на планировщик
если план все еще неадекватный, настраивать глобальные параметры
Цель оптимизации запроса — получить адекватный план выполнения.
Есть разные пути, которыми можно идти к этой цели.
Можно посмотреть в план запроса, понять причину неэффективного
выполнения и сделать что-то, исправляющее ситуацию. К сожалению,
проблема не всегда бывает очевидной, а исправление часто сводится
к борьбе с оптимизатором.
Если идти таким путем, то хочется иметь возможность целиком или
частично отключить планировщик и самому создать план выполнения.
Такая возможность называется подсказками (хинтами) и в явном виде
отсутствует в PostgreSQL.
Другой подход состоит в том, чтобы добиться корректного расчета
кардинальности в каждом узле плана. Для этого, конечно, нужна
аккуратная статистика, но этого часто бывает недостаточно.
Если идти таким путем, то мы не боремся с планировщиком,
а помогаем ему принять верное решение. К сожалению, это часто
оказывается слишком сложной задачей.
Если при правильно оцененной кардинальности планировщик все
равно строит неэффективный план, это повод заняться настройкой
глобальных конфигурационных параметров.
Обычно имеет смысл применять оба способа, смотря по ситуации и
сообразуясь со здравым смыслом.
Дальше мы рассмотрим некоторые возможные приемы оптимизации.
4
Статистика
Актуальность
настройка автоочистки и автоанализа
autovacuum_max_workers, autovacuum_analyze_scale_factor, …
Точность
default_statistics_target = 100
индекс по выражению
расширенная статистика (например, для коррелированных предикатов)
Использование имеющейся статистики
планировщик не всегда может сделать правильные выводы
из имеющихся данных
переформулирование запроса, временные таблицы
В первую очередь стоит еще раз напомнить, что первый шаг
к адекватному плану — актуальная статистика. Для этого статистика
должна собираться достаточно часто, а достигается это настройкой
автоочистки и автоанализа. Детали настройки подробно
рассматриваются в курсе DBA2.
Кроме того, статистика должна быть достаточно точной. Абсолютной
точности достичь не получится (и не нужно), но погрешности не должны
приводить к построению некорректных планов. Признаком
неактуальной, неточной статистики будет серьезное несоответствие
ожидаемого и реального числа строк в листовых узлах плана.
Для увеличения точности может потребоваться изменить значение
default_statistics_target (глобально или для отдельных столбцов таблиц).
Иногда может оказаться полезным индекс по выражению, обладающий
собственной статистикой. В отдельных случаях можно использовать
расширенную статистику.
Наличие точной актуальной статистики необходимо, но не достаточно
для построения хорошего плана. Планировщик может не суметь
сделать правильные выводы из имеющейся статистики; часто ошибки
связаны с неверным расчетом селективности соединений или
агрегаций.
Иногда можно немного переформулировать запрос, чтобы исправить
ситуацию. Может помочь вынесение части запроса во временную
таблицу, чтобы следующий этап учитывал получившееся количество
строк, хотя это и ограничивает свободу планировщика и чревато
накладными расходами.
6
Настройки стоимости
Ввод-вывод
можно (и нужно) указывать на уровне табличных пространств
seq_page_cost = 1.0
random_page_cost = 4.0
effective_io_concurrency = 1
↓ для SSD
Имеется большое число настроек, которые позволяют задать стоимости
элементарных операций, из которых, как мы уже видели, в итоге
складывается стоимость плана запроса. Такие настройки имеет смысл
изменять, если запрос, в котором планировщик точно спрогнозировал
кардинальности, тем не менее выполняется не самым эффективным
образом.
С вводом-выводом связаны настройки, задающие веса для «условных
единиц», в которых выражается стоимость.
Это параметры seq_page_cost и random_page_cost, определяющие
стоимость чтения одной страницы при последовательном доступе и при
произвольном доступе.
Значение seq_page_cost равно единице и его не стоит изменять.
Высокое значение параметра random_page_cost отражает реалии
HDD-дисков. Для SSD-дисков (а также в случаях, когда все данные
с большой вероятностью будут закешированы), значение этого
параметра необходимо значительно уменьшать, например, до 1.1.
Параметр effective_io_concurrency можно увеличить до числа
независимых дисков в дисковом массиве. Фактически этот параметр
влияет только на количество страниц, которые будут предварительно
считаны в кеш при сканировании по битовой карте.
7
Настройки стоимости
Время процессора
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
CREATE FUNCTION … COST стоимость
Параметры для времени процессора определяют веса для учета
времени обработки прочитанных данных. Обычно этот вклад меньше
стоимости ввода-вывода, но не всегда. Можно указать стоимость
обработки одной табличной строки (cpu_tuple_cost), одной строки
индекса (cpu_index_tuple_cost) и одной операции (cpu_operator_cost;
например, операции сравнения).
Также можно задать стоимость пользовательской функции в единицах
cpu_operator_cost. По умолчанию функции на Си получают оценку 1,
а на других языках — 100.
8
Настройки стоимости
Параллельное выполнение
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
Курсоры
cursor_tuple_fraction = 0.1
Параметр parallel_setup_cost указывает стоимость развертывания
инфраструктуры для параллельной обработки: выделение общей
памяти и порождение рабочих процессов. Эта величина добавляется
к общей стоимости запроса. Параметр parallel_tuple_cost определяет
стоимость пересылки одной строки данных от процесса к процессу.
Остальные параметры, относящиеся к параллельному выполнению,
обсуждались ранее (они ограничивают количество параллельных
процессов и устанавливают минимальный размер выборки, при которой
планировщик рассматривает параллельные планы).
Напомним также, что оценка стоимости состоит из двух компонент:
cost1..cost2. При обычном выполнении запросов планировщик
выбирает план, минимизируя cost2, то есть оценку ресурсов для
получения полной выборки данных. А при использовании курсоров
учитывается значение параметра cursor_tuple_fraction: чем меньше
значение этой доли, тем сильнее планировщик ориентируется на
быстрое получение первых результатов. Говоря точнее,
минимизируется значение cost1 + (cost2 − cost1) × cursor_tuple_fraction.
10
Настройки памяти
Память
work_mem = 4MB
hash_mem_multiplier = 1
maintenance_work_mem = 64MB
effective_cache_size = 4GB
↑
Ряд настроек влияет на выделение памяти. Параметр work_mem
определяет размер доступной памяти для отдельных операций
(рассматривался в соответствующих темах). Также он влияет и на
выбор плана. При небольших значениях предпочтение отдается
сортировке (а не хешированию), поскольку ее алгоритм менее
чувствителен к недостатку памяти.
Для узлов, использующих хеширование, размер рабочей памяти можно
увеличить с помощью мультипликатора — параметра
hash_mem_multiplier.
Параметр maintenance_work_mem влияет на скорость построения
индексов и на работу служебных процессов.
Параметр effective_cache_size подсказывает PostgreSQL общий объем
кешируемых данных (как в буферном кеше, так и в кеше ОС). Чем он
больше, тем более предпочтительным будет индексный доступ. Этот
параметр не влияет на реальное выделение памяти.
11
Локализация настроек
На уровне табличного пространства
ALTER TABLESPACE SET …
На уровне базы данных
ALTER DATABASE SET …
На уровне роли и базы данных
ALTER ROLE [IN DATABASE …] SET …
На уровне процедуры или функции
ALTER ROUTINE SET …
На уровне сеанса или транзакции
SET [LOCAL] …
Обычно рассмотренные параметры устанавливаются в файлах
postgresql.conf или postgresql.auto.conf и влияют на всю систему.
Однако есть возможность ограничить влияние параметров,
устанавливая их на других уровнях.
Часть параметров, касающихся ввода-вывода, можно установить для
табличных пространств.
Остальные параметры можно устанавливать для отдельной базы
данных или отдельной роли (или совокупности роли и базы данных).
Можно также устанавливать параметры на уровне процедур и функций,
что позволяет изолировать настройки отдельного запроса и менять их
без вмешательства в исходный код.
Часть параметров можно устанавливать на уровне отдельного сеанса
или транзакции.
13
Отладка
Отключение определенных способов доступа
enable_seqscan
enable_indexscan, enable_bitmapscan, enable_indexonlyscan
Отключение определенных методов соединений
enable_nestloop, enable_hashjoin, enable_mergejoin
Отключение определенных операций
enable_hashagg, enable_sort, enable_incremental_sort
Проверка возможности распараллеливания
force_parallel_mode
Ряд параметров могут использоваться для запрещения определенных
методов доступа, способов соединений и других операций. Установка
этих параметров в значение off не запрещает операции, но
устанавливает им очень большую стоимость. Таким образом,
планировщик будет пытаться обойтись без них, но может применить
в безвыходной ситуации.
Эти параметры оказывают достаточно грубое влияние на планировщик,
но оказываются весьма полезны для отладки и экспериментов.
Точечно их иногда можно применять как аналог подсказок
планировщику.
На слайде показаны не все параметры этого семейства.
Параметр force_parallel_mode никогда не применяется при реальном
выполнении запросов, но полезен для проверки возможности
распараллеливания.
14
Схема данных
Нормализация — устранение избыточности в данных
упрощает запросы и проверку согласованности
Денормализация — привнесение избыточности
может повысить производительность, но требует синхронизации
индексы
предрассчитанные поля (генерируемые столбцы или триггеры)
материализованные представления
кеширование результатов в приложении
На логическом уровне база данных должна быть нормализованной:
неформально, в хранимых данных не должно быть избыточности.
Если это не так, мы имеем дело с ошибкой проектирования: будет
сложно проверять согласованность данных, возможны различные
аномалии при изменении данных и т. п.
Однако на уровне хранения некоторое дублирование может дать
существенный выигрыш в производительности — ценой того, что
избыточные данные необходимо синхронизировать с основными.
Самый частый способ денормализации — индексы (хотя о них обычно
не думают в таком контексте). Индексы обновляются автоматически.
Можно дублировать некоторые данные (или результаты расчета на
основе этих данных) в столбцах таблиц. Можно использовать
генерируемые столбцы или синхронизировать данные с помощью
триггеров. Так или иначе, за денормализацию отвечает база данных.
Другой пример — материализованные представления. Их также надо
обновлять, например, по расписанию или другим способом.
Дублировать данные можно и на уровне приложения, кешируя
результаты выполнения запросов. Это популярный способ, но часто к
нему прибегают из-за неправильной работы приложения с базой
данных (например, в случае использования ORM). На приложение
ложится задача по своевременному обновлению кеша, обеспечению
разграничения доступа к данным кеша и т. п.
15
Схема данных
Типы данных
выбор подходящих типов
составные типы (массивы, JSON) вместо отдельных таблиц
Ограничения целостности
помимо обеспечения целостности данных, могут учитываться
планировщиком для устранения ненужных соединений, улучшения
оценок селективности и других оптимизаций
первичный ключ и уникальность — уникальный индекс
внешний ключ
отсутствие неопределенных значений
проверка CHECK (constraint_exclusion)
Важен правильный выбор типов данных из всего многообразия,
предлагаемого PostgreSQL. Например, представление интервалов дат
не двумя отдельными столбцами, а с помощью диапазонных типов
(daterange, tstzrange) позволяет использовать индексы GiST и SP-GiST
для таких операций, как пересечение интервалов.
В ряде случаев эффект дает использование составных типов (таких как
массивы или JSON) вместо классического подхода с созданием
отдельной таблицы. Это позволяет сэкономить на соединении и не
требует хранения большого количества служебной информации
в заголовках версий строк. Но такое решение следует принимать
с большой осторожностью, поскольку оно имеет свои минусы.
Ограничения целостности важны сами по себе, но в некоторых
случаях планировщик может учитывать их и для оптимизации.
- Ограничения первичного ключа и уникальности сопровождаются
построением уникальных индексов и гарантируют, что все значения
столбцов ключа различны (точная статистика). Такие гарантии
позволяют и более эффективно выполнять соединения.
- Наличие внешнего ключа и ограничений NOT NULL дает гарантии,
которые позволяют в ряде случаев устранять лишние внутренние
соединения (что особенно важно при использовании представлений),
а также улучшает оценку селективности в случае, если соединение
происходит по нескольким столбцам.
- Ограничение CHECK с использованием параметра constraint_exclusion
позволяет не сканировать таблицы (или секции), если в них
гарантированно нет требуемых данных.
17
Физическое расположение
Табличные пространства
разнесение данных по разным физическим устройствам
Секционирование
разделение таблицы на отдельно управляемые части
для упрощения администрирования и ускорения доступа
Шардирование
размещение секций на разных серверах для масштабирования
нагрузки на чтение и запись
секционирование и расширение postgres-fdw
или сторонние решения
Физическая организация данных может сильно влиять на
производительность. К возможностям такой организации можно отнести
распределение объектов по табличным пространствам и
секционирование.
Табличные пространства можно использовать для того, чтобы
управлять размещением объектов по физическим устройствам ввода-
вывода. Например, активно используемые данные хранить на SSD-
дисках, а архивные — на более медленных HDD.
Секционирование позволяет организовать работу с данными очень
большого объема. Основная выгода для производительности состоит
в замене полного сканирования всей таблицы сканированием
отдельной секции. Заметим, что секции тоже можно размещать по
различным табличным пространствам.
Еще один вариант — размещение секций на разных серверах
(шардирование) с возможностью выполнения распределенных
запросов. Стандартный PostgreSQL содержит только базовые
механизмы, необходимые для организации шардирования:
секционирование и расширение postgres-fdw. Более эффективно и
полноценно шардинг реализуется с помощью внешних решений.
Обзорно этот вопрос рассматривается в последней теме курса DBA3.
18
Порядок соединений
Автоматический выбор порядка соединений
если число соединяемых таблиц не превышает join_collapse_limit —
планировщик выбирает лучший порядок соединений
при большем количестве рассматриваются не все варианты
Ручное управление порядком соединений
материализация подзапросов
с помощью CTE или временных таблиц
явные соединения (JOIN) и join_collapse_limit = 1
подзапросы в предложении FROM и from_collapse_limit = 1
Число различных возможных планов выполнения запроса растет
экспоненциально с ростом числа соединений. Начиная с некоторого
момента (как правило, когда количество соединяемых таблиц
превышает значение join_collapse_limit) планировщик вынужден
сокращать пространство поиска, чтобы не тратить слишком много
времени и оперативной памяти. В таком случае за качество
планирования нельзя поручиться, и, скорее всего, потребуется ручное
вмешательство.
Подробнее об этом можно прочитать в статье Павла Толмачева:
Наиболее удобным способом влияния является разделение таблиц на
группы, каждая из которых будет планироваться независимо. Автор
запроса может выделить такие группы с помощью материализации
общих табличных выражений (или с помощью временных таблиц, хотя
такой способ обычно менее предпочтителен).
Другой способ — указать планировщику, что таблицы должны
соединяться в том порядке, в котором они синтаксически перечислены
в запросе. Для этого надо установить параметр join_collapse_limit = 1
и использовать явные соединения с помощью ключевого слова JOIN.
Аналогично и подзапросы не будут раскрываться при значении
from_collapse_limit = 1. Однако такой способ перекладывает на автора
запроса слишком много ответственности. Планировщик сможет
выбирать только то, какой из наборов строк поставить в соединении
внешним, а какой — внутренним.
20
Изменение запросов
Альтернативные способы выполнения
планировщик не всегда рассматривает все возможные трансформации
раскрытие коррелированных подзапросов
устранение лишних таблиц
замена UNION на OR и обратно
и т. п.
Замена процедурного кода декларативным
чтобы избавиться от большого числа мелких запросов
Самый разнообразный и вариативный способ влияния на
производительность запроса — его переформулирование.
Теоретически планировщик должен уметь выполнять эквивалентные
преобразования (ведь SQL — декларативный язык), но на практике это
возможно в довольно ограниченных пределах.
Иногда требуются ручные изменения:
- Переписывание коррелированных подзапросов на соединения.
- Устранение из запроса лишних сканирований таблиц (например, за
счет применения оконных функций).
- Использование недоступных планировщику трансформаций.
Например, операции для работы со множествами в настоящее время
не трансформируются (UNION в OR и т. п.).
Если приложение выполняет слишком большое количество мелких
запросов (каждый из которых сам по себе выполняется эффективно),
общая эффективность будет очень невысока.
В таких случаях со стороны СУБД практически нет средств для
оптимизации (кроме подготовленных операторов и кеширования).
Единственный действенный метод — избавление от процедурного кода
в приложении и перенос его на сервер БД в виде небольшого числа
крупных SQL-команд. Это дает планировщику возможность применить
более эффективные способы доступа и соединений и избавляет от
многочисленных пересылок данных от клиента к серверу и обратно.
К сожалению, это очень затратный способ.
21
Подсказки оптимизатору
Отсутствуют в явном виде
хотя средства влияния имеются: конфигурационные параметры,
материализация CTE и другие
Сторонние расширения
pg_hint_plan
Еще один (традиционный для других СУБД) способ влияния —
подсказки оптимизатору — отсутствует в PostgreSQL: Это
принципиальное решение сообщества:
На самом деле часть подсказок все-таки неявно существует в виде
конфигурационных параметров и других средств.
Кроме того, есть специальные расширения, например
https://postgrespro.ru/docs/enterprise/13/pg-hint-plan (автор — Киотаро
Хоригучи). Но нельзя забывать, что использование подсказок, сильно
ограничивающих свободу планировщика, может навредить в будущем,
когда распределение данных изменится.
23
Итоги
Доступен широкий спектр методов влияния
на план выполнения запросов
Не все методы применимы во всех случаях
Методы, оказывающие глобальное влияние, следует
применять с осторожностью
Ничто не заменит голову и здравый смысл
24
Практика
1. Оптимизируйте запрос, выводящий контактную
информацию пассажиров, летевших бизнес-классом,
рейсы которых были задержаны более чем на 5 часов.
Начните с варианта, предложенного в комментарии.
2. Проверьте план выполнения запроса, приведенного
в комментарии, при значении параметра from_collapse_limit
по умолчанию и при значении, равном единице.
1.
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 *
FROM
(
SELECT *
FROM ticket_flights tf, tickets t
WHERE tf.ticket_no = t.ticket_no
) ttf,
flights f
WHERE f.flight_id = ttf.flight_id;
