SQL Plan Management

Precautions

Only AnalyticDB for PostgreSQL instances that use the elastic storage mode and run minor version V6.3.9.0 or later support SQL Plan Management.

Feature description

The optimizer of AnalyticDB for PostgreSQL evaluates the cost of each feasible execution operator of a query based on statistics instead of fixed rules, and selects the combination with the lowest cost. Although the optimizer selects the optimal execution plan in most cases, in complex business scenarios, rapid changes in data volumes and data distribution can cause significant changes in execution plans. In addition, optimizer version upgrades and feature enhancements can also change execution plans and occasionally generate unexpected plans. In analytics scenarios, complex report queries are periodically executed. The optimizer needs to search for a relatively optimal plan in a large plan search space, which takes a long time.

SQL Plan Management can store specified queries and query plans. When an equivalent query is executed, the stored query plan is directly reused. SQL Plan Management provides the following benefits:

• Reduces the time that is required to generate query plans repeatedly.

• Stabilizes query plans and prevents query plans from changing significantly when data volumes are large and data changes frequently.

• Supports parameterizing constants in queries. One query plan can be applied to all equivalent queries with different constants.

Manage the extension

Install the extension

To use SQL Plan Management, you must first install the sr_plan extension. Run the following statement:

CREATE EXTENSION sr_plan;

Temporarily disable the feature

To temporarily disable SQL Plan Management in the current session, run the following statement:

SET sr_plan.enabled to off;

Uninstall the extension

If you no longer need SQL Plan Management, run the following statement to uninstall the extension:

DROP EXTENSION sr_plan;

The sr_plans table

After you install the sr_plan extension, the system creates an sr_plans table to store plans. Each plan corresponds to a query and a query plan. The following table describes the table structure:

GUC parameters

AnalyticDB for PostgreSQL provides the following GUC parameters to control SQL Plan Management:

Utility functions

Register a plan

bool sr_plans_register(<query>, <const_list>, <hint_str>)

The following table describes the parameters:

Examples

• Register a plan in _p mode. Example:

  SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);', NULL, '/*+ Set(optimizer_enable_hashjoin off) */');

• Register a plan in prepare mode. Example:

  SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;', '11', '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */');

View a plan

• View the text plan

  show_plan(<query_hash>,<plan_hash>,<format>)

  Parameter	Type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query, which corresponds to the query_hash value in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized query plan, which corresponds to the plan_hash value in the sr_plans table. Default value: NULL. If the value is NULL, the first record in the enabled state is returned.
  format	text	The display format. Default value: 'text'. Valid values: 'text' 'xml' 'json' 'yaml'

  Examples

  View a specified text plan. The returned result is displayed in the 'text' format. Example:

  SELECT show_plan(-7846983602634689470, 1283098916874729409, 'text');

• View the complete plan structure

  show_plan_node(<query_hash>,<plan_hash>,<pretty>)

  Parameter	Type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query, which corresponds to the query_hash value in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized query plan, which corresponds to the plan_hash value in the sr_plans table. Default value: NULL. If the value is NULL, the first record in the enabled state is returned.
  pretty	bool	Specifies whether to display the result with line breaks and indentation. Valid values: true (default): displays the result with line breaks and indentation. false: does not display the result with line breaks and indentation.

  Examples

  View the complete plan structure. The returned result automatically includes line breaks and indentation. Example:

  SELECT show_plan_node(-7846983602634689470, 1283098916874729409, on);

• View the parameterized list

  show_const_list(<query_hash>,<plan_hash>,<is_list>,<pretty>)

  Parameter	Type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query, which corresponds to the query_hash value in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized query plan, which corresponds to the plan_hash value in the sr_plans table. Default value: NULL. If the value is NULL, the first record in the enabled state is returned.
  is_list	bool	Specifies whether to display the complete operator structure. Valid values: true (default): displays the complete operator list structure. false: displays a concise string list.
  pretty	bool	Specifies whether to display the result with line breaks and indentation. Valid values: true (default): displays the result with line breaks and indentation. false: does not display the result with line breaks and indentation.

  Examples

  View the parameterized list. The returned result displays the complete operator list structure without line breaks and indentation. Example:

  SELECT show_const_list(-7846983602634689470, 1283098916874729409, true, false);

Enable a plan

enable_sr_plans(<query_hash>,<plan_hash>)

Examples

Enable a specified plan. Example:

SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

Disable a plan

disable_sr_plans(<query_hash>,<plan_hash>)

Examples

Disable a specified plan. Example:

SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

Delete a plan

delete_sr_plans(<query_hash>,<plan_hash>)

Examples

Delete a specified plan. Example:

SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

Update a plan

update_sr_plans(<query_hash>,<plan_node_string>,<plan_hash>)

Examples

1. Prepare the test tables test_table and test_table2, and insert test data.

   DROP TABLE IF EXISTS test_table;
   DROP TABLE IF EXISTS test_table2;
   CREATE TABLE test_table(test_attr1 int, test_attr2 int);
   CREATE TABLE test_table2(test_attr1 int, test_attr2 int);
   INSERT INTO test_table SELECT i, i + 1 FROM generate_series(1, 20000) i;
   INSERT INTO test_table2 SELECT i, i + 1 FROM generate_series(1, 20000) i;
   CREATE INDEX test_table_index1 ON test_table (test_attr1);
   CREATE INDEX test_table_index2 ON test_table (test_attr2);
   ANALYZE test_table;
   ANALYZE test_table2;

2. Enable sr_plan log display to view prompts during registration and usage.

   We recommend that you enable log display during registration to check whether the stored plan can be used.

   SET sr_plan.log_usage = NOTICE;

3. View the original query plan.

   Execute the query plan:

   EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 11;

   The following information is returned:

                                                 QUERY PLAN
   ------------------------------------------------------------------------------------------------------
    Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..438.43 rows=1 width=16)
      ->  Hash Join  (cost=0.00..438.43 rows=1 width=16)
            Hash Cond: (test_table2.test_attr1 = test_table.test_attr1)
            ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
            ->  Hash  (cost=6.00..6.00 rows=1 width=8)
                  ->  Index Scan using test_table_index2 on test_table  (cost=0.00..6.00 rows=1 width=8)
                        Index Cond: (test_attr2 = 11)
    Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
   (8 rows)

4. Register query plans.

   • Use the _p mode with a hint:

     SELECT  sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);', NULL, '/*+ Set(optimizer_enable_hashjoin off) */');

     The following information is returned:

     NOTICE:  sr_plan: saved plan for /*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);
     CONTEXT:  SQL statement "/*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);"
     PL/pgSQL function sr_plans_register(text,text,text) line 17 at EXECUTE statement
     corresponding column is : sr_plans_register
      sr_plans_register
     -------------------
      t
     (1 row)

   • Use the prepare mode with a hint. The ORCA optimizer cannot be used in this mode:

     SELECT sr_plans_register('SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;', '11', '/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */');

     The following information is returned:

     NOTICE:  sr_plan: saved plan for select * from test_table, test_table2 where test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;
     CONTEXT:  SQL statement "/*+ MergeJoin(test_table test_table2) Leading((test_table test_table2)) */execute _sr_plans_register(11)"
     PL/pgSQL function sr_plans_register(text,text,text) line 27 at EXECUTE statement
     corresponding column is : sr_plans_register
      sr_plans_register
     -------------------
      t
     (1 row)

5. Query the plans of all records.

   SELECT * FROM sr_plans;

   To better display the results, you can run the \x command in psql to display the results in expanded mode. The following information is returned:

   -[ RECORD 1 ]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   query_hash    | -7846983602634689470
   query_id      | 0
   plan_hash     | 1283098916874729409
   enable        | f
   query         | /*+ Set(optimizer_enable_hashjoin off) */SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = _p(11);
   plan          | \x28b52ffd603f07150d00520b2a31504f0200745b6bf119296929280e2ae3d08e676124f9ca5fc36c629033c7b9a7a9bf766e213a3e4266ce009604c2ffbef32823871cd54a100b835171815211be6dfaa6d4ad563800a7f48bdc2a744c5dc8a4feeba7284366093db00c71a02190f870675a5b137a269a8d96868e83c5f4b733c6b21ecfc0752250e0e15cab5a4b2b33395506c20b063530f08f32c618ff808f4a133af1bbcca857f2dee7c220a5c311d1ecef4d928080a8b0d64000082000f0829199e1361200c234ca2508e40831901063483011c944125018292ceda0be8ff48b9cc45e2e1df50755b915267643696e998066ca9cdbdaae59e2e74e6a40f2efd406378e2636c854b8508978e1ecf58b356ea8bdab1b208ed06a4c2dea383dc365f24e0ed8e4a78ac542b50cf462aecf05650c56bab6f4266ab87c29354a59b2509a3fed448f897e4ccdf0623e9f293f6dbff7773e405f0884b67d8bc8c18f245c4b8a82297074668c19e8b0158d9ecdd3986fa2adddc4ed0cbaad8a1f54de36785a99cc39475c06e36f415872b3801e3a45a10aa91eded2dcddd3cf88abbd9824b0259f271a8692919907
   const_list    | \x28b52ffd2049ad0100540290020100000035010f80000017003001ffffffff040000000100a30000000ba0000000adde05004f9107417606fc3904325002
   reloids       | {40963,40960,40960,40963}
   index_reloids | {40969}
   -[ RECORD 2 ]-+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   query_hash    | -7846983602634689470
   query_id      | 0
   plan_hash     | 8380868479165711144
   enable        | f
   query         | select * from test_table, test_table2 where test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = $1;
   plan          | \x28b52ffd604a09c51000024f383730918200d5a047fa8799c1e341cc05c54480f746007c5f533b0d22256706d75b93bfed805f83bc0a6319ff43a73f4fbf55a3ed96bef73d35272fde92a40102d5171724a5427da08a5ebe361594ce16b5abf26cb13b631f23b0f98a19b4546c293fc0ac6fddf58b68f773dce8e8f337fc13aa2072a113828e4bc8a9041323b3da5577c38012ce0451cc700c172513f439a0146cd8cc7a5b69fd10a1473da9ad4c4a59c752bdd7344d76524ad2ee8add0a043f0fc448813f171ac771fc0f76663a52895fdbf033452e93800649c68479dc2c3c98bef9caa189784930d1c3755902809fa8d1d522400109822001f00281111a2b1200932cc9640c42c000048c314a0015000413418080090c680cc6838e2fd492d1b489b1738684cc0313c3b91659002bb5237c70b22b863bcc54a973f15fe134370df65c634396bf878f89b3658422ac27a9bb02c7d8b83cd81d1f074acaf5a107f14aa5a1d6c9d5c03b4aa7b08ad04359d3fe16bd509d41c12e0c028c55c9a0390794423566ac01d18c7b021016bf8ba100e83144bf84b0e4ad418a313c57df176b143aab601c43cd46c9516f08c7ea00e3d040d35e7d8b50d52462dfd3b03662d2674a78c9e9cc825594d3fbed29f0acdd1ce9caabbeae8a34c54a23879edea532081ef80aac28e26c1e26e6d99915549bcbb8a7d82cf8c2fef988a1c623472cff31c25dc69a69a130bdac655748e0a6ac3abe97d95b431fe104caffe6c083f007
   const_list    | \x28b52ffd2020010100900201000000310100000100000017000000ffffffff0000000095000000adde
   reloids       | {40960,40963}
   index_reloids | {40970}

6. View the plans.

   • View the first plan by using the query_hash and plan_hash fields:

     SELECT show_plan(-7846983602634689470, 1283098916874729409);

     The following information is returned:

                                                         show_plan
     ------------------------------------------------------------------------------------------------------------------
      ("Gather Motion 3:1  (slice1; segments: 3)")
      ("  Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("  ->  Nested Loop")
      ("        Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("        Join Filter: true")
      ("        ->  Seq Scan on public.test_table2")
      ("              Output: test_table2.test_attr1, test_table2.test_attr2")
      ("        ->  Index Scan using test_table_index1 on public.test_table")
      ("              Output: test_table.test_attr1, test_table.test_attr2")
      ("              Index Cond: (test_table.test_attr1 = test_table2.test_attr1)")
      ("              Filter: (test_table.test_attr2 = _p(11))")
      ("Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0")
      ("Settings: rds_plan_cache_mode=auto")
     (13 rows)

   • View the parameter list of the first plan by using the query_hash and plan_hash fields:

     SELECT show_const_list(-7846983602634689470, 1283098916874729409, false);

     The following information is returned:

           show_const_list
     ----------------------------
      _p(4 [ 11 0 0 0 0 0 0 0 ])
     (1 row)

   • View the second plan by using the query_hash and plan_hash fields:

     SELECT show_plan(-7846983602634689470, 8380868479165711144);

     The following information is returned:

                                                         show_plan
     ------------------------------------------------------------------------------------------------------------------
      ("Gather Motion 3:1  (slice1; segments: 3)")
      ("  Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("  ->  Merge Join")
      ("        Output: test_table.test_attr1, test_table.test_attr2, test_table2.test_attr1, test_table2.test_attr2")
      ("        Merge Cond: (test_table.test_attr1 = test_table2.test_attr1)")
      ("        ->  Sort")
      ("              Output: test_table.test_attr1, test_table.test_attr2")
      ("              Sort Key: test_table.test_attr1")
      ("              ->  Index Scan using test_table_index2 on public.test_table")
      ("                    Output: test_table.test_attr1, test_table.test_attr2")
      ("                    Index Cond: (test_table.test_attr2 = $1)")
      ("        ->  Sort")
      ("              Output: test_table2.test_attr1, test_table2.test_attr2")
      ("              Sort Key: test_table2.test_attr1")
      ("              ->  Seq Scan on public.test_table2")
      ("                    Output: test_table2.test_attr1, test_table2.test_attr2")
      ("Optimizer: Postgres query optimizer")
      ("Settings: rds_plan_cache_mode=auto")
     (18 rows)

   • View the parameter list of the second plan by using the query_hash and plan_hash fields:

     SELECT show_const_list(-7846983602634689470, 8380868479165711144, false);

     The following information is returned:

      show_const_list
     -----------------
      $1
     (1 row)

7. Use SQL Plan Management.

   • Verify the first plan

     1. Enable the first plan:

        SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

     2. Execute the query plan after the first plan is enabled. The parameterized constant can be changed:

        EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 12;

        The following information is returned:

        NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 12;
                                                     QUERY PLAN
        -----------------------------------------------------------------------------------------------------
         Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..120434.24 rows=8000 width=16)
           ->  Nested Loop  (cost=0.00..120433.77 rows=2667 width=16)
                 Join Filter: true
                 ->  Seq Scan on test_table2  (cost=0.00..431.14 rows=6667 width=8)
                 ->  Index Scan using test_table_index1 on test_table  (cost=0.00..120002.35 rows=1 width=8)
                       Index Cond: (test_attr1 = test_table2.test_attr1)
                       Filter: (test_attr2 = 12)
         Optimizer: Pivotal Optimizer (GPORCA) version 3.86.0
        (8 rows)

     3. Disable the first plan:

        SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

   • Verify the second plan

     1. Enable the second plan:

        SELECT  enable_sr_plans(-7846983602634689470, 8380868479165711144);

     2. Execute the query plan after the second plan is enabled. The parameterized constant can be changed:

        EXPLAIN SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 13;

        The following information is returned:

        NOTICE:  sr_plan: cached plan was used for query: explain SELECT * FROM test_table, test_table2 WHERE test_table.test_attr1 = test_table2.test_attr1 and test_table.test_attr2 = 13;
                                                      QUERY PLAN
        ------------------------------------------------------------------------------------------------------
         Gather Motion 3:1  (slice1; segments: 3)  (cost=1660.96..1760.98 rows=15 width=16)
           ->  Merge Join  (cost=1660.96..1760.98 rows=5 width=16)
                 Merge Cond: (test_table.test_attr1 = test_table2.test_attr1)
                 ->  Sort  (cost=8.19..8.20 rows=1 width=8)
                       Sort Key: test_table.test_attr1
                       ->  Index Scan using test_table_index2 on test_table  (cost=0.16..8.18 rows=1 width=8)
                             Index Cond: (test_attr2 = 13)
                 ->  Sort  (cost=1652.77..1702.77 rows=6667 width=8)
                       Sort Key: test_table2.test_attr1
                       ->  Seq Scan on test_table2  (cost=0.00..224.00 rows=6667 width=8)
         Optimizer: Postgres query optimizer
        (11 rows)

     3. Disable the second plan:

        SELECT disable_sr_plans(-7846983602634689470, 8380868479165711144);

8. Delete the plans.

   • Delete the first plan:

     SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

   • Delete the second plan:

     SELECT delete_sr_plans(-7846983602634689470, 8380868479165711144);