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

Save and restore query plans in PostgreSQL

License

NotificationsYou must be signed in to change notification settings

postgrespro/sr_plan

Repository files navigation

Build StatusGitHub license

Save and restore query plans in PostgreSQL

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 and install

make USE_PGXS=1make USE_PGXS=1 install

and modify your postgres config:

shared_preload_libraries = 'sr_plan'

Usage

Install the extension in your database:

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.

Make an example query:

select query_hashfrom sr_planswhere query_hash=10;

Disable saving the plan for the query:

setsr_plan.write_mode= false;

Enable it:

update sr_plansset enable=true;

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);

EXPLAIN for saved plans

It is possible to see saved plans by usingshow_plan function. It requiresknowing query hash which could be fetched fromsr_plans table.

Examples:

Show enabled plan for query hash:

SELECT show_plan(1);                  show_plan---------------------------------------------- ("Seq Scan on public.explain_test") ("  Output: test_attr1, test_attr2") ("  Filter: (explain_test.test_attr1 = 10)")(3 rows)

Get second saved plan by usingindex parameter (ignoresenable attribute):

SELECT show_plan(1, index :=2);                  show_plan---------------------------------------------- ("Seq Scan on public.explain_test") ("  Output: test_attr1, test_attr2") ("  Filter: (explain_test.test_attr1 = 10)")(3 rows)

Use another output format (supported formats arejson,text,xml,yaml):

SELECT show_plan(1, format :='json');                      show_plan------------------------------------------------------ ("[                                                 +   {                                                 +""Plan"": {                                     +""Node Type"":""Seq Scan"",                  +""Parallel Aware"": false,                    +""Relation Name"":""explain_test"",          +""Schema"":""public"",                       +""Alias"":""explain_test"",                  +""Output"": [""test_attr1"",""test_attr2""], +""Filter"":""(explain_test.test_attr1 = 10)""+     }                                               +   }                                                 + ]")(1 row)

pg_stat_statements integration

sr_plans table containsquery_id columns which could be used to makejoins withpg_stat_statements tables and views.

Note: inshared_preload_libraries listpg_stat_statements should bespecified aftersr_plan.

About

Save and restore query plans in PostgreSQL

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Contributors4

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