PolarDB O Edition provides the Outline feature to capture, view, and delete fixed 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 execution plans. The Outline feature does not support evolved execution plans. The Outline feature of PolarDB can be used to mark execution plans of only PREPARE statements as fixed execution plans.

The Outline feature of PolarDB is implemented by using the polar_outline plug-in.To enable the Outline feature, you must Submit a ticket to apply to use the feature.

Create the polar_outline plug-in

To create the polar_outline plug-in, 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 specify a 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 to 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;
Note
  • The test data is applicable only to the example in this section. Adjust the statements based on your business requirements.
  • You can use hints to control the execution plans that you want the Outline feature to capture. This way, execution plans of the statements that do not use hints are marked as fixed execution plans by the Outline feature.

Capture execution plans and mark them as fixed plans

PolarDB O Edition provides the following methods to capture execution plans and mark the execution plans as fixed execution plans:

  • Use 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:
    Notice If the Outline feature is used to capture execution plans, a plan cache is generated.
    1. To use the Outline feature to capture execution plans, execute the following statement:
      SET polar_outline.capture_plan TO ON;
    2. To capture an execution plan, execute the following statement:
      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;