Statement outline

Background

Adding hints directly to SQL is the standard way to control optimizer behavior, but it creates three practical problems in production:

• No access to the SQL source. Many applications generate SQL through middleware. There is no source file to edit.

• Every hint change triggers a release. Modifying application SQL and redeploying takes time and introduces risk.

• Hints accumulate and become unmanageable. As the number of hints grows, it becomes difficult to track which statements are affected and whether each hint is still active.

Statement outline stores hints at the database level and applies them automatically, solving all three problems without requiring application changes.

Key concepts

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster running version 2.0, revision version 2.0.14.28.0 or later. Run the following statement to check your revision version:

  SHOW polar_version;

  To upgrade your cluster, see Upgrade the version.

• The pg_hint_plan extension version 1.4.1 or later installed. For installation instructions, see pg_hint_plan.

Performance considerations

Statement outline uses a high-concurrency cache to minimize runtime overhead. After you enable the feature and add outlines, SysBench stress tests show a decrease of only 1%–2% in TPS and QPS.

Enable statement outline

1. Verify that the pg_hint_plan extension is installed and meets the version requirement:

   • Extension not installed:

     CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

   • Extension version earlier than 1.4.1:

     ALTER EXTENSION pg_hint_plan UPDATE;

   Use an account with the necessary permissions to run the following statements.

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

   The following output confirms the extension is installed and the version is sufficient:

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

   If the extension is missing or outdated, run the appropriate statement:

2. Set pg_hint_plan.polar_enable_outline to on. This change takes effect immediately without a cluster restart. For instructions on setting cluster parameters in the PolarDB console, see .

Use statement outline

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

SELECT * FROM hint_plan.outlines_status;

-- Query outline IDs.
SELECT id, sql_id, hints, state FROM hint_plan.outlines_status;

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

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

-- Query outline IDs.
SELECT id, sql_id, hints, state FROM hint_plan.outlines_status;

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

Usage notes

Example

This example walks through a complete workflow: setting up test data, diagnosing an unstable execution plan, fixing it with an outline, and verifying the result.

1. Enable statement outline. See Enable statement outline.

2. Create a test table and populate it with 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. Check the current execution plan. Because the optimizer considers the cost of using the primary key index and the b column index to be similar, the plan may vary across runs:

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

   Sample output:

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

4. Fix the execution plan to use the primary key index:

   1. Use a hint to verify the target plan:

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

      Sample output:

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

   2. Create an outline based on the hinted SQL statement. The SQL statement must match the query template used by your application. Parameters, constants, hints, spaces, and comments do not affect matching. Operations such as :: type casting, explicit schema names, and changes to table or column name case do affect matching. For full details, see SQL_ID matching rules.

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

      Note: If you connect through DMS and get an error similar to ERROR: invalid transaction termination, use a different client such as psql instead. See Connect to a cluster.

   3. Run the original SQL statement without any hints. The outline is applied automatically and the execution plan uses the primary key index:

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

      Sample output:

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

      Changing parameter values or adding whitespace and comments does not affect outline matching:

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

      Sample output:

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

5. Verify the outline status:

   SELECT * FROM hint_plan.outlines_status;

   Sample output:

   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)

   The calls column shows 2, confirming the outline was matched and applied twice.

6. When the outline is no longer needed, disable or delete it: After disabling or deleting the outline, the original SQL statement returns to its default execution plan:

   • Disable outline 1:

     CALL hint_plan.disable_outline(1);

   • Delete outline 1:

     CALL hint_plan.del_outline(1);

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

   Sample output:

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