PREPARE

примечание

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

PREPARE - создание подготовленного оператора.

Синтаксис

PREPARE name [ ( data_type [, ...] ) ] AS statement

Описание

PREPARE создает подготовленный оператор. Подготовленный оператор является объектом на стороне сервера, предназначенный для оптимизации производительности запросов. Когда выполняется команда PREPARE, указанный SQL-запрос проходит этапы синтаксического разбора, анализа и переписывания. Позднее, при выполнении команды EXECUTE, это подготовленное выражение планируется и выполняется. Такое разделение выполнения позволяет избежать многократного анализа выражения, при этом учитывая конкретные значения параметров при создании плана выполнения.

Подготовленные операторы могут использовать параметры — значения, которые подставляются в оператор при его исполнении. При создании подготовленного оператора параметры указываются по порядковым номерам — $1, $2, и так далее. Можно также указать соответствующие типы данных параметров. Если тип данных параметра не указан или явно указан как unknown, тип будет выведен автоматически из контекста первого использования параметра, если это возможно. При выполнении оператора необходимо передать конкретные значения параметров в команде EXECUTE. Подробности смотрите в описании команды EXECUTE.

Подготовленные операторы существуют только в течение текущей сессии с базой данных. Когда сессия завершается, подготовленные операторы удаляются и должны быть созданы заново при следующем подключении. Это также означает, что один и то же подготовленный оператор не может быть использовано несколькими клиентами одновременно. Однако каждый клиент может создать свой собственный оператор для использования в своей сессии. Подготовленные операторы можно удалить вручную с помощью команды DEALLOCATE.

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

Параметры

name

Произвольное имя, присваиваемое данному подготовленному оператору. Имя должно быть уникальным в рамках одной сессии. Оно используется позже для выполнения (EXECUTE) или удаления (DEALLOCATE) подготовленного оператора.

data_type

Тип данных параметра. Если тип не указан или задан как unknown, PostgreSQL попытается вывести его из контекста первого использования параметра в теле оператора. Внутри подготовленного оператора параметры обозначаются как $1, $2, и так далее.

statement

SQL-запрос любого из следующих видов: SELECT, INSERT, UPDATE, DELETE, MERGE или VALUES.

Примечания

Подготовленный оператор может выполняться с использованием обобщенного плана или специализированного плана. Обобщенный план создается один раз и используется для всех последующих выполнений выражения. Специализированный план создается заново каждый раз на основе конкретных значений параметров.

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

По умолчанию, если параметр plan_cache_mode установлен в значение auto, сервер сам решает, какой тип плана использовать для подготовленного выражения, содержащего параметры. Текущий алгоритм следующий:

1. Первые пять выполнений — со специализированным планом.
2. Вычисляется средняя оценочная стоимость этих пяти выполнений.
3. Создается обобщенный план, и его оценочная стоимость сравнивается со средней стоимостью специализированных планов.
4. Если обобщенный план не оказывается существенно хуже, он используется для всех последующих выполнений.

Логику выбора плана можно переопределить, установив параметр plan_cache_mode в значение force_generic_plan (принудительно обобщенный план) или force_custom_plan (принудительно специализированный план). Такая настройка может быть полезна в ситуациях, когда оценка стоимости обобщенного плана ошибочна, и он выбирается сервером несмотря на то, что на практике он оказывается менее эффективным.

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

EXPLAIN EXECUTE name(parameter_values);

Если используется обобщенный план, то в выводе будут отображаться параметры в виде $n. Если используется специализированный план, то значения параметров будут подставлены непосредственно в план.

Дополнительную информацию о планировании запросов и о статистике, собираемой PostgreSQL для этих целей, можно найти в описании команды ANALYZE.

Хотя основное назначение подготовленного выражения — избежать повторного синтаксического анализа и планирования, PostgreSQL может принудительно выполнить повторную переработку оператора перед его использованием, если:

• объекты базы данных, используемые в выражении, были изменены (DDL-операции);
• была обновлена статистика, используемая планировщиком;
• между выполнениями изменился параметр search_path.

Поведение, связанное с search_path, актуально начиная с версии PostgreSQL 9.3. Эти правила обеспечивают семантическую эквивалентность подготовленного оператора повторной отправке SQL-запроса как текста, но при этом позволяют достичь преимущества в производительности в случаях, когда определения объектов не меняются и подходящий план остается тем же.

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

Список всех подготовленных выражений, доступных в текущей сессии, можно посмотреть с помощью представления pg_prepared_statements.

Примеры

Создание подготовленного оператора для команды INSERT и его выполнение:

PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

Создание подготовленного оператора для команды SELECT и его выполнение:

PREPARE usrrptplan (int) AS
    SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid
    AND l.date = $2;
EXECUTE usrrptplan(1, current_date);

В этом примере тип данных второго параметра не указан, поэтому он выводится из контекста, в котором используется оператор $2.

Совместимость

Стандарт SQL включает команду PREPARE, но она предназначена только для использования во встроенном SQL. Эта версия команды PREPARE использует несколько иной синтаксис.

Смотрите также

DEALLOCATE, EXECUTE