sr_plan looks like Oracle Outline system. It can be used to lock the execution plan. It is necessary if you do not trust the planner or able to form a better plan.

sr_plan looks like Oracle Outline system. It can be used to lock

the execution plan. It is necessary if you do not trust the planner

or able to form a better plan.

Typically, DBA would play with queries interactively, and save their

plans and then enable use of saved plans for the queries, where

predictable responce time is essential.

Then application which uses these queries would use saved plans.

Dependencies: >= Python 3.2, Mako, pycparser

This module needs to serialize and deserialize lot of structures, which

results in almost same code.

So, mako preprocessor is used to generate C files serialize.c and

deserialize.c.

If you want to modify these files, you'll needed to alter mako templates

and regenerate files, so you'll need to install maco, python >= 3.2 and

pycparser modules.

If you only have a Python you can use the virtual environment:

virtualenv env

source ./env/bin/activate

pip install -r ./requirements.txt

Then you need to generate C code and compiled it:

Then you need to generate C code and compile it:

make USE_PGXS=1 genparser

make USE_PGXS=1

make USE_PGXS=1 install

In your db:

CREATE EXTENSION sr_plan;

and modify your postgresql.conf:

It is essential that library is preloaded during server startup, because

use of saved plans is enabled on per-database basis and doesn't require

any per-connection actions.

If you want to save the query plan is necessary to set the variable:

setsr_plan.write_mode= true;

Now plans for all subsequent queries will be stored in the table sr_plans. Don't forget that all queries will be stored including duplicates.

Now plans for all subsequent queries will be stored in the table sr_plans,

until this variable is set to false. Don't forget that all queries will be

stored including duplicates.

Making an example query:

select query_hashfrom sr_planswhere query_hash=10;

disable saving the query:

Disable saving the query:

setsr_plan.write_mode= false;

explain_jsonb_plan function allows you to display explain execute the plan of which lies in jsonb. By default, all the plans are off, you need enable it:

Note use of explain\_jsonb\_plan function, that allows you to visualize

execution plan in the similar way as EXPLAIN command does.

In the database plans are stored as jsonb. By default, all the newly

saved plans are disabled, you need enable it manually:

To enable use of the saved plan

update sr_plansset enable=truewhere query_hash=1783086253;

(1783086253 for example only)

After that, the plan for the query will be taken from the sr_plans.

In addition sr plan allows you to save a parameterized query plan. In this case, we have some constants in the query are not essential.

For the parameters we use a special function_p (anyelement) example:

In addition sr plan allows you to save a parameterized query plan. In

this case, we have some constants in the query that, as we know, do

not affect plan.

During plan saving mode we can mark these constants as query parameters

using a special function_p (anyelement). For example:

select query_hashfrom sr_planswhere query_hash=1000+_p(10);

=>create table test_table (anumeric, btext);

CREATE TABLE

=>insert into test_tablevalues (1,'1'),(2,'2'),(3,'3');

INSERT03

=>setsr_plan.write_mode= true;

=>select a,bfrom test_tablewhere a= _p(1);

a | b

1 |1

(1 row)

=>setsr_plan.write_mode= false;

if we keep the plan for the query and enable it to be used also for the following queries:

Now plan for query from our table is saved with parameter. So,

if we enable saved plan in this table, this plan would be used for query

with any value for a, as long as this value is wrapped with_p()

function.

select query_hashfrom sr_planswhere query_hash=1000+_p(11);

select query_hashfrom sr_planswhere query_hash=1000+_p(-5);

=>update sr_plansset enable= truewhere quesry=

'select a,b from test_table where a = _p(1)';

=>select a,bfrom test_tablewhere a= _p(2);

a | b

2 |2

(1 row)

=>select a,bfrom test_tablewhere a= _p(3);

a | b

3 |3

(1 row)

=>select a,bfrom test_tablewhere a=1;

a | b

1 |1

(1 row)