PolarDB PostgreSQL版(兼容Oracle)提供了Outline功能,用于抓取、查看和删除固定的执行计划。

功能介绍

执行计划管理SPM(SQL Plan Management)是一种预防性机制,可以使优化器自动管理执行计划,确保数据库仅使用已知或经过验证的计划。

Outline是SPM的子集,仅支持固定执行计划,不支持演进执行计划。目前PolarDB的Outline功能仅支持固定PREPARE类型语句的执行计划。

PolarDB的Outline功能是通过polar_outline插件实现的,如需使用,请前往配额中心,在配额名称PolarDB PG 调整执行计划操作列,单击申请,申请开启。

创建插件

创建插件语句如下:

CREATE EXTENSION polar_outline;

配置参数

打开Outline固定执行计划功能,开启语句如下:

SET polar_outline.use_stored_plan TO ON;

由于Outline定义的函数和关系(Relation)都在polar_outline这个schema下,为了方便使用,您可以通过如下语句设置路径。

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

准备测试数据

创建表t,并在表中插入数据。语句如下:

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

更新表数据以确保优化器使用的统计信息正确。

ANALYZE t;

执行一个PREPARE类型的语句。

PREPARE test AS SELECT * FROM t WHERE a=$1;
说明
  • 测试数据仅适用于本文中的示例,实际使用中请根据您的实际情况进行调整。
  • 您也可以使用hint功能来控制您想要被outline抓取的执行计划,从而使其他没有使用hint的语句也会被outline固定成您想要的计划。

抓取和固定执行计划

目前PolarDB提供了两种方法来抓取和固定执行计划,分别如下:

  • 使用polar_outline_create函数抓取执行计划(推荐使用),语句如下:
    SELECT polar_outline_create('EXECUTE test(100)');

    返回信息如下:

     polar_outline_create 
    ----------------------
     t
    (1 row)
  • 使用执行计划抓取功能,该方法更适合批量抓取执行计划,操作方式如下:
    重要 开启执行计划抓取功能会导致计划缓存(plancache)
    1. 开启抓取执行计划功能,语句如下:
      SET polar_outline.capture_plan TO ON;
    2. 抓取执行计划,语句如下:
      EXECUTE test(100);

      返回信息如下:

        a  |  b
      -----+-----
       100 | 100
      (1 row)
    3. 关闭抓取执行计划功能,语句如下:
      SET polar_outline.capture_plan TO OFF;

参数配置

参数说明
use_stored_plan打开或关闭Outline固定执行计划。
  • 打开命令如下:
    SET polar_outline.use_stored_plan TO ON;
  • 关闭命令如下:
    SET polar_outline.use_stored_plan TO OFF;
capture_plan打开或关闭Outline计划抓取开关。
  • 打开命令如下:
    SET polar_outline.capture_plan TO ON;
  • 关闭命令如下:
    SET polar_outline.capture_plan TO OFF;
log_usage控制outline内部日志的输出级别。
set polar_outline.log_usage To none;
set polar_outline.log_usage To debug;
set polar_outline.log_usage To debug1;
set polar_outline.log_usage To debug2;
set polar_outline.log_usage To debug3;
set polar_outline.log_usage To debug4;
set polar_outline.log_usage To debug5;
set polar_outline.log_usage To log;
set polar_outline.log_usage To info;
set polar_outline.log_usage To notice;
set polar_outline.log_usage To warning;

通过outline实现查询改写

在PolarDB查询改写不符合预期时,您可以使用outline来实现SQL的查询改写。
说明 错误的使用可能会导致PolarDB输出错误的执行计划,返回错误的结果集,请确保改写计划的等价。
如下所示,SQL1和SQL2是等价的。
SQL1: 
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:
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;

通过以下步骤展示如何通过outline将SQL1查询改写成SQL2的。

  1. 执行以下命令,准备数据。
    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. 执行以下命令,将准备改写的原SQL和改写后的SQL抓取进outline中。
    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)');
     polar_outline_create 
    ----------------------
     t
    (1 row)
    
    SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');
     polar_outline_create 
    ----------------------
     t
    (1 row)
  3. 执行以下命令,使用polar_outline_switch功能交换两个SQL的计划。
    说明 输入的参数为两个计划在outline中的ID,原SQL的计划即被改写为改写后的SQL的计划。
    SELECT polar_outline.polar_outline_switch(1,2);
     polar_outline_switch 
    ----------------------
     t
    (1 row)
  4. 执行以下命令,删除outline中改写后的SQL。
    说明 计划改写完成后,需要清除outline中SQL2的无用计划。
    SELECT polar_outline.polar_outline_delete(2);
  5. 通过outline实现查询改写的结果如下所示:
    查询改写前:s1是改写前的计划
    EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                    QUERY PLAN                 
    -------------------------------------------
     Sort
       Sort Key: t.a
       ->  Hash Join
             Hash Cond: (t.a = t2.a)
             ->  Index Scan using t_c_idx on t
                   Index Cond: (c < $1)
             ->  Hash
                   ->  HashAggregate
                         Group Key: t2.a
                         ->  Seq Scan on t2
    (10 rows)
    
    SELECT polar_outline_switch(1,2);
     polar_outline_switch 
    ----------------------
     t
    (1 row)
    
    查询改写后:s1是改写后s2的计划
    EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                          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)