Информация для оптимизации операторов

примечание

Эта страница переведена при помощи нейросети GigaChat.

Определение оператора PostgreSQL может включать несколько дополнительных предложений, которые сообщают системе полезные сведения о поведении оператора. Старайтесь задавать эти предложения при возможности, так как они могут значительно ускорить выполнение запросов, использующих данный оператор. Но если задаете их, убедитесь, что они верны! Неправильное использование оптимизационного предложений может привести к замедлению выполнения запросов, незаметным ошибкам вывода или другим неприятностям. Всегда можно опустить оптимизационное предложение, если не уверены в нем; единственным последствием будет то, что запросы могут выполняться медленнее, чем необходимо.

В будущих версиях PostgreSQL могут быть добавлены дополнительные оптимизационные предложения. Те, которые описаны здесь, являются всеми теми, которые понимает версия 15.5.

Также возможно прикрепить функцию поддержки планировщика к функции, лежащей в основе оператора, обеспечивая другой способ информирования системы о поведении оператора. См. раздел «Информация об оптимизации функций» для получения дополнительной информации.

COMMUTATOR

Предложение COMMUTATOR, если оно предоставлено, называет оператор, который является коммутатором оператора, определяемого в данный момент. Говорим, что оператор А является коммутатором оператора В, если (x A y) равно (y B x) для всех возможных входных значений x и y. Обратите внимание, что B также является коммутатором A. Например, операторы < и > для определенного типа данных обычно являются взаимными коммутаторами друг друга, а оператор + обычно коммутативен сам с собой. Но оператор - обычно не коммутирует ни с чем.

Тип левого операнда коммутируемого оператора совпадает с типом правого операнда его коммутатора, и наоборот. Таким образом, имя оператора-коммутатора - это все, что нужно предоставить PostgreSQL, чтобы найти коммутатор, и это все, что необходимо указать в предложении COMMUTATOR.

Очень важно предоставлять информацию о коммутаторе для операторов, которые будут использоваться в индексах и условиях соединения, поскольку это позволяет оптимизатору запросов «перевернуть» такое условие к формам, необходимым для различных типов планов. Рассмотрим, например, запрос с условием WHERE, подобным tab1.x = tab2.y, где tab1.x и tab2.y имеют определенный пользователем тип, и предположим, что tab2.y проиндексирован. Оптимизатор не сможет создать сканирование индекса, если он не сможет определить, как перевернуть условие к виду tab2.y = tab1.x, потому что механизм сканирования индекса ожидает увидеть проиндексированный столбец слева от оператора, который ему передан. PostgreSQL не будет просто предполагать, что это допустимое преобразование - создатель оператора = должен указать, что это допустимо, отметив оператор информацией о коммутаторе.

Определение самосопряженного оператора не вызывает сложностей. Определение пары коммутирующих операторов сопряжено с небольшой трудностью: как первому оператору сослаться на второй, который еще не был объявлен?

Существует два варианта решения этой проблемы:

• Один из вариантов — оставить предложение COMMUTATOR пустым в первом операторе, который определяете, а затем добавить его во втором определении. PostgreSQL знает, что коммутирующие операторы образуют пары, поэтому увидев второе определение, он автоматически возвратится назад и заполнит отсутствующее предложение COMMUTATOR в первом определении.

• Другой, более прямолинейный подход — включить предложения COMMUTATOR в обоих определениях. Когда PostgreSQL обрабатывает первое определение и обнаруживает, что COMMUTATOR ссылается на еще не существующий оператор, система создаст фиктивную запись для этого оператора в системном каталоге. Данная фиктивная запись будет содержать достоверные данные только для имени оператора, типов левого и правого операндов и типа результата, так как именно это PostgreSQL может определить на данный момент. Запись каталога первого оператора будет ассоциирована с этой фиктивной записью. Когда определится второй оператор, система обновит фиктивную запись остальной информацией из второго определения. Попытка использовать фиктивный оператор до его заполнения вызовет ошибку.

NEGATOR

Предложение NEGATOR, если присутствует, задает оператор, обратный к определяемому. Говорим, что оператор А является отрицателем оператора В, если оба возвращают логические результаты и (х А у) равно НЕ (х В у) для всех возможных входных значений х, у. Обратите внимание, что В также является обратным к А. Например, < и >= составляют пару обратных друг к другу для большинства типов данных. Оператор никогда не может быть обратным самому себе.

В отличие от коммутаторов, пара унарных операторов могла бы правомерно быть помечена как взаимные отрицатели; это означало бы, что (А х) равно НЕ (В х) для всех х.

Отрицатель оператора должен иметь те же типы операндов слева и/или справа, что и сам оператор, поэтому, так же как и с COMMUTATOR, в предложении NEGATOR нужно указать только имя оператора.

Предоставление отрицателя очень полезно для оптимизатора запросов, поскольку позволяет упрощать выражения типа NOT (x = y) до x <> y. Это происходит чаще, чем можно представить, потому что операции NOT могут быть вставлены в результате других изменений.

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

RESTRICT

Предложение RESTRICT, если оно предоставлено, определяет функцию оценки избирательности ограничения для оператора. (Обратите внимание, что это имя функции, а не имя оператора.) Предложения RESTRICT имеют смысл только для бинарных операторов, которые возвращают boolean. Идея, лежащая в основе оценщика селективности ограничений, заключается в том, чтобы угадать, какая доля строк в таблице будет удовлетворять условию WHERE следующего вида:

column OP constant

Для текущего оператора и конкретного постоянного значения. Это помогает оптимизатору, давая ему некоторое представление о том, сколько строк будет исключено условиями WHERE, имеющими такую форму. (Можно задаться вопросом, что происходит, если константа находится слева? Вот одна из вещей, для которых предназначен COMMUTATOR...).

Написание новых функций оценки избирательности ограничений выходит далеко за рамки этой главы, но обычно можно использовать один из стандартных системных оценщиков для большинства дополнительных операторов. Стандартные оценщики ограничений:

• eqsel для =;
• neqsel для <>;
• scalarltsel для <;
• scalarlesel для <=;
• scalargtsel для >;
• scalargesel для >=.

Часто можно обойтись использованием либо eqsel или neqsel для операторов с очень высокой или очень низкой селективностью, даже если они не являются равенством или неравенством. Например, геометрические операторы приблизительного равенства используют eqsel в предположении, что соответствующие (равные) элементы будут составлять только небольшой процент от всех записей таблицы.

Можно использовать scalarltsel, scalarlesel, scalargtsel и scalargesel для сравнения типов данных, которые имеют какой-то разумный способ преобразования в числовые скаляры для сравнений диапазонов. Если возможно, добавьте тип данных к тем, которые понимает функция convert_to_scalar() в src/backend/utils/adt/selfuncs.c. (В конечном счете, эта функция должна быть заменена функциями для каждого типа данных, идентифицируемыми через столбец каталога системы pg_type; но это еще не произошло.) Если этого не сделать, все будет работать, но оценки оптимизатора не будут такими хорошими, какими могли бы быть.

Еще одна полезная встроенная функция оценки селективности - matchingsel, которая будет работать почти для любого бинарного оператора, если стандартные статистики MCV и/или гистограммы собираются для входных типов данных. Ее оценка по умолчанию установлена вдвое больше, чем оценка по умолчанию, используемая в eqsel, что делает ее наиболее подходящей для операторов сравнения, которые немного менее строгие, чем равенство. Или можно вызвать базовую функцию generic_restriction_selectivity, предоставив другую оценку по умолчанию.

Есть дополнительные функции оценки селективности, предназначенные для геометрических операторов в src/backend/utils/adt/geo_selfuncs.c: areasel, positionsel и contsel. На момент написания документации это просто заглушки, но тем не менее вполне можно использовать (или, еще лучше, доработать) их.

JOIN

Предложение JOIN, если оно предоставлено, называет функцию оценки избирательности соединения для оператора. (Обратите внимание, что это имя функции, а не имя оператора.) Предложения JOIN имеют смысл только для двоичных операторов, которые возвращают boolean. Идея за оценщиком селективности объединения заключается в том, чтобы угадать, какая доля строк в паре таблиц будет удовлетворять условию WHERE вида:

table1.column1 OP table2.column2

Для текущего оператора. Как и в случае с предложением RESTRICT, это помогает оптимизатору очень существенно, позволяя ему определить, какая из нескольких возможных последовательностей соединений, вероятно, потребует наименьших затрат.

Предлагается использовать один из стандартных оценщиков, если он применим:

• eqjoinsel для =;
• neqjoinsel для <>;
• scalarltjoinsel для <;
• scalarlejoinsel для <=;
• scalargtjoinsel для >;
• scalargejoinsel для >=;
• matchingjoinsel для универсальных операторов сопоставления;
• areajoinsel для сравнений на основе областей 2D;
• positionjoinsel для сравнений на основе положения 2D;
• contjoinsel для сравнений на основе содержимого 2D.

HASHES

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

Предположение, лежащее в основе хеширования, заключается в том, что оператор соединения может возвращать истинное значение только для пар левых и правых значений, которые имеют одинаковый хеш-код. Если два значения попадают в разные хеш-блоки, соединение никогда не будет сравнивать их вообще, неявно предполагая, что результатом оператора соединения должно быть ложь. Поэтому никогда не имеет смысла указывать HASHES для операторов, которые не представляют какую-либо форму равенства. В большинстве случаев поддержка хеширования практична только для операторов, которые принимают один и тот же тип данных с обеих сторон. Однако иногда возможно разработать совместимые функции хеширования для двух или более типов данных; то есть функции, которые будут генерировать одинаковые коды хеширования для «равных» значений, даже несмотря на то, что у них разное представление. Например, довольно просто организовать это свойство при хешировании целых чисел разной ширины.

Чтобы пометить HASHES, оператор соединения должен появиться в семействе операторов индекса хеша. Это не контролируется при создании оператора, поскольку, конечно, семейство операторов, на которое делается ссылка, еще не могло существовать. Но попытки использовать оператор в хеш-соединениях завершатся ошибкой во время выполнения, если такое семейство операторов не существует. Система нуждается в семействе операторов для поиска специфичных для типа данных функций хеширования(для входных типов данных оператора). Конечно, также нужно создать подходящие функции хеширования, прежде чем сможете создать семейство операторов.

Следует соблюдать осторожность при подготовке функции хеширования, так как существуют зависящие от машины способы, которыми она может не делать то, что нужно. Например, если тип данных представляет собой структуру, в которой могут быть несущественные биты заполнения, то не можете просто передать всю структуру в hash_any . Если только не напишете другие операторы и функции таким образом, чтобы гарантировать, что неиспользуемые биты всегда равны нулю, что является рекомендуемой стратегией. Еще одним примером является то, что на машинах, соответствующих стандарту IEEE для чисел с плавающей запятой, отрицательный ноль и положительный ноль являются разными значениями (разные шаблоны бит), но они определены как сравнимые по равенству. Если значение с плавающей точкой может содержать отрицательный ноль, необходимы дополнительные шаги, чтобы убедиться, что оно генерирует одно и то же значение хеша, что и положительное значение нуля.

Оператор с возможностью хеширования должен иметь коммутатор (самого себя, если типы данных двух операндов одинаковы, или связанный оператор равенства, если они разные), который появляется в одной и той же семье операторов. Если это не так, могут возникнуть ошибки планировщика при использовании оператора. Кроме того, хорошая идея (но строго не требуется) для семейства хеш-операторов, поддерживающих несколько типов данных, предоставлять операторы равенства для каждой комбинации типов данных; это позволяет лучше оптимизировать.

Примечание

Функция, лежащая в основе оператора с возможностью хеширования, должна быть помечена как неизменяемая или стабильная. Если она изменчива, система никогда не попытается использовать оператор для хеш-соединения.

Примечание

Если оператор с возможностью хеширования имеет базовую функцию, помеченную как строгая, эта функция также должна быть полной: то есть она должна возвращать истинное или ложное значение, никогда не нулевое, для любых двух ненулевых входных значений. Если это правило не соблюдается, оптимизация хеша операций IN может привести к неправильным результатам. (В частности, IN может вернуть ложь там, где правильный ответ согласно стандарту будет нулевым; или он может выдать ошибку, жалуясь на то, что он не был подготовлен к нулевому результату.)

MERGES

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

Слияние соединения основано на идее сортировки левой и правой таблиц в порядке и их параллельном сканировании. Таким образом, оба типа данных должны быть способны полностью упорядочиваться, а оператор соединения должен быть таким, чтобы он мог успешно работать только для пар значений, которые находятся в одном и том же месте в порядке сортировки. На практике это означает, что оператор соединения должен вести себя как равенство. Но возможно выполнить слияние двух различных типов данных до тех пор, пока они логически совместимы. Например, оператор равенства smallint по сравнению с integer является объединяемым слиянием. Нужны только операторы сортировки, которые приведут оба типа данных в логически совместимую последовательность.

Чтобы быть помеченным MERGES, оператор соединения должен появляться в качестве члена равенства оператора семейства индексов btree. Это не контролируется при создании оператора, поскольку, конечно же, ссылающееся семейство операторов еще не могло существовать. Но оператор фактически не будет использоваться для слияния соединений, если не удастся найти соответствующее семейство операторов. Таким образом, флаг MERGES действует как подсказка планировщику о том, что стоит поискать подходящее семейство операторов.

Оператор, допускающий слияние, должен иметь коммутатор (самого себя, если два типа операндов одинаковы, или связанный оператор равенства, если они разные), который появляется в одном и том же семействе операторов. Если это не так, могут возникнуть ошибки планировщика при использовании оператора. Кроме того, хорошая идея (но строго не требуется) для оператора семейства btree, поддерживающего несколько типов данных, предоставлять операторы равенства для каждой комбинации типов данных; это позволяет лучшую оптимизацию.

Примечание

Функция, лежащая в основе оператора, допускающего слияние, должна быть помечена как неизменяемая или стабильная. Если она изменчива, система никогда не попытается использовать этот оператор для слияния.