postgrespro / sr_plan Goto Github PK

View Code? Open in Web Editor NEW

61.0 33.0 10.0 155 KB

Save and restore query plans in PostgreSQL

License: Other

Makefile 1.48% C 72.91% Shell 2.37% Shell 3.33% PLpgSQL 9.32% Python 10.60%

planning postgres postgresql outline extension

sr_plan's Introduction

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 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:

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.

sr_plan's People

Contributors

Stargazers

Watchers

Forkers

redzgn gridl funbringer pizzal erin-h rekgrpth zhihuifan sshyran andyqinshy guanqingtao

sr_plan's Issues

cached plan doesn't work for explain commands?

postgres=# select show_plan(1437592932);
                 show_plan
-------------------------------------------
 (Gather)
 ("  Output: i, im5, im100, im1000")
 ("  Workers Planned: 2")
 ("  ->  Parallel Seq Scan on public.j1")
 ("        Output: i, im5, im100, im1000")
 ("        Filter: (j1.i = 1)")
(6 rows)

postgres=# select query from sr_plans where query_hash = 1437592932;
             query
-------------------------------
 select * from j1 where i = 1;
(1 row)


postgres=# explain select * from j1 where i = 1;  -- I created the index after we created the cached plan
                           QUERY PLAN
----------------------------------------------------------------
 Index Scan using j1_i on j1  (cost=0.43..8.45 rows=1 width=16)
   Index Cond: (i = 1)
(2 rows)


postgres=# explain analyze select * from j1 where i = 1;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan using j1_i on j1  (cost=0.43..8.45 rows=1 width=16) (actual time=0.050..0.051 rows=1 loops=1)
   Index Cond: (i = 1)
 Planning Time: 0.122 ms
 Execution Time: 0.092 ms
(4 rows)

postgres=# select * from j1 where i = 1;
 i | im5 | im100 | im1000
---+-----+-------+--------
 1 |   1 |     1 |      1
(1 row)

Time: 615.249 ms

Here we can see we create a sr_plan for select * from sr_plans where i = 1, which is a Parallel Seq Scan. and during the execution of the plan, it can choose the cached plan as expected.

But if people use explain / explain analyze to check the plan, it will get a wrong result. I think a better solution is to use the cached plan as well to avoid such confusion.

What do you think?

sr_plan broken on PG 12

It looks like sr_plan needs to be adjusted for the new table access methods work from Andres.:


make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2 -fPIC -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o sr_plan.o sr_plan.c -MMD -MP -MF .deps/sr_plan.Po
sr_plan.c: In function ‘lookup_plan_by_query_hash’:
sr_plan.c:397:2: warning: implicit declaration of function ‘index_getnext’ [-Wimplicit-function-declaration]
  while ((htup = index_getnext(query_index_scan, ForwardScanDirection)) != NULL)
  ^
sr_plan.c:397:15: warning: assignment makes pointer from integer without a cast [enabled by default]
  while ((htup = index_getnext(query_index_scan, ForwardScanDirection)) != NULL)
               ^
sr_plan.c: In function ‘sr_planner’:
sr_plan.c:551:3: error: too few arguments to function ‘index_fetch_heap’
   htup = index_fetch_heap(query_index_scan);
   ^
In file included from /usr/local/pgsql/include/server/access/amapi.h:15:0,
                 from /usr/local/pgsql/include/server/access/hash.h:20,
                 from sr_plan.c:9:
/usr/local/pgsql/include/server/access/genam.h:164:13: note: declared here
 extern bool index_fetch_heap(IndexScanDesc scan, struct TupleTableSlot *slot);
             ^
sr_plan.c: In function ‘sr_query_expr_walker’:
sr_plan.c:716:29: error: ‘List’ has no member named ‘head’
    param->node = fexpr->args->head->data.ptr_value;
                             ^
sr_plan.c:738:17: error: ‘List’ has no member named ‘head’
      fexpr->args->head->data.ptr_value = param->node;
                 ^
sr_plan.c: In function ‘make_tupledesc’:
sr_plan.c:910:2: error: too many arguments to function ‘CreateTemplateTupleDesc’
  tupdesc = CreateTemplateTupleDesc(1, false);
  ^
In file included from /usr/local/pgsql/include/server/executor/tuptable.h:19:0,
                 from /usr/local/pgsql/include/server/nodes/print.h:17,
                 from sr_plan.h:8,
                 from sr_plan.c:1:
/usr/local/pgsql/include/server/access/tupdesc.h:94:18: note: declared here
 extern TupleDesc CreateTemplateTupleDesc(int natts);
                  ^

Multi plan for the same query is allowed, but which one is used is undefined.

The current implementation allows us create multi plan for the same query, and allow us enable all of them. and letter which one is choose is not well defined in looup_plan_by_query_hash.

How about to add a new index

create unique index sr_plans_query_hash_idx on sr_plan(query_hash) where (enable=true);

Stored wrong plan in table?

Hi,
I'm trying to use this extension to log query plan ( the input para Query *parse). Please correct me if I understand it wrong.

At line 255 in sr_plan.c, it's actually storing the execution plan instead of query plan.

Shouldn't log parse instead of pl_stmt at line 256?

postgrespro / sr_plan Goto Github PK

sr_plan's Introduction

Save and restore query plans in PostgreSQL

Rationale

Build and install

Usage

EXPLAIN for saved plans

`pg_stat_statements` integration

sr_plan's People

Contributors

Stargazers

Watchers

Forkers

sr_plan's Issues

cached plan doesn't work for explain commands?

sr_plan broken on PG 12

Multi plan for the same query is allowed, but which one is used is undefined.

Stored wrong plan in table?

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent

postgrespro / sr_plan Goto Github PK

sr_plan's Introduction

Save and restore query plans in PostgreSQL

Rationale

Build and install

Usage

EXPLAIN for saved plans

pg_stat_statements integration

sr_plan's People

Contributors

Stargazers

Watchers

Forkers

sr_plan's Issues

Recommend Projects

Recommend Topics

Recommend Org

`pg_stat_statements` integration