PolarDB:Statement outline

Background information

When managing databases, the O&M team and database developers often need to troubleshoot slow queries. Various factors, such as uneven data distribution and inaccurate statistics, may cause slow SQL queries.

In order to generate optimal execution plans, it is a common and effective method to use hints to control the optimizer behavior. However, database administrators may face the following difficulties when adding hints to business SQL statements:

• Complexity of adding hints: The SQL statements of many applications are automatically generated by middleware. Manually adding hints is difficult.

• Complexity of the release process: Manually adding hints to an application and releasing a new application version each time the hints are modified is time-consuming and labor-intensive. This may also bring additional risks.

• Complexity of managing hints: As more hints are added to an application, the management cost increases. It is difficult for database administrators to know the number of hints in the entire system.

To address the preceding difficulties, PolarDB for PostgreSQL (Compatible with Oracle) provides the statement outline feature to automatically add hints to specific SQL statements.

Feature overview

The statement outline feature allows you to manually outline the execution plan of specific SQL statements by adding or modifying the hints. The statement outline feature provides the following benefits:

• No changes to applications: does not require application code modification or release. The configuration takes effect only at the database level.

• Quick response and adjustment: responds to changes and adjusts execution plans in a timely manner to avoid slow SQL queries and improve system stability and performance.

• Fine-grained control and management: provides corresponding outlines for different SQL statements. You can view the status of outlines in the entire system, such as the hit information and whether an outline is enabled.

Terms

• HINT: The annotations that are used to intervene in the optimizer to generate different execution plans.

• OUTLINE: The hints added to specific SQL statements.

• OUTLINE DDL: The operations on outlines such as creation, deletion, and status modification.

• SQL_ID: The SQL statement identifier, which is automatically generated by the database system and used to identify SQL statements. The statement outline feature uses SQL_ID to match SQL statements.

Prerequisites

• Your PolarDB for PostgreSQL cluster runs the following database engine version:

  PolarDB for PostgreSQL (Compatible with Oracle) 2.0 whose revision version is 2.0.14.28.0 or later

  Note

  You can execute the following statement to query the revision version of your PolarDB for PostgreSQL (Compatible with Oracle) cluster:

  SHOW polar_version; 

  For more information about how to upgrade the revision version of your cluster, see Upgrade the version.

• Create the pg_hint_plan extension whose version is 1.4.1 or later. For more information, see pg_hint_plan.

Usage notes

• SQL_ID matching: Some SQL statements are normalized by the database kernel. As a result, the following factors are ignored during the SQL_ID matching:

  • Spaces, line breaks, and comments.

  • Parameters in SQL statements, including constants and variables. For example, a = 1, a = $1, a = 2 are normalized.

  • The case of keywords. For example, SELECT a, select a, Select a are normalized.

  • For parameters with continuous values, the number of parameters is ignored. For example, a IN (1,2,3) and a IN (1,2) are normalized. However, a IN (1,2,3) and a IN (1) are not normalized because IN (1) does not contain continuous parameters.

• Multiple outlines can be created for specific SQL statements. Multiple hints can take effect on a SQL statement at the same time.

• Outlines have a higher priority than hints in SQL statements. After an outline is created for a SQL statement, the hints in the SQL statement become invalid. Only the hints in the outline take effect.

• The statement outline feature and the hint_table feature of pg_hint_plan are similar and cannot be enabled at the same time. After statement outline is enabled, hint_table becomes invalid by default.

Impact on performance

The statement outline feature provides a high concurrency cache module that minimizes the impact on performance. After the feature is enabled and outlines are added, the SysBench stress test shows only 1% to 2% decrease in TPS and QPS.

Enable the statement outline feature

1. Make sure that the pg_hint_plan extension (1.4.1 or later) is installed in the database.

   SELECT extname, extversion >= '1.4.1' AS outline_version_ok FROM pg_extension WHERE extname = 'pg_hint_plan';

   The following result shows that the extension is installed and meets the version requirement.

      extname    | outline_version_ok
   --------------+--------------------
    pg_hint_plan | t
   (1 row)

   If the following issues exist, different results may return. You can execute corresponding SQL statements to resolve the issues:

   Note

   Make sure that you use an account that has the permissions to perform the following operations.

   • The extension is not installed:

     CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

   • The extension version is earlier than 1.4.1:

     ALTER EXTENSION pg_hint_plan UPDATE;

2. Set pg_hint_plan.polar_enable_outline to on to enable the statement outline feature. This parameter does not require a cluster restart to take effect. For information about how to configure cluster parameters in the PolarDB console, see Configure cluster parameters.

Use the statement outline feature

Create an outline

PolarDB provides the hint_plan.create_outline function to create outlines. Specify the SQL statement for which to create the outline. The hints in the SQL statement are automatically extracted and added to the outline.

Example

CALL hint_plan.create_outline($$ SELECT /*+ Set(enable_bitmapscan off) */ * FROM t WHERE a = 1 $$);

View outlines

PolarDB for PostgreSQL (Compatible with Oracle) provides the hint_plan.outlines_status view to display the information of outlines in a database.

Execute the following statement to query the outlines in a database:

SELECT * FROM hint_plan.outlines_status;

The following table describes the response parameters.

Enable or disable an outline

You can use the following functions to enable or disable an outline.

• hint_plan.enable_outline: Enables the outline that corresponds to the ID that you specify.

• hint_plan.disable_outline: Disables the outline that corresponds to the ID that you specify.

Example

--- Query the outline IDs in the database.
SELECT * FROM hint_plan.outlines_status;

--- Enable the outline.
CALL hint_plan.enable_outline(1);

--- Disable the outline.
CALL hint_plan.disable_outline(1);

Delete an outline

You can use the hint_plan.del_outline function to delete an outline that is no longer used. Specify the ID of the outline that you want to delete.

Example

--- Query the outline IDs in the database.
SELECT * FROM hint_plan.outlines_status;

--- Delete the outline.
CALL hint_plan.del_outline(1);

Use case

1. Enable the statement outline feature. For more information, see the Enable the statement outline feature.

2. Prepare test data.

   CREATE TABLE t(a int,b int,PRIMARY KEY(a));
   CREATE INDEX ON t(b);
   INSERT INTO t SELECT i,i FROM generate_series(1,100000)i;
   ANALYZE t;

3. Execute the following statement to generate the execution plan. The optimizer considers that the cost of using the primary key index is equivalent to that of using the b column index. Different execution plans may be returned each time you execute the following statement.

   EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

   Sample result:

             QUERY PLAN
   -------------------------------
    Index Scan using t_b_idx on t
      Index Cond: (b = 1)
      Filter: (a = 1)
   (3 rows)

4. Use hints to intervene in the optimizer and select the primary key index in the execution plan. Then create an outline to keep the plan for future executions.

   1. Use hints to select the primary key index for the execution plan.

      EXPLAIN (costs off) /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;

      Sample result:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 1)
         Filter: (b = 1)
      (3 rows)

   2. Create an outline for the SQL statement. Make sure that the SQL statement is consistent with the application template. Common parameters, constants, hints, spaces, and comments do not affect the matching. Additional operations such as :: type conversion, specifying the schema of a table, changing the case of table names, and changing the case of column names affect the matching. For more information about the matching rules, see SQL_ID matching.

      CALL hint_plan.create_outline($$/*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;$$);

      Note

      If you use DMS to connect to the cluster and an error message similar to ERROR: invalid transaction termination occurs when you execute the preceding statements, you can use other clients, such as psql, to connect to the cluster. For more information, see Connect to a cluster.

   3. Execute the original SQL statement again. The execution plan uses the primary key index as expected:

      EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

      Sample result:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 1)
         Filter: (b = 1)
      (3 rows)

      Changing parameters of the original SQL statement and adding spaces and comments to the original SQL statement do not affect the outline matching. The execution plan still uses the primary key index.

      EXPLAIN (costs off) SELECT * -- comment
      FROM t
      WHERE b = 2 AND a = 4;

      Sample result:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 4)
         Filter: (b = 2)
      (3 rows)

5. Check the status of all outlines by querying the hint_plan.outlines_status view.

   SELECT * FROM hint_plan.outlines_status;

   Sample result:

    id |        sql_id        |        hints        | state | depends_rels |                           query_string                           | create_user |        create_time         |     total_hints     | calls
   ----+----------------------+---------------------+-------+--------------+------------------------------------------------------------------+-------------+----------------------------+---------------------+-------
     1 | -3220256307655713529 | IndexScan(t t_pkey) | Y     | {public.t}   | /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1; | postgres    | 2024-11-11 11:24:44.063143 | IndexScan(t t_pkey) |     2
   (1 row)

6. You can disable or delete an outline that is no longer used.

   • Disable the outline whose id is 1.

     CALL hint_plan.disable_outline(1);

   • Delete the outline whose id is 1.

     CALL hint_plan.del_outline(1);

   After you disable or delete an outline, execute the original SQL statement again. The original execution plan is generated.

   EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

   Sample result:

             QUERY PLAN
   -------------------------------
    Index Scan using t_b_idx on t
      Index Cond: (b = 1)
      Filter: (a = 1)
   (3 rows)