PolarDB:Statement outline

How it works

OUTLINE associates a hint with a class of SQL statements identified by a SQL_ID. When a query matches the SQL_ID, the database applies the bound hints before optimization, overriding any hints embedded in the original SQL.

All OUTLINE operations go through the hint_plan schema:

Supported versions

To check your minor engine version, run SHOW polardb_version; or view it in the console. If the version is not supported, upgrade the minor engine version.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster running a supported version (see Supported versions)

• The pg_hint_plan extension installed at version 1.4.1 or later

• An account with permissions to run DDL statements in the target database

Enable the OUTLINE feature

Step 1. Verify that pg_hint_plan is installed at version 1.4.1 or later:

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

Expected output:

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

If the output differs, resolve the issue before proceeding:

Run these statements in the target database using an account with the appropriate permissions.

Step 2. Set the pg_hint_plan.polar_enable_outline parameter to on. Go to in the console. This change does not restart the cluster.

Create an OUTLINE

Pass the SQL statement with the desired hint to hint_plan.create_outline(). The function extracts the hint and binds it to the SQL class.

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

If you get ERROR: invalid transaction termination when running this statement from Data Management Service (DMS), switch to another client such as psql. See Connect to a database cluster.

View OUTLINEs

Query hint_plan.outlines_status to see all OUTLINEs in the current database:

SELECT * FROM hint_plan.outlines_status;

Enable or disable an OUTLINE

Use the OUTLINE id from hint_plan.outlines_status:

-- Enable OUTLINE with id = 1
CALL hint_plan.enable_outline(1);

-- Disable OUTLINE with id = 1
CALL hint_plan.disable_outline(1);

Delete an OUTLINE

CALL hint_plan.del_outline(1);

Usage notes

• OUTLINE takes priority over inline hints. After you create an OUTLINE for a SQL class, any hints embedded in the original SQL are ignored. Only the OUTLINE hints apply.

• Multiple OUTLINEs stack. You can create more than one OUTLINE for the same SQL class. The hints are combined in ascending id order.

• OUTLINE and `hint_table` are mutually exclusive. Enabling OUTLINE disables the hint_table feature of pg_hint_plan automatically.

Performance considerations

OUTLINE uses an internal high-concurrency cache to minimize overhead. Under standard Sysbench stress testing with OUTLINE enabled and active, Transactions Per Second (TPS) and Queries Per Second (QPS) decrease by approximately 1% to 2%.

Complete example

This example shows how to stabilize an execution plan that switches between two indexes depending on optimizer cost estimates.

1. Enable OUTLINE. See Enable the OUTLINE feature.

2. Create a test table and populate it:

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 plan. The optimizer chooses the index on column b:

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

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

4. Use a hint to force the primary key index and confirm it works:

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

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

5. Create an OUTLINE to lock in this plan:

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

6. Verify the OUTLINE is applied. Run the original query without any inline hint—the primary key index is still used:

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

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

The OUTLINE also matches queries with different parameter values, added comments, or extra whitespace:

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

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

7. Check the OUTLINE status:

SELECT * FROM hint_plan.outlines_status;

 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)

8. When the OUTLINE is no longer needed, disable or delete it:

-- Disable
CALL hint_plan.disable_outline(1);

-- Or delete permanently
CALL hint_plan.del_outline(1);

After disabling or deleting the OUTLINE, the optimizer reverts to its original plan:

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

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

Key concepts