Оптимизация запросов
Функции
16
Авторские права
© Postgres Professional, 2019–2024
Авторы: Егор Рогов, Павел Лузанов, Павел Толмачев, Илья Баштанов
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Категории изменчивости
Подстановка кода функции в текст запроса
Вызов табличных функций
Настройки COST и ROWS
Вспомогательные функции планировщика
Конфигурационные параметры
3
Категории изменчивости
Volatile
возвращаемое значение может произвольно меняться
при одинаковых значениях входных параметров
используется по умолчанию
Stable
значение не меняется в пределах одного оператора SQL
функция не может менять состояние базы данных
Immutable
значение не меняется, функция детерминирована
функция не может менять состояние базы данных
Каждой функции сопоставлена категория изменчивости, которая
определяет свойства возвращаемого значения при одинаковых
значениях входных параметров.
Категория Volatile говорит о том, что возвращаемое значение может
произвольно меняться. Такие функции будут выполняться каждый раз
при каждом вызове. Если при создании функции категория не указана,
назначается именно эта категория.
Категория Stable используется для функций, возвращаемое значение
которых не меняется в пределах одного SQL-оператора. В частности,
такие функции не могут менять состояние БД. Такая функция может
быть выполнена один раз во время выполнения запроса, а затем будет
использоваться вычисленное значение.
Категория Immutable еще более строгая: возвращаемое значение
не меняется никогда. Такую функцию можно выполнить на этапе
планирования запроса, а не во время выполнения.
Можно — не означает, что всегда происходит именно так,
но планировщик вправе выполнить такие оптимизации.
5
Скалярные функции на SQL
один оператор SELECT без предложения FROM,
возвращает одно значение
вызываемые функции не должны быть изменчивее вызывающей
и др.
Табличные функции на SQL
один оператор SELECT
категория Immutable или Stable
функция не STRICT
и др.
Подстановка кода функций
Оптимизатор PostgreSQL умеет подставлять (inline) тело функции
в SQL-запрос. Это работает как со скалярными, так и с табличными
функциями.
В обоих случаях есть много ограничений: функция должна быть
написана на языке SQL, использовать единственный оператор SELECT
и т. д. Скалярная функция, к тому же, не должна обращаться к
таблицам базы данных и вызывать функции, имеющие менее строгую
категорию, а табличная — быть стабильной или постоянной.
Важным преимуществом подстановки тела функции в запрос является
то, что функция становится прозрачной для планировщика. Например,
дополнительные условия в теле основного запроса могут быть
применены к запросу из тела функции, что позволит как можно раньше
отфильтровать лишние строки.
7
Вызов табличных функций
Function Scan
функция fun()
Join
Scan Scan
ProjectSet
SELECT * FROM fun() SELECT fun()
по требованию:
SQL, C, итераторы
Python
Когда табличная функция вызывается в предложении FROM, за ее
вычисление отвечает узел Function Scan. При этом сначала все строки,
возвращаемые функцией, материализуются, и только затем передаются
родительскому узлу плана. Таково текущее ограничение реализации.
Если же функция вызывается в предложении SELECT, в плане она
выполняется в узле ProjectSet. В этом случае функция может
воспользоваться интерфейсом возвращения строк «по требованию»
(value-per-call), без материализации.
Так работают функции на языке SQL, большинство встроенных
функций (написанных на С) и функции на PL/Python, возвращающие
итератор. Функции на других языках программирования тоже могут
использовать этот интерфейс, если такая возможность в них
реализована.
9
Настройки COST и ROWS
CREATE FUNCTION fun( )
SELECT * FROM fun()
CREATE FUNCTION fun( ) ROWS 12 COST 123
SELECT * FROM fun()
Function Scan ( rows=1000 cost=100 )
Function Scan ( rows=12 cost=123 )
ROWS
оценки
по умолчанию
COST
Обычно (если не удалось подставить тело функции в запрос)
оптимизатор не имеет возможности анализировать код функции
и воспринимает ее как «черный ящик».
Однако можно дать оптимизатору приблизительную информацию
о стоимости вызова функции и числе возвращаемых строк.
Параметр COST задает стоимость пользовательской функции
в единицах cpu_operator_cost. По умолчанию функции на C получают
оценку 1, а на других языках — 100.
Параметр ROWS указывает примерное число возвращаемых строк.
COST и ROWS можно указывать как при создании функции, так и для
уже существующих функций.
11
Вспомогательные функции
CREATE FUNCTION fun(x) ROWS 18
SELECT * FROM fun(1) SELECT * FROM fun(2)
CREATE FUNCTION fun(x) SUPPORT fun_support
SELECT * FROM fun(1) SELECT * FROM fun(2)
Function Scan ( rows=18 )
ROWS
Function Scan ( rows=18 )
Function Scan ( rows=12 )
fun_support( 1 )
Function Scan ( rows=24 )
fun_support( 2 )
Параметры COST и ROWS позволяют задать стоимость и количество
строк, возвращаемых функцией, как постоянные величины.
Но константы не всегда дают желаемый результат.
В PostgreSQL есть возможность для функции написать
вспомогательную функцию, которая предоставляет планировщику
информацию, зависящую от значений аргументов основной (целевой)
функции.
Вспомогательная функция может по значениям аргументов целевой
функции выдавать:
●
оценку ее стоимости;
●
оценку числа возвращаемых строк;
●
выражение, эквивалентное вызову функции.
Дополнительно для функций, возвращающих boolean:
●
оценку селективности;
●
эквивалентный предикат с индексируемым оператором.
Вспомогательная функция должна быть написана на языке С.
13
CREATE FUNCTION fun( ) PARALLEL …
Пометки параллельности
SAFE
RESTRICTED
UNSAFE
безопасные для распараллеливания
ограниченно распараллеливаемые
небезопасные для распараллеливания
(по умолчанию)
В теме «Параллельная обработка» было рассказано про то,
что не каждый запрос может выполняться в параллельном режиме.
Поскольку оптимизатор не может проанализировать тело функции,
он рассчитывает на пометки параллельности, определяя по ним
возможность параллельной обработки.
При создании функции (или позже) можно указать одну из трех пометок:
●
UNSAFE — запрещаются параллельные планы, если в запросе есть
вызов функции;
●
RESTRICTED — разрешаются параллельные планы, но запрещено
вызывать функцию в параллельной части плана;
●
SAFE — безопасна для параллельной обработки.
По умолчанию используется пометка UNSAFE.
Пометки параллельности указываются также для пользовательских
агрегатных функций.
15
Итоги
Функция — черный ящик для планировщика,
если ее тело не подставляется в запрос
Вызов табличных функций обычно материализуется
Планировщику можно дать дополнительную информацию
категорию изменчивости функции
кардинальность и стоимость
пометку параллельности
Вспомогательные функции помогают оптимизировать
вызовы встроенных функций
16
Практика
1. Отключите материализацию общего табличного выражения,
в котором вызывается функция random. Объясните результат.
2. Напишите функцию-обертку на SQL для запроса
SELECT * FROM generate_series (1, 10_000_000).
Рассмотрите три варианта: сам исходный запрос и вызовы
функции в предложениях FROM и SELECT. Сравните планы
выполнения запросов и использование временных файлов.
Что меняется при установке категории изменчивости Stable?
3. Какую категорию изменчивости имеет функция
days_of_week из демонстрации? Какова ее изменчивость
на самом деле?
3. Определение функции:
CREATE FUNCTION days_of_week() RETURNS SETOF text
AS $$
BEGIN
FOR i IN 7 .. 13 LOOP
RETURN NEXT to_char(to_date(i::text,'J'),'TMDy');
END LOOP;
END;
$$ LANGUAGE plpgsql;
