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

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

Обычно системные администраторы играют с запросами интерактивно и сохраняют выбранные планы для использования с запросами, время обработки которых должно быть предсказуемым.

Затем сохранённые планы должны задействоваться приложением, выполняющим эти запросы.

Выполните в вашей базе:

CREATE EXTENSION sr_plan;

И отредактируйте postgresql.conf:

shared_preload_libraries = 'sr_plan'

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

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

set sr_plan.write_mode = true;

После этого планы всех последующих запросов будут сохраняться в таблице sr_plans, пока этой переменной не будет присвоено значение false. Помните, что при этом сохраняются планы всех запросов, включая повторные. Выполните нужный вам запрос:

select query_hash from sr_plans where query_hash=10;

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

set sr_plan.write_mode = false;

Убедитесь в том, что план запроса сохранён:

select query_hash, enable, valid, query, explain_jsonb_plan(plan) from sr_plans;

 query_hash | enable | valid |                        query                         |                 explain_jsonb_plan                 
------------+--------+-------+------------------------------------------------------+----------------------------------------------------
 1783086253 | f      | t     | select query_hash from sr_plans where query_hash=10; | Bitmap Heap Scan on sr_plans                      +
            |        |       |                                                      |   Recheck Cond: (query_hash = 10)                 +
            |        |       |                                                      |   ->  Bitmap Index Scan on sr_plans_query_hash_idx+
            |        |       |                                                      |         Index Cond: (query_hash = 10)             +
            |        |       |                                                      | 

Обратите внимание на вызов функции explain_jsonb_plan, которая позволяет визуализировать план выполнения подобно тому, как это делает команда EXPLAIN.

Планы сохраняются в базе данных в виде jsonb. По умолчанию все записанные планы отключены, их использование необходимо разрешить вручную:

Чтобы разрешить использование сохранённого плана, выполните:

update sr_plans set enable=true where query_hash=1783086253;

(Здесь 1783086253 указано только для примера.) После этого при выполнении данного запроса будет использоваться план из таблицы sr_plans.

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

Мы можем пометить в режиме сохранения плана эти константы как параметры запроса, используя специальную функцию _p (anyelement). Например:

=>create table test_table (a numeric, b text);
CREATE TABLE
=>insert into test_table values (1,'1'),(2,'2'),(3,'3');
INSERT 0 3 
=> set sr_plan.write_mode = true;
SET
=> select a,b  from test_table where a = _p(1);
 a | b
---+---
 1 | 1
(1 row)

=> set sr_plan.write_mode = false;
SET

Теперь план запроса к нашей таблице сохранён с параметром. И если мы включим сохранённый план, он будет использоваться для запросов с любым значением a, если это значение будет завёрнуто в вызов функции _p().

=>update sr_plans set enable = true where query=
  'select a,b from test_table where a = _p(1)';
UPDATE 1
-- Эти запросы будут использовать сохранённый план

=>select a,b from test_table where a = _p(2);
 a | b
---+---
 2 | 2
(1 row)

=>select a,b from test_table where a = _p(3);
 a | b
---+---
 3 | 3
(1 row)

-- Этот запрос не будет использовать сохранённый план, так как
-- константа не завёрнута в _p()

=>select a,b from test_table where a = 1;
 a | b
---+---
 1 | 1
(1 row)