Postgres Pro Enterprise
1615131211109.6
Postgres Pro Standard
131211109.69.5
RUENRU EN
G.6. sr_plan
Пред. НаверхПриложение G. Модули и расширения Postgres Pro, поставляемые в виде отдельных пакетовНачало След.

G.6. sr_plan

G.6.1. Описание
G.6.2. Установка
G.6.3. Использование
G.6.4. Типы замороженных планов
G.6.5. Совместимость с другими расширениями
G.6.6. Идентификация замороженного запроса
G.6.7. Автоматическое приведение типов
G.6.8. Представления
G.6.9. Функции
G.6.10. Параметры конфигурации

Важно

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

G.6.1. Описание

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

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

G.6.2. Установка

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

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

    shared_preload_libraries = 'sr_plan'

    Обратите внимание, что имена библиотек в переменной shared_preload_libraries должны добавляться в определённом порядке. Совместимость sr_plan с другими расширениями описана в Подразделе G.6.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.6.3. Использование

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

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

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

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

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

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

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

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

G.6.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.6.4. Типы замороженных планов

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

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

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

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

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

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

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

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

G.6.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.6.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.6.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.6.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.6.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.5. pgpro_stats Начало G.7. pgpro_validate — проверка целостности данных экземпляра Postgres Pro
G.6. sr_plan
Prev UpAppendix G. Postgres Pro Modules and Extensions Shipped as Individual PackagesHome Next

G.6. sr_plan

G.6.1. Description
G.6.2. Installation
G.6.3. Usage
G.6.4. Frozen Plan Types
G.6.5. Compatibility with Other Extensions
G.6.6. Frozen Query Identification
G.6.7. Automatic Type Casting
G.6.8. Views
G.6.9. Functions
G.6.10. Configuration Parameters

Important

The sr_plan extension is considered deprecated. Use the pgpro_multiplan extension instead.

G.6.1. Description

sr_plan allows the user to save query execution plans and utilize these plans for subsequent executions of the same queries, thereby avoiding repeated optimization of identical queries.

sr_plan looks like Oracle Outline system. It can be used to lock the execution plan. It could help if you do not trust the planner.

G.6.2. Installation

The sr_plan extension is provided with Postgres Pro Enterprise as a separate pre-built package sr-plan-ent-15 (for the detailed installation instructions, see Chapter 17). To enable sr_plan, complete the following steps:

  1. Add the library name to the shared_preload_libraries variable in the postgresql.conf file: 

    shared_preload_libraries = 'sr_plan'

    Note that the library names in the shared_preload_libraries variable must be added in the specific order, for information on compatibility of sr_plan with other extensions, see Section G.6.5.

  2. Reload the database server for the changes to take effect.

    To verify that the sr_plan library was installed correctly, you can run the following command: 

    SHOW shared_preload_libraries;

  3. Create the sr_plan extension using the following query: 

    CREATE EXTENSION sr_plan;

    It is essential that the library is preloaded during server startup because sr_plan has a shared memory cache that can be initialized only during startup. The sr_plan extension should be created in each database where query management is required.

  4. Enable the sr_plan extension, which is disabled by default, in one of the following ways:

    • To enable sr_plan for all backends, set sr_plan.enable = true in the postgresql.conf file.

    • To activate sr_plan in the current session, use the following command: 

      SET sr_plan.enable TO true;

  5. If you want to transfer sr_plan data from the primary to a standby using physical replication, set the sr_plan.wal_rw parameter to on on both servers. In this case, ensure that the same sr_plan versions are installed on both primary and standby, otherwise correct replication workflow is not guaranteed.

G.6.3. Usage

sr_plan allows you to freeze plans for future usage. Freezing involves three stages:

  1. Registering the query for which you want to freeze the plan.

  2. Modifying the query execution plan.

  3. Freezing the query execution plan.

G.6.3.1. Registering a Query

There are two ways to register a query:

  • Using the sr_register_query() function: 

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

    Here query_string is your query with $n parameters (same as in PREPARE statement_name AS). You can describe each parameter type with the optional parameter_type argument of the function or choose not to define parameter types explicitly. In the latter case, Postgres Pro attempts to determine each parameter type from the context. This function returns the unique pair of sql_hash and const_hash. Now sr_plan will track executions of queries that fit the saved parameterized query template. 

    -- Create table 'a'
CREATE TABLE a AS (SELECT * FROM generate_series(1,30) AS x);
CREATE INDEX ON a(x);
ANALYZE;

-- Register the query
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)

  • Using the sr_plan.auto_tracking parameter: 

    -- Set sr_plan.auto_tracking to on
SET sr_plan.auto_tracking = on;

-- Execute EXPLAIN for a non-parameterized query

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.6.3.2. Modifying the Query Execution Plan

A query execution plan can be modified using optimizer variables, pg_hint_plan hints if the extension is enabled, or other extensions that allow changing the query plan, such as aqo. For information on compatibility of sr_plan with other extensions, see Section G.6.5.

G.6.3.3. Freezing the Query Execution Plan

To freeze a modified query plan, use the sr_plan_freeze function. The optional parameter plan_type can be set to either serialized or hintset. The default value is serialized. For detailed information on types of frozen plans, see Section G.6.4.

G.6.3.4. Usage Example

The below example illustrates the usage of sr_plan. 

CREATE EXTENSION sr_plan;
SET sr_plan.enable = ON;

-- Register the query
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)

-- Modify the query execution plan
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)

-- Freeze the query execution plan
SELECT sr_plan_freeze();
RESET enable_seqscan;

-- Now the frozen plan is used
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.6.4. Frozen Plan Types

There are two types of frozen plans: serialized plans and hint-set plans.

  • A serialized plan is a serialized representation of the plan. This plan is transformed into an executable plan upon the first match of the corresponding frozen query. The serialized plan remains valid as long as the query metadata (table structures, indexes, etc.) remain unchanged. For example, if a table present in the frozen plan is recreated, the frozen plan becomes invalid and is ignored. The serialized plan is only valid within the current database and cannot be copied to another, as it depends on OIDs. For this reason, using a serialized plan for temporary tables is impractical.

  • A hintset plan is a set of hints that are formed based on the execution plan at the time of freezing. The set of hints consists of optimizer environment variables differing from default values, join types, join orders, and data access methods. These hints correspond to those supported by the pg_hint_plan extension. To use hint-set plans, pg_hint_plan must be enabled. The set of hints is passed to the pg_hint_plan planner upon the first match of the corresponding frozen query, and pg_hint_plan generates the executable plan. If the pg_hint_plan extension is not active, the hints are ignored, and the plan generated by the Postgres Pro optimizer is executed. Hint-set plans do not depend on object identifiers and remain valid when tables are recreated, fields are added, etc.

G.6.5. Compatibility with Other Extensions

To ensure compatibility of sr_plan with other enabled extensions, specify the library names in the shared_preload_libraries variable in the postgresql.conf file in the specific order:

  • pg_hint_plan: sr_plan must be loaded after pg_hint_plan. 

    shared_preload_libraries = 'pg_hint_plan, sr_plan'

  • aqo: sr_plan must be loaded before aqo. 

    shared_preload_libraries = 'sr_plan, aqo'

  • pgpro_stats: sr_plan must be loaded after pgpro_stats. 

    shared_preload_libraries = 'pgpro_stats, sr_plan'

G.6.6. Frozen Query Identification

A frozen query in the current database is identified by a combination of sql_hash and const_hash.

sql_hash is a hash generated based on the parse tree, ignoring parameters and constants. Field and table aliases are not ignored. Therefore, the same query with different aliases will have different sql_hash values.

const_hash is a hash generated based on all constants involved in the query. Constants with the same value but different types, such as 1 and '1', will produce different hash values.

G.6.7. Automatic Type Casting

sr_plan automatically attempts to cast the types of constants involved in the query to match the parameter types of the frozen query. If type casting is not possible, the frozen plan is ignored. 

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

-- Type casting is possible
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)

-- Type casting is possible
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)

-- Type casting is impossible
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.6.8. Views

G.6.8.1. The sr_plan_storage View

The sr_plan_storage view provides detailed information about all frozen statements. The columns of the view are shown in Table G.93.

Table G.93. sr_plan_storage Columns

NameTypeDescription
dbidoidID of the database where the statement is executed
sql_hashbigintInternal query ID
const_hashbigintHash of non-parameterized constants
validbooleanFALSE if the plan was invalidated the last time it was used
query_stringtextQuery registered by the sr_register_query function
paramtypesregtype[]Array with parameter types used in the query
querytextInternal representation of the query
plantextInternal representation of the plan
hintstrtextSet of hints formed based on the frozen plan

G.6.8.2. The sr_plan_local_cache View

The sr_plan_local_cache view provides detailed information about registered and frozen statements in the local cache. The columns of the view are shown in Table G.94.

Table G.94. sr_plan_local_cache Columns

NameTypeDescription
sql_hashbigintInternal query ID
const_hashbigintHash of non-parameterized constants
fs_is_frozenbooleanTRUE if the statement is frozen
fs_is_validbooleanTRUE if the statement is valid
ps_is_validbooleanTRUE if the statement should be revalidated
query_stringtextQuery registered by the sr_register_query function
querytextInternal representation of the query
paramtypesregtype[]Array with parameter types used in the query
hintstrtextSet of hints formed based on the frozen plan

G.6.8.3. The sr_captured_queries View

The sr_captured_queries view provides detailed information about all queries captured in sessions. The columns of the view are shown in Table G.95.

Table G.95. sr_captured_queries Columns

NameTypeDescription
dbidoidID of the database where the statement is executed
sql_hashbigintInternal query ID
queryidbigintStandard query ID
sample_stringtextQuery executed in the automatic query capture mode
query_stringtextParameterized query
constantstextSet of constants in the query
prep_conststextSet of constants used to EXECUTE a prepared statement
hintstrtextSet of hints formed based on the plan
explain_plantextPlan shown by the EXPLAIN command

G.6.9. Functions

Only superuser can call the functions listed below.

sr_register_query(query_string text) returns record
 sr_register_query(query_string text, VARIADIC regtype[]) returns record

Saves the query described in the query_string in the local cache and returns the unique pair of sql_hash and const_hash.

sr_unregister_query() returns bool

Removes the query that was registered but not frozen from the local cache. Returns true if there are no errors.

sr_plan_freeze(plan_type text) returns bool

Freezes the last used plan for the statement. The allowed values of the plan_type optional argument are serialized and hintset. The serialized value means that the query plan based on the serialized representation is used. With hintset, sr_plan uses the query plan based on the set of hints, which is formed at the stage of registered query execution. If the plan_type argument is omitted, the serialized query plan is used by default. Returns true if there are no errors.

sr_plan_unfreeze(sql_hash bigint, const_hash bigint) returns bool

Removes the plan only from the storage and keeps the query registered in the local cache. Returns true if there are no errors.

sr_plan_remove(sql_hash bigint, const_hash bigint) returns bool

Removes the frozen statement with the specified sql_hash and const_hash. Operates as sr_plan_unfreeze and sr_unregister_query called sequentially. Returns true if there are no errors.

sr_plan_reset(dbid oid) returns bigint

Removes all records in the sr_plan storage for the specified database. Omit dbid to remove the data collected by sr_plan for the current database. Set dbid to NULL to reset data for all databases.

sr_reload_frozen_plancache() returns bool

Drops all frozen plans and reloads them from the storage. It also drops statements that have been registered but not frozen.

sr_plan_fs_counter() returns table

Returns the number of times each frozen statement was used and the ID of the database where the statement was registered and used.

sr_show_registered_query(sql_hash bigint, const_hash bigint) returns table

Returns the registered query with the specified sql_hash and const_hash even if it is not frozen, for debugging purposes only. This works if the query is registered in the current backend or frozen in the current database.

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

Sets the type of the query plan for the frozen statement. The allowed values of the plan_type argument are serialized and hintset. To be able to use the query plan of the hintset type, the pg_hint_plan module must be loaded. Returns true if the plan type has been changed successfully.

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

Allows to change the generated hint set with the set of custom hints. Custom hint-set string should not be enclosed in the special form of comment, as in pg_hint_plan, i.e. it should not start with /*+ and end with */. Returns true if the hint-set plan was changed successfully.

sr_captured_clean() returns bigint

Removes all records from the sr_captured_queries view. The function returns the number of removed records.

G.6.10. Configuration Parameters

sr_plan.enable (boolean)

Enables sr_plan to use frozen plans. The default value is off. Only superusers can change this setting.

sr_plan.fs_ctr_max (integer)

Sets the maximum number of frozen statements returned by the sr_plan_fs_counter() function. The default value is 5000. This parameter can only be set at server start.

sr_plan.max_items (integer)

Sets the maximum number of entries sr_plan can operate with. The default value is 100. This parameter can only be set at server start.

sr_plan.auto_tracking (boolean)

Enables sr_plan to normalize and register queries executed using the EXPLAIN command automatically. The default value is off. Only superusers can change this setting.

sr_plan.max_local_cache_size (integer)

Sets the maximum size of local cache, in kB. The default value is zero, which means no limit. Only superusers can change this setting.

sr_plan.wal_rw (boolean)

Enables physical replication of sr_plan data. When set to off on the primary, no data is transferred from it to a standby. When set to off on a standby, any data transferred from the primary is ignored. The default value is off. This parameter can only be set at server start.

sr_plan.auto_capturing (boolean)

Enables the automatic query capture in sr_plan. Setting this configuration parameter to on allows you to see the queries with constants in the text form as well as parameterized queries in the sr_captured_queries view. Information about executed queries is stored until the server restart. The default value is off. Only superusers can change this setting.

sr_plan.max_captured_items (integer)

Sets the maximum number of queries sr_plan can capture. The default value is 1000. This parameter can only be set at server start.

sr_plan.sandbox (boolean)

Enables reserving a separate area in shared memory to be used by a primary or standby node, which allows testing and analyzing queries with the existing data set without affecting the node operation. If set to on on the standby, sr_plan freezes plans only on this node and stores them in the “sandbox”, an alternative plan storage. If enabled on the primary, the extension uses the separate shared memory area that is not replicated to the standby. Changing the parameter value resets the sr_plan cache. The default value is off. Only superusers can change this setting.

Prev Up Next
G.5. pgpro_stats Home G.7. pgpro_validate — check data integrity of a Postgres Pro instance
FAQ