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

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

G.7.1. Описание
G.7.2. Установка
G.7.3. Использование
G.7.4. Типы замороженных планов
G.7.5. Совместимость с другими расширениями
G.7.6. Идентификация замороженного запроса
G.7.7. Автоматическое приведение типов
G.7.8. Представления
G.7.9. Функции
G.7.10. Параметры конфигурации

Важно

Расширение sr_plan признано устаревшим. Вместо него используйте расширение pgpro_multiplan.

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

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

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

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

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

  1. Добавьте имя библиотеки в переменную shared_preload_libraries в файле postgresql.conf:

    shared_preload_libraries = 'sr_plan'

    Обратите внимание, что имена библиотек в переменной shared_preload_libraries должны добавляться в определённом порядке. Совместимость sr_plan с другими расширениями описана в Подразделе G.7.5.

  2. Перезагрузите сервер баз данных, чтобы изменения вступили в силу.

    Чтобы убедиться, что библиотека sr_plan установлена правильно, вы можете выполнить следующую команду:

    SHOW shared_preload_libraries;

  3. Создайте расширение sr_plan, выполнив следующий запрос:

    CREATE EXTENSION sr_plan;

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

  4. По умолчанию расширение sr_plan выключено. Включите его одним из следующих способов:

    • Чтобы включить sr_plan для всех серверов, в файле postgresql.conf необходимо указать sr_plan.enable = true.

    • Чтобы включить sr_plan в текущем сеансе, воспользуйтесь следующей командой: 

      SET sr_plan.enable TO true;

  5. При необходимости переноса данных sr_plan с главного на резервный сервер при помощи физической репликации, на обоих серверах необходимо задать значение параметра sr_plan.wal_rw=on. Также убедитесь, что на обоих серверах установлена одинаковая версия sr_plan, иначе репликация может работать некорректно.

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

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

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

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

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

G.7.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.7.3.2. Изменение плана выполнения запроса #

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

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

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

G.7.3.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)

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

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

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

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

G.7.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.7.6. Идентификация замороженного запроса #

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

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

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

G.7.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.7.8. Представления #

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

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

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

NameТипОписание
dbidoidИдентификатор базы данных, в которой выполнялся оператор
sql_hashbigintВнутренний идентификатор запроса
const_hashbigintХеш непараметризованных констант
validbooleanFALSE, если план был аннулирован при последнем использовании
query_stringtextЗапрос, зарегистрированный функцией sr_register_query
paramtypesregtype[]Массив с типами параметров, использованными в запросе
querytextВнутреннее представление запроса
plantextВнутреннее представление плана
hintstrtextНабор указаний, сформированный на основе замороженного плана

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

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

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

NameТипОписание
sql_hashbigintВнутренний идентификатор запроса
const_hashbigintХеш непараметризованных констант
fs_is_frozenbooleanTRUE, если оператор был заморожен
fs_is_validbooleanTRUE, если оператор действителен
ps_is_validbooleanTRUE, если оператор должен быть перепроверен
query_stringtextЗапрос, зарегистрированный функцией sr_register_query
querytextВнутреннее представление запроса
paramtypesregtype[]Массив с типами параметров, использованными в запросе
hintstrtextНабор указаний, сформированный на основе замороженного плана

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

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

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

NameТипОписание
dbidoidИдентификатор базы данных, в которой выполнялся оператор
sql_hashbigintВнутренний идентификатор запроса
queryidbigintСтандартный идентификатор запроса
sample_stringtextЗапрос, выполненный в режиме автоматического отслеживания запросов
query_stringtextПараметризованный запрос
constantstextНабор констант в запросе
prep_conststextНабор констант, использованных для выполнения (EXECUTE) подготовленного оператора
hintstrtextНабор указаний, сформированный на основе плана
explain_plantextПлан, показанный командой EXPLAIN

G.7.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.7.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.6. pgpro_stats — сбор статистики планирования и выполнения всех обрабатываемых сервером SQL-операторов Начало G.8. utl_http — доступ к данным в Интернете по протоколу HTTP