AnalyticDB for PostgreSQL:SQL plan management

Usage notes

• The SQL plan management feature can be used only for AnalyticDB for PostgreSQL instances in elastic storage mode that run V6.3.9.0 or later.

• To install or upgrade extensions on instances that run V6.3.8.9 or later, Submit a ticket.

For information about how to view and update the minor version, see View the minor engine version and Update the minor engine version.

Overview

AnalyticDB for PostgreSQL uses a cost-based optimizer that utilizes statistics instead of static rules. The optimizer estimates the cost of each possible execution plan for an SQL statement and chooses the minimum-cost plan for execution. Although the optimizer does its best to select the optimal execution plan, the execution plan may change significantly when the amount of data and data distribution characteristics sharply change in complex business scenarios. In addition, the execution plan may also change due to optimizer version updates and feature improvements. Therefore, unexpected execution plans may be generated occasionally. If complex report queries occur in analysis scenarios, it may take a long time for the optimizer to retrieve an optimal execution plan from a large number of execution plans.

The SQL plan management feature stores SQL patterns and their corresponding execution plans. If an SQL statement that uses a stored pattern is executed, this feature can directly call the corresponding execution plan. The SQL plan management feature provides the following benefits:

• Reduces the amount of time that is required to generate repeated execution plans.

• Keeps execution plans stable. This prevents excessive changes to execution plans in scenarios where large amounts of data are frequently changed.

• Supports parameterized constants. An execution plan can be used for a set of SQL statements that share the same pattern but use different constants.

Manage the sr_plan extension

Install the sr_plan extension

To use the SQL plan management feature, you must execute the following statement to install the sr_plan extension first:

CREATE EXTENSION sr_plan;

Temporarily disable the SQL plan management feature

To temporarily disable the SQL plan management feature in the current session, you can execute the following statement:

SET sr_plan.enabled to off;

Delete the sr_plan extension

If the SQL plan management feature is no longer needed, you can execute the following statement to delete the sr_plan extension:

DROP EXTENSION sr_plan;

sr_plans table

After the sr_plan extension is installed, a table named sr_plans is automatically generated to store SQL plans. Each SQL plan contains an SQL pattern and its corresponding execution plan in a row. The following table describes the schema of the sr_plans table.

GUC parameters

The following table describes the Grand Unified Configuration (GUC) parameters that are provided by AnalyticDB for PostgreSQL for managing the SQL plan management feature.

Function description

Register an SQL plan

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

The following table describes the parameters.

Examples:

• Register an SQL plan in _p 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 = _p(11);', NULL, '/*+ Set(optimizer_enable_hashjoin off) */');

• Register an SQL plan in prepared 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)) */');

Query an SQL plan

• Query an SQL plan in the text format

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

  Parameter	Data type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query statement. It corresponds to the value of query_hash in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized execution plan. It corresponds to the value of plan_hash in the sr_plans table. Default value: NULL. It indicates the first execution plan that is in the enabled state.
  format	text	The format in which the query result is displayed. Default value: 'text'. Valid values: 'text' 'xml' 'json' 'yaml'

  Example:

  Query an SQL plan and display the query result in the 'text' format.

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

• Query an entire plan structure

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

  Parameter	Data type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query statement. It corresponds to the value of query_hash in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized execution plan. It corresponds to the value of plan_hash in the sr_plans table. Default value: NULL. It indicates the first execution plan that is in the enabled state.
  pretty	bool	Specifies whether to use text wrapping and indents in the query result display. Valid values: true (default) false

  Example:

  Query an entire plan structure and use text wrapping and indents to display the query result.

  SELECT show_plan_node(-7846983602634689470, 1283098916874729409, on);

• Query the parameterized constant

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

  Parameter	Data type	Description
  query_hash	bigint	The 64-bit hash value of the parameterized query statement. It corresponds to the value of query_hash in the sr_plans table.
  plan_hash	bigint	The 64-bit hash value of the parameterized execution plan. It corresponds to the value of plan_hash in the sr_plans table. Default value: NULL. It indicates the first execution plan that is in the enabled state.
  is_list	bool	Specifies whether to display the entire operator list. Valid values: true (default) false
  pretty	bool	Specifies whether to use text wrapping and indents in the query result display. Valid values: true (default) false

  Example:

  Query the parameterized constant and display the query result by using the entire operator list without text wrapping or indents.

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

Enable an SQL plan

enable_sr_plans(<query_hash>,<plan_hash>)

Example:

Enable an SQL plan.

SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

Disable an SQL plan

disable_sr_plans(<query_hash>,<plan_hash>)

Example:

Disable an SQL plan.

SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

Delete an SQL plan

delete_sr_plans(<query_hash>,<plan_hash>)

Example:

Delete an SQL plan.

SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

Modify an SQL plan

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

Examples

1. Create tables named test_table and test_table2 and insert data into the two tables.

   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 log display for the sr_plan extension to view notices when you register and use an SQL plan.

   We recommend that you enable log display when you register an SQL plan. This helps you check whether stored SQL plans can be used.

   SET sr_plan.log_usage = NOTICE;

3. Query an execution plan.

   Execute the following statement to query an execution 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 an SQL plan.

   • Use the _p mode and 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 prepared mode and a hint. The ORCA optimizer cannot be used.

     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 all SQL plans that are stored in the sr_plans table.

   SELECT * FROM sr_plans;

   To display query results horizontally, you can run the \x command in psql. 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. Query SQL plans.

   • Use the query_hash and plan_hash parameters to query the first SQL plan.

     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)

   • Use the query_hash and plan_hash parameters to query the parameterized constant of the first SQL plan.

     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)

   • Use the query_hash and plan_hash parameters to query the second SQL plan.

     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)

   • Use the query_hash and plan_hash parameters to query the parameterized constant of the second SQL plan.

     SELECT show_const_list(-7846983602634689470, 8380868479165711144, false);

     The following information is returned:

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

7. Use the SQL plan management feature.

   • Verify the first SQL plan

     1. Enable the first SQL plan.

        SELECT enable_sr_plans(-7846983602634689470, 1283098916874729409);

     2. While the first SQL plan is enabled, query the execution plan with the parameterized constant 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 SQL plan.

        SELECT disable_sr_plans(-7846983602634689470, 1283098916874729409);

   • Verify the second SQL plan

     1. Enable the second SQL plan.

        SELECT  enable_sr_plans(-7846983602634689470, 8380868479165711144);

     2. While the second SQL plan is enabled, query the execution plan with the parameterized constant 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 SQL plan.

        SELECT disable_sr_plans(-7846983602634689470, 8380868479165711144);

8. Delete SQL plans.

   • Delete the first SQL plan.

     SELECT delete_sr_plans(-7846983602634689470, 1283098916874729409);

   • Delete the second SQL plan.

     SELECT delete_sr_plans(-7846983602634689470, 8380868479165711144);