PolarDB:Adjust execution plans

Prerequisites

Before you begin, ensure that you have applied for the polar_outline whitelist in Quota Center. In the Actions column, click Apply next to polardb_pg_polar_outline.

Set up the extension

1. Create the polar_outline extension:

   CREATE EXTENSION polar_outline;

   All Outline functions and relations are stored in the polar_outline schema.

2. Add the schema to your search path so you can call Outline functions without the schema prefix:

   SET search_path TO "$user", public, polar_outline;

3. Enable fixed plan enforcement:

   SET polar_outline.use_stored_plan TO ON;

Prepare test data

Execute the following statements to create a table named t and insert data into the table:

CREATE TABLE t(a INT, b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000) i;

Update the data in the table to ensure that the optimizer uses accurate statistics:

ANALYZE t;

Execute a PREPARE statement:

PREPARE test AS SELECT * FROM t WHERE a=$1;

The test data is applicable only to the examples in this section. Modify the statements based on your business requirements.

The Outline feature provides a hint to control the execution plan to capture. If you use the hint in a statement, the execution plan of the statement is captured. Then, if you execute this statement without the hint, the statement is still executed based on the captured execution plan.

Capture and pin an execution plan

The Outline feature can only pin execution plans for PREPARE statements.

Choose a capture method based on your situation:

SELECT polar_outline_create('EXECUTE test(100)');

 polar_outline_create
----------------------
 t
(1 row)

Rewrite a query using plan switching

If performance issues occur after PolarDB rewrites SQL queries, you can use the Outline feature to rewrite SQL queries.

The two execution plans must be semantically equivalent. Switching non-equivalent plans may produce incorrect result sets.

The following example replaces the plan for SQL1 (which uses a Hash Join) with the more efficient plan from SQL2 (which uses a Nested Loop with a lateral join).

SQL1 — original query:

select t.a, t2.avg_b
from t join (select avg(b) as avg_b, a
             from t2
             group by a) t2
on t2.a = t.a and t.c < $1
order by t.a;

SQL2 — rewritten query with equivalent semantics:

select t.a, t2.avg_b
from t join lateral (select avg(b) as avg_b
                     from t2
                     where t2.a = t.a) as t2
on t.c < $1
order by t.a;

1. Prepare the test tables:

   CREATE TABLE t(a int, b int, c int);
   INSERT INTO t SELECT i % 100000, i, i FROM generate_series(1, 1000000) i;
   CREATE TABLE t2 AS SELECT * FROM t;
   CREATE INDEX ON t(c);
   CREATE INDEX ON t2(a);
   ANALYZE t, t2;

2. Capture the plans for both queries:

   PREPARE s1 AS
     SELECT t.a, t2.avg_b FROM t
     JOIN (SELECT avg(b) AS avg_b, a FROM t2 GROUP BY a) t2
     ON t2.a = t.a AND t.c < $1
     ORDER BY t.a;
   
   PREPARE s2 AS
     SELECT t.a, t2.avg_b FROM t
     JOIN LATERAL (SELECT avg(b) AS avg_b FROM t2 WHERE t2.a = t.a) AS t2
     ON t.c < $1
     ORDER BY t.a;
   
   SELECT polar_outline.polar_outline_create('EXECUTE s1(5)');
   SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');

   Both calls return t on success.

3. Switch the plan for s1 to use the plan from s2. The input parameters are the IDs of the two execution plans in the outline:

   SELECT polar_outline.polar_outline_switch(1, 2);

   Confirm with EXPLAIN that s1 now uses the Nested Loop plan:

   EXPLAIN (COSTS FALSE) EXECUTE s1(5);

   Expected output after switching:

                         QUERY PLAN
   -------------------------------------------------------
    Sort
      Sort Key: t.a
      ->  Nested Loop
            ->  Index Scan using t_c_idx on t
                  Index Cond: (c < $1)
            ->  Aggregate
                  ->  Bitmap Heap Scan on t2
                        Recheck Cond: (a = t.a)
                        ->  Bitmap Index Scan on t2_a_idx
                              Index Cond: (a = t.a)
   (10 rows)

4. Delete the outline for s2, which is no longer needed:

   SELECT polar_outline.polar_outline_delete(2);

Parameters

All parameters are in the polar_outline namespace. Set them with SET polar_outline.<parameter> TO <value>;.

Usage notes

• The Outline feature can only pin plans for PREPARE statements. Ad-hoc queries are not supported.

• A hint embedded in a statement captures the plan for that specific execution. Subsequent executions of the same statement without the hint still use the captured plan.

• Test data used in examples is for illustration only. Adjust table definitions and parameter values to match your workload.