sr_plan

Описание

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

sr_plan похож на систему Oracle Outline. Данный модуль можно использовать для блокировки плана выполнения. Это может помочь, если есть сомнения в планировщике или невозможно составить лучший план.

Установка

Модуль управления хранением планов запросов в QHB для QHB поставляется в виде пакета qhb-1.5.2-sr-plan.

Подключите репозиторий пакетов QHB и установите пакет расширения для выбранной платформы со страницы загрузки.

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

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

Обратите внимание, что имена библиотек в переменной shared_preload_libraries должны идти в особом порядке; информацию о совместимости sr_plan с другими расширениями см. в параграфе Совместимость с другими расширениями.

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

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

SHOW shared_preload_libraries;
  1. Создайте расширение sr_plan с помощью следующего запроса:
CREATE EXTENSION sr_plan;

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

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

    • Чтобы включить sr_plan для всех обслуживающих процессов, установите sr_plan.enable = true в файле qhb.conf.

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

SET sr_plan.enable TO true;
  1. Если вы хотите перенести данные sr_plan с основного на резервный сервер, используя физическую репликацию, установите в параметре sr_plan.wal_rw значение on на обоих серверах. В этом случае убедитесь, что на основном и резервном серверах установлена одна версия sr_plan, иначе нет гарантии, что поток репликации будет корректным.

Применение

Если требуется сохранить план запроса, то необходимо установить следующую переменную:

SET sr_plan.write_mode = true;

Теперь планы для всех последующих запросов будут сохраняться в таблице sr_plans. Обратите внимание, что при этом будут сохранены все запросы, включая дубликаты.

Формирование примера запроса:

SELECT query_hash FROM sr_plans WHERE query_hash=10;

Отключение сохранения плана для запроса:

SET sr_plan.write_mode = false;

Включение сохранения:

UPDATE sr_plans SET enable=true;

После этого план запроса будет браться из таблицы sr_plans.

Таблица sr_plans содержит столбцы query_id, которые можно использовать для соединений с таблицами и представлениями pg_stat_statements.

Кроме того, модуль sr_plan позволяет сохранять параметризованный план запроса. При этом некоторые константы в запросе не существенны. Для параметров используется специальная функция _p (anyelement), например:

SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(10);

Если сохранить план для запроса, то можно разрешить его использование для последующих запросов:

SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(11);
SELECT query_hash FROM sr_plans WHERE query_hash=1000+_p(-5);

С помощью функции show_plan можно просмотреть сохраненные планы. Это требует знания хеша запроса, который можно получить из таблицы sr_plans.

Примеры:

Показать включенный план для хеша запроса:

SELECT show_plan(1);
                  show_plan                   
----------------------------------------------
 ("Seq Scan on public.explain_test")
 ("  Output: test_attr1, test_attr2")
 ("  Filter: (explain_test.test_attr1 = 10)")
(3 rows)

Получить второй сохраненный план, используя параметр index (игнорирует атрибут enable):

SELECT show_plan(1, index := 2);
                  show_plan                   
----------------------------------------------
 ("Seq Scan on public.explain_test")
 ("  Output: test_attr1, test_attr2")
 ("  Filter: (explain_test.test_attr1 = 10)")
(3 rows)

Использовать другой формат вывода (поддерживаются форматы json, text, xml, yaml):

SELECT show_plan(1, format := 'json');
                      show_plan                       
------------------------------------------------------
 ("[                                                 +
   {                                                 +
     ""Plan"": {                                     +
       ""Node Type"": ""Seq Scan"",                  +
       ""Parallel Aware"": false,                    +
       ""Relation Name"": ""explain_test"",          +
       ""Schema"": ""public"",                       +
       ""Alias"": ""explain_test"",                  +
       ""Output"": [""test_attr1"", ""test_attr2""], +
       ""Filter"": ""(explain_test.test_attr1 = 10)""+
     }                                               +
   }                                                 +
 ]")
(1 row)

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

Регистрация запроса

Существует два способа зарегистрировать запрос:

SELECT sr_register_query(query_string, parameter_type, ...);

Здесь query_string — это ваш запрос с параметрами $n (так же, как в команде PREPARE statement_name AS). Можно описать тип каждого параметра с помощью необязательного аргумента функции parameter_type или предпочесть не определять типы параметров явно. В последнем случае QHB пытается выяснить тип каждого параметра, исходя из контекста. Эта функция возвращает уникальную пару sql_hash and 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 значения 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 (если это расширение включено) или других расширений, позволяющих менять план запроса. Информацию о совместимости sr_plan с другими расширениями см. в параграфе Совместимость с другими расширениями.

Замораживание плана выполнения запроса

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

Пример использования

Приведенный ниже пример иллюстрирует применение модуля 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;

Типы замороженных планов

Существует два типа замороженных планов: сериализованные и с набором подсказок.

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

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

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

Чтобы гарантировать совместимость sr_plan с другими включенными расширениями, нужно указать имена библиотек в переменной shared_preload_libraries в файле qhb.conf в определенном порядке:

  • pg_hint_plan: расширение sr_plan должно быть загружено после pg_hint_plan.
shared_preload_libraries = 'pg_hint_plan, sr_plan'
  • pg_stat_statements: расширение sr_plan должно быть загружено перед pg_stat_statements.
shared_preload_libraries = 'sr_plan, pg_stat_statements'

Идентификация замороженных запросов

Замороженный запрос в текущей базе данных идентифицируется по комбинации 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 предоставляет подробную информацию обо всех замороженных операторах. Столбцы этого представления описаны в Таблице 3.

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

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

Представление sr_plan_local_cache

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

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

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

Представление sr_captured_queries

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

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

Столбец ТипОписание
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) → record
sr_register_query(query_string text, VARIADIC regtype[]) → record

Сохраняет в локальном кеше запрос, описанный в query_string, и возвращает уникальную пару sql_hash и const_hash.

sr_unregister_query() → bool

Удаляет из локального кеша запрос, который был зарегистрирован, но не был заморожен. Если нет ошибок, возвращает true.

sr_plan_freeze(plan_type text) → bool

Замораживает последний использованный план для оператора. Допустимые значения необязательного аргумента plan_type: serialized и hintset. Значение serialized означает, что используется план запроса, основанный на сериализованном представлении. Со значением hintset расширение sr_plan использует план запроса, основанный на наборе подсказок, формирующемся на стадии выполнения зарегистрированного запроса. Если аргумент plan_type опущен, по умолчанию используется план запроса serialized. Если нет ошибок, возвращает true.

sr_plan_unfreeze(sql_hash bigint, const_hash bigint) → bool

Удаляет план только из хранилища и оставляет план, зарегистрированный в локальном кеше. Если нет ошибок, возвращает true.

sr_plan_remove(sql_hash bigint, const_hash bigint) → bool

Удаляет замороженный оператор с указанными sql_hash и const_hash. Действует как вызванные последовательно функции sr_plan_unfreeze и sr_unregister_query. Если нет ошибок, возвращает true.

sr_plan_reset(dbid oid) → bigint

Удаляет все записи в хранилище sr_plan для указанной базы данных. Чтобы удалить данные, собранные sr_plan для текущей базы данных, опустите аргумент dbid. Чтобы сбросить данные для всех баз данных, установите в dbid значение NULL.

sr_reload_frozen_plancache() → bool

Удаляет все замороженные планы и снова загружает их из хранилища. Также эта функция удаляет операторы, которые были зарегистрированы, но не были заморожены.

sr_plan_fs_counter() → table

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

sr_show_registered_query(sql_hash bigint, const_hash bigint) → table

Возвращает зарегистрированный запрос с указанными sql_hash и const_hash, даже если он не заморожен, только для целей отладки. Это работает, если запрос зарегистрирован в текущем обслуживающем процессе или заморожен в текущей базе данных.

sr_set_plan_type(sql_hash bigint, const_hash bigint, plan_type text) → bool

Устанавливает тип плана запроса для замороженного оператора. Допустимые значения для аргумента plan_type: serialized и hintset. Чтобы иметь возможность использовать план запроса типа hintset, нужно загрузить модуль pg_hint_plan. Если план запроса был успешно изменен, возвращает true.

sr_plan_hintset_update(sql_hash bigint, const_hash bigint, hintset text) → bool

Позволяет изменить сгенерированный набор подсказок на набор пользовательских подсказок. Строка с набором пользовательских подсказок не должна вводиться в специальной форме комментария, как в pg_hint_plan, т. е. она не должна начинаться символами /*+ и заканчиваться символами */. Если план с набором подсказок был успешно изменен, возвращает true.

sr_captured_clean() → 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)
Устанавливает максимальный размер локального кеша, в килобайтах. Значение по умолчанию — ноль, что означает отсутствие ограничений. Этот параметр могут изменять только суперпользователи.

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.max_consts_len (integer)
Устанавливает максимальную длину набора констант в отслеживаемых запросах. Значение по умолчанию — 100. Этот параметр можно установить только при запуске сервера.

sr_plan.sandbox (boolean)
Разрешает резервировать отдельную область в разделяемой памяти для использования основным или резервным узлом, что позволяет тестировать и анализировать запросы с существующим набором данных без влияния на работу узла. При значении on на резервном узле sr_plan замораживает планы только на этом узле и хранит их в «песочнице» — альтернативном хранилище планов. Если же параметр включен на основном узле, расширение использует отдельную область разделяемой памяти, которая не реплицируется на резервный узел. При изменении значения этого параметра сбрасывается кеш sr_plan. Значение по умолчанию — off. Этот параметр могут изменять только суперпользователи.

См. также

Документация по sr_plan на Github

Copyright © KVANTOM LLC, 2019-2025