Расширение sr_plan признано устаревшим. Вместо него используйте расширение pgpro_multiplan.
Расширение sr_plan позволяет пользователю сохранять планы выполнения запросов и использовать эти планы при последующем выполнении тех же запросов, что помогает избежать повторной оптимизации идентичных запросов.
sr_plan действует подобно системе Oracle Outline. С его помощью можно жёстко зафиксировать план выполнения, если нужен вариант, отличный от предлагаемого планировщиком.
Расширение 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.8.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, иначе репликация может работать некорректно.
Расширение sr_plan позволяет замораживать планы запросов для дальнейшего использования. Заморозка состоит из трёх этапов:
Регистрация запроса, план которого необходимо заморозить.
Изменение плана выполнения запроса.
Заморозка плана выполнения запроса.
Зарегистрировать запрос можно двумя способами:
С помощью функции sr_register_query():
SELECT sr_register_query(query_string,parameter_type, ...);
Здесь query_string — запрос с параметрами $ (аналогично nPREPARE ). Можно описать каждый тип параметра, используя необязательный аргумент функции statement_name ASparameter_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))План выполнения запроса можно изменить при помощи переменных оптимизатора, указаний pg_hint_plan при включённом расширении или других расширений, например aqo. Информация о совместимости sr_plan с другими расширениями представлена в Подразделе G.8.5.
Для заморозки плана выполнения запроса используйте функцию sr_plan_freeze. Для необязательного параметра plan_type можно задать значение serialized или hintset. Значение по умолчанию — serialized. Более подробно типы замороженных планов описаны в Подразделе G.8.4.
Ниже показано, как использовать sr_plan.
CREATE EXTENSION sr_plan;
SET sr_plan.enable = ON;
-- Зарегистрируйте запрос
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';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
-------------------------------------------------------------------------
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_idx on a (actual rows=12 loops=1)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
Rows Removed by Filter: 18
Heap Fetches: 30
Planning Time: 1.085 ms
Execution Time: 0.085 ms
(11 rows)
-- Заморозьте план выполнения запроса
SELECT sr_plan_freeze();
RESET enable_seqscan;
-- Теперь используется замороженный план
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM a WHERE x = 1 OR (x > 11 AND x < 22) OR x = 22;
QUERY PLAN
-------------------------------------------------------------------------
Custom Scan (SRScan) (actual rows=1 loops=1)
Plan is: frozen, serialized
SQL hash: 5393873830515778388
Const hash: 15498345
-> Aggregate (actual rows=1 loops=1)
-> Index Only Scan using a_x_idx on a (actual rows=12 loops=1)
Filter: ((x = 1) OR ((x > $2) AND (x < $1)) OR (x = $1))
Rows Removed by Filter: 18
Heap Fetches: 30
Planning Time: 0.327 ms
Execution Time: 0.081 ms
(11 rows)Есть два типа замороженных планов: сериализованные и планы с указаниями.
Сериализованный план (serialized) — это сериализованное представление плана. Этот план становится выполняемым планом при первом нахождении соответствующего замороженного запроса. Сериализованный план действителен до тех пор, пока не изменятся метаданные запроса (структуры таблиц, индексы и так далее). В случае пересоздания таблицы, которая присутствует в замороженном плане, данный замороженный план становится недействительным и игнорируется. Сериализованный план действителен только в текущей базе данных и не может быть скопирован в другую, поскольку зависит от идентификаторов объектов. По этой причине использовать сериализованные планы для временных таблиц не имеет смысла.
План hintset — это план с набором указаний, формируемым в момент заморозки на основе плана выполнения. Этот набор состоит из значений переменных окружения оптимизатора, отличных от используемых по умолчанию, типов соединений, порядка соединений и методов доступа к данным. Указания соответствуют указаниям, которые поддерживаются расширением pg_hint_plan. Для использования таких планов необходимо включить расширение pg_hint_plan. Набор указаний передаётся планировщику pg_hint_plan при первом нахождении соответствующего замороженного запроса, после чего pg_hint_plan формирует выполняемый план. В случае отсутствия данного расширения указания игнорируются и выполняется план, сформированный оптимизатором Postgres Pro. План с указаниями не зависит от идентификаторов объектов и остаётся действительным при пересоздании таблиц, добавлении полей и других изменениях.
Для обеспечения совместимости 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'
Замороженный запрос в текущей БД идентифицируется уникальной парой sql_hash и const_hash.
sql_hash— это хеш, сформированный на основе дерева разбора без учёта параметров и констант. Псевдонимы полей и таблиц не игнорируются, поэтому один и тот же запрос с разными псевдонимами будет иметь разные значение sql_hash.
const_hash — хеш, сгенерированный на основе всех присутствующих в запросе констант. Константы с одинаковым значением, но разным типом, например 1 и '1', выдадут разное значение хеша.
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)sr_plan_storage #Представление sr_plan_storage содержит подробную информацию обо всех замороженных операторах. Столбцы представления показаны в Таблице G.97.
Таблица G.97. Столбцы 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 | Набор указаний, сформированный на основе замороженного плана |
sr_plan_local_cache #Представление sr_plan_local_cache содержит подробную информацию о зарегистрированных и замороженных операторах в локальном кеше. Столбцы представления показаны в Таблице G.98.
Таблица G.98. Столбцы 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 | Набор указаний, сформированный на основе замороженного плана |
sr_captured_queries #Представление sr_captured_queries содержит подробную информацию обо всех запросах, отслеживаемых в сеансах. Столбцы представления показаны в Таблице G.99.
Таблица G.99. Столбцы 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 |
Вызывать нижеуказанные функции может только суперпользователь.
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. Функция возвращает количество удалённых записей.
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. Изменить этот параметр могут только суперпользователи.