You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
5
+
sr_plan looks like Oracle Outline system. It can be used to lock
6
+
the execution plan. It is necessary if you do not trust the planner
7
+
or able to form a better plan.
8
+
9
+
Typically, DBA would play with queries interactively, and save their
10
+
plans and then enable use of saved plans for the queries, where
11
+
predictable responce time is essential.
12
+
13
+
Then application which uses these queries would use saved plans.
6
14
7
15
## Build
8
16
9
-
Dependencies: >= Python 3.2, Mako, pycparser
17
+
This module needs to serialize and deserialize lot of structures, which
18
+
results in almost same code.
19
+
20
+
So, mako preprocessor is used to generate C files serialize.c and
21
+
deserialize.c.
22
+
23
+
If you want to modify these files, you'll needed to alter mako templates
24
+
and regenerate files, so you'll need to install maco, python >= 3.2 and
25
+
pycparser modules.
26
+
10
27
If you only have a Python you can use the virtual environment:
11
28
```bash
12
29
virtualenv env
13
30
source ./env/bin/activate
14
31
pip install -r ./requirements.txt
15
32
```
16
33
17
-
Then you need to generate C code and compiled it:
34
+
Then you need to generate C code and compile it:
35
+
18
36
```bash
19
37
make USE_PGXS=1 genparser
20
38
make USE_PGXS=1
21
39
make USE_PGXS=1 install
22
40
```
23
41
24
-
and modify your postgres config:
25
-
```
26
-
shared_preload_libraries = 'sr_plan.so'
27
-
```
42
+
If you want to only build this module as is, pregenerated files are
43
+
provided for you, so ``make genparser`` command should be omitted.
44
+
45
+
## Installation
28
46
29
-
## Usage
30
47
In your db:
31
48
```SQL
32
49
CREATE EXTENSION sr_plan;
33
50
```
51
+
and modify your postgresql.conf:
52
+
```
53
+
shared_preload_libraries = 'sr_plan.so'
54
+
```
55
+
It is essential that library is preloaded during server startup, because
56
+
use of saved plans is enabled on per-database basis and doesn't require
57
+
any per-connection actions.
58
+
59
+
## Usage
60
+
61
+
62
+
34
63
If you want to save the query plan is necessary to set the variable:
64
+
35
65
```SQL
36
66
setsr_plan.write_mode= true;
37
67
```
38
-
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.
68
+
69
+
Now plans for all subsequent queries will be stored in the table sr_plans,
70
+
until this variable is set to false. Don't forget that all queries will be
71
+
stored including duplicates.
39
72
Making an example query:
73
+
40
74
```SQL
41
75
select query_hash from sr_plans where query_hash=10;
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:
97
+
Note use of explain\_jsonb\_plan function, that allows you to visualize
98
+
execution plan in the similar way as EXPLAIN command does.
99
+
100
+
In the database plans are stored as jsonb. By default, all the newly
101
+
saved plans are disabled, you need enable it manually:
102
+
103
+
To enable use of the saved plan
104
+
62
105
```SQL
63
106
update sr_plans set enable=true where query_hash=1783086253;
64
107
```
108
+
65
109
(1783086253 for example only)
66
110
After that, the plan for the query will be taken from the sr_plans.
67
111
68
-
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.
69
-
For the parameters we use a special function _p (anyelement) example:
112
+
In addition sr plan allows you to save a parameterized query plan. In
113
+
this case, we have some constants in the query that, as we know, do
114
+
not affect plan.
115
+
116
+
During plan saving mode we can mark these constants as query parameters
117
+
using a special function _p (anyelement). For example:
118
+
119
+
70
120
```SQL
71
-
select query_hash from sr_plans where query_hash=1000+_p(10);
121
+
122
+
=>create table test_table (a numeric, b text);
123
+
CREATE TABLE
124
+
=>insert into test_table values (1,'1'),(2,'2'),(3,'3');
125
+
INSERT 03
126
+
=>setsr_plan.write_mode= true;
127
+
SET
128
+
=>select a,b from test_table where a = _p(1);
129
+
a | b
130
+
---+---
131
+
1 | 1
132
+
(1 row)
133
+
134
+
=>setsr_plan.write_mode= false;
135
+
SET
136
+
72
137
```
73
-
if we keep the plan for the query and enable it to be used also for the following queries:
138
+
139
+
140
+
Now plan for query from our table is saved with parameter. So,
141
+
if we enable saved plan in this table, this plan would be used for query
142
+
with any value for a, as long as this value is wrapped with _p()
143
+
function.
144
+
74
145
```SQL
75
-
select query_hash from sr_plans where query_hash=1000+_p(11);
76
-
select query_hash from sr_plans where query_hash=1000+_p(-5);
146
+
=>update sr_plans set enable = true where quesry=
147
+
'select a,b from test_table where a = _p(1)';
148
+
UPDATE1
149
+
-- These queries would use saved plan
150
+
151
+
=>select a,b from test_table where a = _p(2);
152
+
a | b
153
+
---+---
154
+
2 | 2
155
+
(1 row)
156
+
157
+
=>select a,b from test_table where a = _p(3);
158
+
a | b
159
+
---+---
160
+
3 | 3
161
+
(1 row)
162
+
163
+
-- This query wouldn't use saved plan, because constant is not wrapped
0 commit comments