PolarDB:Adjust execution plans

Overview

SQL plan management (SPM) is a preventive mechanism. SPM allows the optimizer to automatically manage execution plans to ensure that your database uses only known or verified execution plans.

SPM provides the Outline feature. The Outline feature supports only fixed execution plans. The Outline feature does not support evolved execution plans. The Outline feature of PolarDB can be used to mark the execution plans of only PREPARE statements as fixed execution plans.

The outline feature in PolarDB is implemented by using the polar_outline extension. To use this feature, go to the Quota Center. Click Apply in the Actions column corresponding to polardb_pg_polar_outline.

Create the polar_outline extension

To create the polar_outline extension, execute the following statement:

CREATE EXTENSION polar_outline;

Configure parameters

To use the Outline feature to mark execution plans as fixed execution plans, execute the following statement:

SET polar_outline.use_stored_plan TO ON;

The functions and relations defined by the Outline feature are stored in the polar_outline schema. You can execute the following statement to add the polar_outline schema to the search path:

SET search_path To "$user",public,polar_outline;

Prepare test data

Execute the following statements to create a table named t and insert data into the table:

CREATE TABLE t(a INT,b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000)i;

Update the data in the table to ensure that the optimizer uses accurate statistics.

ANALYZE t;

Execute a PREPARE statement. The following code provides an example:

PREPARE test AS SELECT * FROM t WHERE a=$1;

Capture execution plans and mark them as fixed plans

PolarDB for Oracle provides the following methods to capture execution plans and mark the execution plans as fixed execution plans:

• Call the polar_outline_create function to capture execution plans. We recommend that you use this method. For example, execute the following statement:

  SELECT polar_outline_create('EXECUTE test(100)');

  The following information is returned:

   polar_outline_create 
  ----------------------
   t
  (1 row)

• Use the Outline feature to capture execution plans. This method can be used to capture multiple execution plans at the same time. To use the Outline feature to capture execution plans, perform the following steps:
  Important If the Outline feature is used to capture execution plans, a plan cache is generated.
  1. Execute the following statement to allow the Outline feature to capture execution plans:

     SET polar_outline.capture_plan TO ON;

  2. Execute the following statement to capture an execution plan:

     EXECUTE test(100);

     The following information is returned:

       a  |  b
     -----+-----
      100 | 100
     (1 row)

  3. If you do not want to use the Outline feature to capture execution plans, execute the following statement:

     SET polar_outline.capture_plan TO OFF;

Parameters

set polar_outline.log_usage To none;
set polar_outline.log_usage To debug;
set polar_outline.log_usage To debug1;
set polar_outline.log_usage To debug2;
set polar_outline.log_usage To debug3;
set polar_outline.log_usage To debug4;
set polar_outline.log_usage To debug5;
set polar_outline.log_usage To log;
set polar_outline.log_usage To info;
set polar_outline.log_usage To notice;
set polar_outline.log_usage To warning;

Uses the Outline feature to rewrite queries

SQL1: 
select t.a, t2.avg_b
from t join (select avg(b) as avg_b, a 
             from t2
             group by a) t2
on t2.a = t.a and t.c < $1 
order by t.a;

SQL2:
select t.a, t2.avg_b
from t join lateral (select avg(b) as avg_b
                     from t2
                     where t2.a = t.a) as t2
on t.c < $1
order by t.a;

You can perform the following steps to use the Outline feature to replace the execution plan of SQL1 with the execution plan of SQL2:

1. Execute the following statements to prepare data:

   CREATE TABLE t(a int, b int, c int);
   insert into t select i % 100000, i, i from generate_series(1, 1000000) i;
   create table t2 as select * from t;
   create index on t(c);
   create index on t2(a);
   ANALYZE t,t2;

2. Execute the following statements to capture the execution plans of the source query SQL1 and the rewritten query SQL2:

   prepare s1 as select t.a, t2.avg_b from t join (select avg(b) as avg_b, a from t2 group by a) t2
   on t2.a = t.a and t.c < $1 order by t.a;
   prepare s2 as select t.a, t2.avg_b from t join lateral (select avg(b) as avg_b from t2 where t2.a = t.a) as t2
   on t.c < $1 order by t.a;
   SELECT polar_outline.polar_outline_create('EXECUTE s1(5)');
    polar_outline_create 
   ----------------------
    t
   (1 row)
   
   SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');
    polar_outline_create 
   ----------------------
    t
   (1 row)

3. Execute the following statements to use polar_outline_switch to exchange the execution plans of the two queries:
   Note The input parameters are the IDs of the two execution plans in the outline. The execution plan of SQL1 is replaced with the execution plan of SQL2.

   SELECT polar_outline.polar_outline_switch(1,2);
    polar_outline_switch 
   ----------------------
    t
   (1 row)

4. Execute the following statement to delete the execution plan of SQL2 from the outline:
   Note After SQL1 is rewritten, the execution plan of SQL2 is unnecessary and needs to be cleared.

   SELECT polar_outline.polar_outline_delete(2);

5. The following code shows the execution result of SQL1 before SQL1 is rewritten and the execution result of SQL1 after SQL1 is rewritten:

   Before SQL1 is rewritten, s1 indicates the original execution plan of SQL1.
   EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                   QUERY PLAN                 
   -------------------------------------------
    Sort
      Sort Key: t.a
      ->  Hash Join
            Hash Cond: (t.a = t2.a)
            ->  Index Scan using t_c_idx on t
                  Index Cond: (c < $1)
            ->  Hash
                  ->  HashAggregate
                        Group Key: t2.a
                        ->  Seq Scan on t2
   (10 rows)
   
   SELECT polar_outline_switch(1,2);
    polar_outline_switch 
   ----------------------
    t
   (1 row)
   
   After SQL1 is rewritten, s1 indicates the current execution plan of SQL1. The current execution plan is sourced from SQL2.
   EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                         QUERY PLAN                       
   -------------------------------------------------------
    Sort
      Sort Key: t.a
      ->  Nested Loop
            ->  Index Scan using t_c_idx on t
                  Index Cond: (c < $1)
            ->  Aggregate
                  ->  Bitmap Heap Scan on t2
                        Recheck Cond: (a = t.a)
                        ->  Bitmap Index Scan on t2_a_idx
                              Index Cond: (a = t.a)
   (10 rows)