Postgres Pro Enterprise : Документация: 16: G.4. sr_plan — сохранение отдельных планов параметризованных запросов для использования в будущем : Компания Postgres Professional

RU
EN
RU EN

G.4. sr_plan — сохранение отдельных планов параметризованных запросов для использования в будущем
Пред.	Наверх	Приложение G. Модули и расширения Postgres Pro, поставляемые в виде отдельных пакетов	Начало	След.

G.4. sr_plan — сохранение отдельных планов параметризованных запросов для использования в будущем #

G.4.1. Описание
G.4.2. Установка
G.4.3. Использование
G.4.4. Типы замороженных планов
G.4.5. Совместимость с другими расширениями
G.4.6. Идентификация замороженного запроса
G.4.7. Автоматическое приведение типов
G.4.8. Представления
G.4.9. Функции
G.4.10. Параметры конфигурации

G.4.1. Описание #

Расширение sr_plan позволяет пользователю сохранять планы выполнения запросов и использовать эти планы при последующем выполнении тех же запросов, что помогает избежать повторной оптимизации идентичных запросов.

sr_plan действует подобно системе Oracle Outline. С его помощью можно жёстко зафиксировать план выполнения, если нужен вариант, отличный от предлагаемого планировщиком.

G.4.2. Установка #

Расширение sr_plan поставляется вместе с Postgres Pro Enterprise в виде отдельного пакета sr-plan-ent-16 (подробные инструкции по установке приведены в Главе 17). Чтобы включить sr_plan, выполните следующие действия:

Добавьте имя библиотеки в переменную shared_preload_libraries в файле postgresql.conf:
```
shared_preload_libraries = 'sr_plan'
```
Обратите внимание, что имена библиотек в переменной shared_preload_libraries должны добавляться в определённом порядке. Совместимость sr_plan с другими расширениями описана в Подразделе G.4.5.
Перезагрузите сервер баз данных, чтобы изменения вступили в силу.
Чтобы убедиться, что библиотека sr_plan установлена правильно, вы можете выполнить следующую команду:
```
SHOW shared_preload_libraries;
```
Создайте расширение sr_plan, выполнив следующий запрос:
```
CREATE EXTENSION sr_plan;
```
sr_plan использует кеш разделяемой памяти, который инициализируется только при запуске сервера, поэтому данная библиотека также должна предзагружаться при запуске. Расширение sr_plan следует создать в каждой базе данных, где требуется управление запросами.
По умолчанию расширение sr_plan выключено. Включите его одним из следующих способов:
- Чтобы включить sr_plan для всех серверов, в файле postgresql.conf необходимо указать sr_plan.enable = true.
- Чтобы включить sr_plan в текущем сеансе, воспользуйтесь следующей командой:
```
SET sr_plan.enable TO true;
```
При необходимости переноса данных sr_plan с главного на резервный сервер при помощи физической репликации, на обоих серверах необходимо задать значение параметра sr_plan.wal_rw=on. Также убедитесь, что на обоих серверах установлена одинаковая версия sr_plan, иначе репликация может работать некорректно.

G.4.3. Использование #

Расширение sr_plan позволяет замораживать планы запросов для дальнейшего использования. Заморозка состоит из трёх этапов:

Регистрация запроса, план которого необходимо заморозить.
Изменение плана выполнения запроса.
Заморозка плана выполнения запроса.

G.4.3.1. Регистрация запроса #

Зарегистрировать запрос можно двумя способами:

С помощью функции sr_register_query():
```
SELECT sr_register_query(query_string, parameter_type, ...);
```
Здесь query_string — запрос с параметрами $n (аналогично PREPARE statement_name AS). Можно описать каждый тип параметра, используя необязательный аргумент функции parameter_type, или отказаться от явного определения типов параметров. В последнем случае Postgres Pro пытается определить тип каждого параметра из контекста. Эта функция возвращает уникальную пару sql_hash и const_hash. После этого sr_plan будет отслеживать выполнение запросов, соответствующих сохранённому шаблону параметризованного запроса.
```
-- Создайте таблицу 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Зарегистрируйте запрос
SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
        sql_hash       | const_hash
----------------------+------------
  5393873830515778388 |  15498345
(1 row)
```

С помощью параметра sr_plan.auto_tracking:

-- Установите для sr_plan.auto_tracking значение on
SET sr_plan.auto_tracking = on;

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

EXPLAIN SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;

Custom Scan (SRScan)  (cost=1.60..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: 5393873830515778388
  Const hash: 0
  ->  Aggregate  (cost=1.60..1.61 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.60 rows=2 width=0)
              Filter: ((x = $1) OR ((x > $2) AND (x < $3)) OR (x = $4))

G.4.3.2. Изменение плана выполнения запроса #

План выполнения запроса можно изменить при помощи переменных оптимизатора, указаний pg_hint_plan при включённом расширении или других расширений, например aqo. Информация о совместимости sr_plan с другими расширениями представлена в Подразделе G.4.5.

G.4.3.3. Заморозка плана выполнения запроса #

Для заморозки плана выполнения запроса используйте функцию sr_plan_freeze. Для необязательного параметра plan_type можно задать значение serialized или hintset. Значение по умолчанию — serialized. Более подробно типы замороженных планов описаны в Подразделе G.4.4.

G.4.3.4. Пример использования #

Ниже показано, как использовать sr_plan.

-- Зарегистрируйте запрос
SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = 1 OR (x > $2 AND x < $1) OR x = $1', 'int', 'int')
        sql_hash       | const_hash
----------------------+------------
  5393873830515778388 |  15498345
(1 row)

-- Измените план выполнения запроса
SET enable_seqscan = 'off';

Custom Scan (SRScan) (actual rows=1 loops=1)
  Plan is: tracked
  SQL hash: 5393873830515778388
  Const hash: 15498345
  ->  Aggregate (actual rows=1 loops=1)
        ->  Index Only Scan using a_x_idx2 on a (actual rows=10 loops=1)
              Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
              Rows Removed by Filter: 20
              Heap Fetches: 30
(5 rows)

-- Заморозьте план выполнения запроса
SELECT sr_plan_freeze();
RESET enable_seqscan;

G.4.4. Типы замороженных планов #

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

Сериализованный план (serialized) — это сериализованное представление плана. Этот план становится выполняемым планом при первом нахождении соответствующего замороженного запроса. Сериализованный план действителен до тех пор, пока не изменятся метаданные запроса (структуры таблиц, индексы и так далее). В случае пересоздания таблицы, которая присутствует в замороженном плане, данный замороженный план становится недействительным и игнорируется. Сериализованный план действителен только в текущей базе данных и не может быть скопирован в другую, поскольку зависит от идентификаторов объектов. По этой причине использовать сериализованные планы для временных таблиц не имеет смысла.
План hintset — это план с набором указаний, формируемым в момент заморозки на основе плана выполнения. Этот набор состоит из значений переменных окружения оптимизатора, отличных от используемых по умолчанию, типов соединений, порядка соединений и методов доступа к данным. Указания соответствуют указаниям, которые поддерживаются расширением pg_hint_plan. Для использования таких планов необходимо включить расширение pg_hint_plan. Набор указаний передаётся планировщику pg_hint_plan при первом нахождении соответствующего замороженного запроса, после чего pg_hint_plan формирует выполняемый план. В случае отсутствия данного расширения указания игнорируются и выполняется план, сформированный оптимизатором Postgres Pro. План с указаниями не зависит от идентификаторов объектов и остаётся действительным при пересоздании таблиц, добавлении полей и других изменениях.

G.4.5. Совместимость с другими расширениями #

Для обеспечения совместимости sr_plan с другими расширениями необходимо в файле postgresql.conf в переменной shared_preload_libraries указывать имена библиотек в определённом порядке:

pg_hint_plan: расширение sr_plan необходимо загружать после pg_hint_plan.
```
shared_preload_libraries = 'pg_hint_plan, sr_plan'
```
aqo: расширение sr_plan необходимо загружать до aqo.
```
shared_preload_libraries = 'sr_plan, aqo'
```
pgpro_stats: расширение sr_plan необходимо загружать после pgpro_stats.
```
shared_preload_libraries = 'pgpro_stats, sr_plan'
```

G.4.6. Идентификация замороженного запроса #

Замороженный запрос в текущей БД идентифицируется уникальной парой sql_hash и const_hash.

sql_hash— это хеш, сформированный на основе дерева разбора без учёта параметров и констант. Псевдонимы полей и таблиц не игнорируются, поэтому один и тот же запрос с разными псевдонимами будет иметь разные значение sql_hash.

const_hash — хеш, сгенерированный на основе всех присутствующих в запросе констант. Константы с одинаковым значением, но разным типом, например 1 и '1', выдадут разное значение хеша.

G.4.7. Автоматическое приведение типов #

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

SELECT sql_hash, const_hash
FROM sr_register_query('SELECT count(*) FROM a
WHERE x = $1', 'int');

-- Приведение типов возможно
EXPLAIN SELECT count(*) FROM a WHERE x = '1';
                     QUERY PLAN
-------------------------------------------------------------
Custom Scan (SRScan)  (cost=1.38..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: -5166001356546372387
  Const hash: 0
  ->  Aggregate  (cost=1.38..1.39 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
              Filter: (x = $1)

-- Приведение типов возможно
EXPLAIN SELECT count(*) FROM a WHERE x = 1::bigint;
                     QUERY PLAN
-------------------------------------------------------------
Custom Scan (SRScan)  (cost=1.38..0.00 rows=1 width=8)
  Plan is: tracked
  SQL hash: -5166001356546372387
  Const hash: 0
  ->  Aggregate  (cost=1.38..1.39 rows=1 width=8)
        ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
              Filter: (x = $1)

-- Приведение типов невозможно
EXPLAIN SELECT count(*) FROM a WHERE x = 1111111111111;
                  QUERY PLAN
-------------------------------------------------------
 Aggregate  (cost=1.38..1.39 rows=1 width=8)
   ->  Seq Scan on a  (cost=0.00..1.38 rows=1 width=0)
         Filter: (x = '1111111111111'::bigint)

G.4.8. Представления #

G.4.8.1. Представление `sr_plan_storage` #

Представление sr_plan_storage содержит подробную информацию обо всех замороженных операторах. Столбцы представления показаны в Таблице G.84.

Таблица G.84. Столбцы sr_plan_storage

Name	Тип	Описание
`dbid`	`oid`	Идентификатор базы данных, в которой выполнялся оператор
`sql_hash`	`bigint`	Внутренний идентификатор запроса
`const_hash`	`bigint`	Хеш непараметризованных констант
`valid`	`boolean`	`FALSE`, если план был аннулирован при последнем использовании
`query_string`	`text`	Запрос, зарегистрированный функцией `sr_register_query`
`paramtypes`	`regtype[]`	Массив с типами параметров, использованными в запросе
`query`	`text`	Внутреннее представление запроса
`plan`	`text`	Внутреннее представление плана
`hintstr`	`text`	Набор указаний, сформированный на основе замороженного плана

G.4.8.2. Представление `sr_plan_local_cache` #

Представление sr_plan_local_cache содержит подробную информацию о зарегистрированных и замороженных операторах в локальном кеше. Столбцы представления показаны в Таблице G.85.

Таблица G.85. Столбцы sr_plan_local_cache

Name	Тип	Описание
`sql_hash`	`bigint`	Внутренний идентификатор запроса
`const_hash`	`bigint`	Хеш непараметризованных констант
`fs_is_frozen`	`boolean`	`TRUE`, если оператор был заморожен
`fs_is_valid`	`boolean`	`TRUE`, если оператор действителен
`ps_is_valid`	`boolean`	`TRUE`, если оператор должен быть перепроверен
`query_string`	`text`	Запрос, зарегистрированный функцией `sr_register_query`
`query`	`text`	Внутреннее представление запроса
`paramtypes`	`regtype[]`	Массив с типами параметров, использованными в запросе
`hintstr`	`text`	Набор указаний, сформированный на основе замороженного плана

G.4.8.3. Представление `sr_captured_queries` #

Представление sr_captured_queries содержит подробную информацию обо всех запросах, отслеживаемых в сеансах. Столбцы представления показаны в Таблице G.86.

Таблица G.86. Столбцы sr_captured_queries

Name	Тип	Описание
`dbid`	`oid`	Идентификатор базы данных, в которой выполнялся оператор
`sql_hash`	`bigint`	Внутренний идентификатор запроса
`queryid`	`bigint`	Стандартный идентификатор запроса
`sample_string`	`text`	Запрос, выполненный в режиме автоматического отслеживания запросов
`query_string`	`text`	Параметризованный запрос
`constants`	`text`	Набор констант в запросе
`prep_consts`	`text`	Набор констант, использованных для выполнения (`EXECUTE`) подготовленного оператора
`hintstr`	`text`	Набор указаний, сформированный на основе плана
`explain_plan`	`text`	План, показанный командой `EXPLAIN`

G.4.9. Функции #

Вызывать нижеуказанные функции может только суперпользователь.

sr_register_query(query_string text) returns record sr_register_query(query_string text, VARIADIC regtype[]) returns record #: Сохраняет запрос, описанный в query_string, в локальном кеше и возвращает уникальную пару sql_hash и const_hash.
sr_unregister_query() returns bool #: Удаляет из локального кеша запрос, который был зарегистрирован, но не был заморожен. Если нет ошибок, возвращает true.
sr_plan_freeze(plan_type text) returns bool #: Замораживает последний использованный план для оператора. Допустимые значения необязательного аргумента plan_type: serialized и hintset. Значение serialized показывает, что используется план запроса, основанный на сериализованном представлении. При использовании hintset sr_plan использует план запроса на основе набора указаний, который формируется на этапе выполнения зарегистрированного запроса. Если аргумент plan_type опущен, по умолчанию используется serialized план запроса. При отсутствии ошибок возвращает true.
sr_plan_unfreeze(sql_hash bigint, const_hash bigint) returns bool #: Удаляет план только из хранилища, но оставляет запрос в локальном кеше. Если нет ошибок, возвращает true.
sr_plan_remove(sql_hash bigint, const_hash bigint) returns bool #: Удаляет замороженный оператор с указанными sql_hash и const_hash. Работает как функции sr_plan_unfreeze и sr_unregister_query, вызываемые последовательно. Если нет ошибок, возвращает true.
sr_plan_reset(dbid oid) returns bigint #: Удаляет все записи в хранилище sr_plan для указанной базы данных. Чтобы удалить данные, собранные sr_plan для текущей базы данных, не указывайте dbid. Чтобы сбросить данные для всех баз данных, установите для параметра dbid значение NULL.
sr_reload_frozen_plancache() returns bool #: Удаляет все замороженные планы и снова загружает их из хранилища. Также удаляет операторы, которые были зарегистрированы, но не заморожены.
sr_plan_fs_counter() returns table #: Возвращает количество использований каждого замороженного оператора и идентификатор базы данных, в которой этот оператор был зарегистрирован и использован.
sr_show_registered_query(sql_hash bigint, const_hash bigint) returns table #: Возвращает зарегистрированный запрос с указанными sql_hash и const_hash, даже если он не заморожен, только для целей отладки. Работает, если запрос зарегистрирован в текущем обслуживающем процессе или заморожен в текущей базе данных.
sr_set_plan_type(sql_hash bigint, const_hash bigint, plan_type text) returns bool #: Устанавливает тип плана запроса для замороженного оператора. Допустимые значения аргумента plan_type: serialized и hintset. Чтобы иметь возможность использовать план запроса типа hintset, необходимо загрузить модуль pg_hint_plan. Если тип плана был успешно изменён, возвращает true.
sr_plan_hintset_update(sql_hash bigint, const_hash bigint, hintset text) returns bool #: Позволяет изменить сгенерированный список указаний на пользовательский набор указаний. Строка с такими пользовательскими указаниями задаётся не в виде особого комментария, как в pg_hint_plan, то есть она не должна начинаться с последовательности символов /*+ и заканчиваться последовательностью */. Если план с указаниями изменён успешно, возвращается true.
sr_captured_clean() returns bigint #: Удаляет все записи из представления sr_captured_queries. Функция возвращает количество удалённых записей.

G.4.10. Параметры конфигурации #

sr_plan.enable (boolean) #: Позволяет sr_plan использовать замороженные планы. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.
sr_plan.fs_ctr_max (integer) #: Задаёт максимальное количество замороженных операторов, возвращаемых функцией sr_plan_fs_counter(). Значение по умолчанию — 5000. Этот параметр можно задать только при запуске сервера.
sr_plan.max_items (integer) #: Задаёт максимальное количество записей, с которым может работать sr_plan. Значение по умолчанию — 100. Этот параметр можно задать только при запуске сервера.
sr_plan.auto_tracking (boolean) #: Позволяет sr_plan автоматически нормализовать и регистрировать запросы, выполняемые с использованием команды EXPLAIN. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.
sr_plan.max_local_cache_size (integer) #: Задаёт максимальный размер локального кеша, в килобайтах. Значение по умолчанию — 0, что означает отсутствие ограничений. Изменить этот параметр могут только суперпользователи.
sr_plan.wal_rw (boolean) #: Включает физическую репликацию данных sr_plan. При значении off на главном сервере данные на резервный сервер не передаются. При значении off на резервном сервере любые данные, передаваемые с главного сервера, игнорируются. Значение по умолчанию — off. Этот параметр можно задать только при запуске сервера.
sr_plan.auto_capturing (boolean) #: Включает автоматическое отслеживание запросов в sr_plan. Если для этого параметра конфигурации установить значение on, в представлении sr_captured_queries можно будет увидеть запросы с константами в текстовой форме и параметризованные запросы. Информация о выполненных запросах хранится до перезапуска сервера. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.
sr_plan.max_captured_items (integer) #: Задаёт максимальное количество запросов, которые может отслеживать sr_plan. Значение по умолчанию — 1000. Этот параметр можно задать только при запуске сервера.
sr_plan.sandbox (boolean) #: Включает резервирование отдельных зон разделяемой памяти для ведущего и резервного узла, что позволяет тестировать и анализировать запросы с существующим набором данных без влияния на работу узла. Если на резервном узле установлено значение on, sr_plan замораживает планы выполнения запросов только на этом узле и хранит их в альтернативном хранилище планов — «песочнице». Если параметр включён на ведущем узле, расширение использует отдельную зону разделяемой памяти, данные которой не реплицируются на резервные узлы. При изменении значения параметра сбрасывается кеш sr_plan. Значение по умолчанию — off. Изменить этот параметр могут только суперпользователи.

Пред.	Наверх	След.
G.3. pgpro_stats — сбор статистики планирования и выполнения всех обрабатываемых сервером SQL-операторов	Начало	Приложение H. Сторонние модули и расширения, поставляемые в виде отдельных пакетов

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля