Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Postgres Professional fork of PostgreSQL

NotificationsYou must be signed in to change notification settings

postgrespro/postgrespro

Repository files navigation

Rationale

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.

Build

Dependencies: >= Python 3.2, Mako, pycparserIf you only have a Python you can use the virtual environment:

virtualenv envsource ./env/bin/activatepip install -r ./requirements.txt

Then you need to generate C code and compiled it:

make USE_PGXS=1 genparsermake USE_PGXS=1make USE_PGXS=1 install

and modify your postgres config:

shared_preload_libraries = 'sr_plan.so'

Usage

In your db:

CREATE EXTENSION sr_plan;

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.Making an example query:

select query_hashfrom sr_planswhere query_hash=10;

disable saving the query:

setsr_plan.write_mode= false;

Now verify that your query is saved:

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_hashfrom sr_planswhere query_hash=10; | Bitmap Heap Scanon sr_plans+            |        |       |                                                      |   Recheck Cond: (query_hash=10)+            |        |       |                                                      |->  Bitmap Index Scanon sr_plans_query_hash_idx+            |        |       |                                                      |         Index Cond: (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:

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:

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

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

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

About

Postgres Professional fork of PostgreSQL

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Contributors36

[8]ページ先頭
©2009-2025 Movatter.jp