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=1
make USE_PGXS=1 installand 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:
set sr_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_hash from sr_plans where query_hash=10;Disable saving the plan for the query:
set sr_plan.write_mode = false;Enable it:
update sr_plans set 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_hash from sr_plans where 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_hash from sr_plans where query_hash=1000+_p(11);
select query_hash from sr_plans where query_hash=1000+_p(-5);EXPLAIN for saved plans
It is possible to see saved plans by using show_plan function. It requires
knowing query hash which could be fetched from sr_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 using index parameter (ignores enable 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 are json, 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 contains query_id columns which could be used to make
joins with pg_stat_statements tables and views.
Note: in shared_preload_libraries list pg_stat_statements should be
specified after sr_plan.