VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

SQL 执行计划绑定

功能描述

支持将SQL语句的query_id与执行计划的plan_id绑定,通过绑定的执行计划执行语句。

  • query_id:查询语句ID。
  • plan_id:执行计划ID。

上述ID可以从系统表PG_SQLPLAN中查询。

实际运用中,可以通过更改query_id与plan_id的绑定关系,以达到在线改变执行计划的目的。

要素参考

  • 本特性依赖SQL PATCH实现,前提需设置参数以记录语句的执行信息:

  • 参数enable_sqlbind决定是否允许语句使用绑定的计划执行。

  • 函数_bind_p用于进行语句中常量参数化,该函数无任何内部操作。

  • 函数show_sql_plan用于输出格式化后的执行计划树。

  • DBE_SQL_UTIL.import_sql_bind和DBE_SQL_UTIL.drop_sql_bind分别用于绑定执行计划和删除绑定。

  • 系统表PG_SQLPLAN存储语句执行计划的信息。

  • 系统表PG_SQLBIND存储语句执行计划的绑定信息。

使用限制

  • 仅Vastbase G100 V2.2 Build 10(Patch No.17)及以后版本支持此功能。

  • 当前仅支持查询语句(SELECT)的执行计划绑定。

  • 数据库进行补丁升级后,unique_query_id可能由于升级前后计算的算法不一致导致不可用,需要重新收集。

  • 数据库进行补丁升级后,执行计划可能由于升级前后节点及节点成员不一致导致不可用,需要重新收集并绑定。

  • 不支持查询缓存、with function、with cte存在INSERT/UPDATE/DELETE、SMP的语句执行计划绑定。

  • 不支持存储过程中SQL语句的执行计划绑定。

  • MySQL、SQL Server兼容模式下,如果SQL中存在@变量,不支持执行计划绑定。

  • 绑定的执行计划中存在对象OID,将绑定的执行计划导出后,再导入到另一个实例中,可能由于对象OID不一致导致执行计划不可用。

使用流程

准备工作

1、创建测试表并插入数据。

create table table_1027465a(id int,col int);
insert into table_1027465a values(generate_series(1,1000),generate_series(1,1000));
create table table_1027465b(id int,col int);
insert into table_1027465b values(generate_series(5,10),generate_series(5,10));
ANALYZE table_1027465a;  --收集统计信息用于生成默认执行计划
ANALYZE table_1027465b;

2、设置参数以记录语句执行的信息。

set log_min_duration_statement=0;
set track_stmt_stat_level='L1,L1';

3、执行查询语句,为方便描述,分别记作Query1、Query2。

select * from table_1027465a as a,table_1027465b as b where a.id=b.id;  --Query1
select * from table_1027465a as a,table_1027465b as b where a.id=b.id and a.id=_bind_p(10);  --Query2

返回结果依次如下:

 id | col | id | col
----+-----+----+-----
  5 |   5 |  5 |   5
  6 |   6 |  6 |   6
  7 |   7 |  7 |   7
  8 |   8 |  8 |   8
  9 |   9 |  9 |   9
 10 |  10 | 10 |  10
(6 rows)

 id | col | id | col
----+-----+----+-----
 10 |  10 | 10 |  10
(1 row)

4、关闭语句记录功能。

set track_stmt_stat_level to default;

5、查看记录的语句执行信息,获取Query1、Query2的query_id和plan_id。

select unique_query_id,full_query_id,plan_id,full_query from pg_sqlplan order by start_time desc limit 2;

返回结果如下:

 unique_query_id | full_query_id |  plan_id   |                                         full_query
-----------------+---------------+------------+---------------------------------------------------------------------------------------------
       651242588 |     882708871 | 2005146881 | select * from table_1027465a as a,table_1027465b as b where a.id=b.id and a.id=_bind_p(10);
        15851573 |    2625746187 |  199947619 | select * from table_1027465a as a,table_1027465b as b where a.id=b.id;
(2 rows)

6、增加索引扫描的执行计划:Query3的查询计划走索引扫描。

create index idx_a on table_1027465a(id);
set track_stmt_stat_level='L1,L1';
select * from table_1027465a where id=1;  --Query3
set track_stmt_stat_level to default;
select unique_query_id,full_query_id,plan_id,full_query from pg_sqlplan order by start_time desc limit 1;  --获取Query3的query_id和plan_id

获取Query3的query_id和plan_id如下:

 unique_query_id | full_query_id |  plan_id   |                full_query
-----------------+---------------+------------+------------------------------------------
      2120403277 |    3934437041 | 1557260801 | select * from table_1027465a where id=1;
(1 row)

使用绑定计划执行

1、指定full_query_id为其绑定执行计划。

select DBE_SQL_UTIL.import_sql_bind('bind1',2625746187,199947619);  --绑定Query1与bind1
select DBE_SQL_UTIL.import_sql_bind('bind2',882708871,2005146881);  --绑定Query2与bind2
select DBE_SQL_UTIL.import_sql_bind('bind3',3934437041,1557260801);  --绑定Query3与bind3

2、查询绑定计划是否已绑定。

set enable_sqlbind=on;  --允许Query使用绑定的查询计划执行
  • 查看Query1的执行计划:

    explain select * from table_1027465a as a,table_1027465b as b where a.id=b.id;
    

    返回结果如下,使用bind1绑定的执行计划:

                               QUERY PLAN
    ----------------------------------------------------------------------------
    [SqlBind]: This SQL is using bind plan, bindname:bind1.
    Hash Join  (cost=1.14..19.73 rows=6 width=16)
    Hash Cond: (a.id = b.id)
    ->  Seq Scan on table_1027465a a  (cost=0.00..16.00 rows=1000 width=8)
    ->  Hash  (cost=1.06..1.06 rows=6 width=8)
            ->  Seq Scan on table_1027465b b  (cost=0.00..1.06 rows=6 width=8)
    (6 rows)
    
  • 查看Query2的执行计划:

    explain select * from table_1027465a as a,table_1027465b as b where a.id=b.id and a.id=10;
    

    返回结果如下,使用bind2绑定的执行计划:

                                QUERY PLAN
    -----------------------------------------------------------------------
    [SqlBind]: This SQL is using bind plan, bindname:bind2.
    Nested Loop  (cost=0.00..22.10 rows=1 width=16)
    ->  Seq Scan on table_1027465a a  (cost=0.00..21.00 rows=1 width=8)
            Filter: (id = 10)
    ->  Seq Scan on table_1027465b b  (cost=0.00..1.09 rows=1 width=8)
            Filter: (id = 10)
    (6 rows)
    
  • Query2的扩展:由于生成Query2的执行计划时,在过滤条件“a.id=10”中使用了_bind_p函数进行了常量参数化,因此bind2的适用范围实际为“a.id=常量”,例如:

    explain select * from table_1027465a as a,table_1027465b as b where a.id=b.id and a.id=11;
    

    返回结果如下,仍可使用bind2绑定的执行计划:

                                QUERY PLAN
    -----------------------------------------------------------------------
    [SqlBind]: This SQL is using bind plan, bindname:bind2.
    Nested Loop  (cost=0.00..22.10 rows=1 width=16)
    ->  Seq Scan on table_1027465a a  (cost=0.00..21.00 rows=1 width=8)
            Filter: (id = 11)
    ->  Seq Scan on table_1027465b b  (cost=0.00..1.09 rows=1 width=8)
            Filter: (id = 11)
    (6 rows)
    
  • 查看Query3的执行计划:

    explain select * from table_1027465a where id=1;
    

    返回结果如下,使用bind3绑定的执行计划:

                                    QUERY PLAN
    ----------------------------------------------------------------------------
    [SqlBind]: This SQL is using bind plan, bindname:bind3.
    Index Scan using idx_a on table_1027465a  (cost=0.00..8.27 rows=1 width=8)
    Index Cond: (id = 1)
    (3 rows)
    

3、调用show_sql_plan函数输出格式化计划树。

select show_sql_plan(query_plan_tree) from pg_sqlplan limit 1;

返回形如以下格式的计划树:

  show_sql_plan
--------------------------------------------------
    {PLANNEDSTMT                                 +
    :commandType 1                               +
    :queryId 1970324836976892                    +
    :queryId4plugin 0                            +
    :hasReturning false                          +
    :hasModifyingCTE false                       +
    :canSetTag true                              +
    :transientPlan false                         +
    :dependsOnRole false                         +
    :planTree                                    +
       {HASHJOIN                                 +
       :plan_node_id 1                           +
       :parent_node_id 0                         +
       :exec_type 1                              +
       :startup_cost 1.14                        +
       :total_cost 19.73                         +
       :plan_rows 6                              +
       :multiple 1                               +
       :plan_width 16                            +
       :targetlist (                             +
          {TARGETENTRY                           +
          :expr                                  +
             {VAR                                +
             :varno 65001                        +
             :varattno 1                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 1                         +
             :varoattno 1                        +
             :location 7                         +
             :name <>                            +
             }                                   +
          :resno 1                               +
          :resname id                            +
          :ressortgroupref 0                     +
          :resorigtbl 16904                      +
          :resorigcol 1                          +
          :resjunk false                         +
          :col_no_quoted false                   +
          :rtindex 0                             +
          }                                      +
          {TARGETENTRY                           +
          :expr                                  +
             {VAR                                +
             :varno 65001                        +
             :varattno 2                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 1                         +
             :varoattno 2                        +
             :location 7                         +
             :name <>                            +
             }                                   +
          :resno 2                               +
          :resname col                           +
          :ressortgroupref 0                     +
          :resorigtbl 16904                      +
          :resorigcol 2                          +
          :resjunk false                         +
          :col_no_quoted false                   +
          :rtindex 0                             +
          }                                      +
          {TARGETENTRY                           +
          :expr                                  +
             {VAR                                +
             :varno 65000                        +
             :varattno 1                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 2                         +
             :varoattno 1                        +
             :location 7                         +
             :name <>                            +
             }                                   +
          :resno 3                               +
          :resname id                            +
          :ressortgroupref 0                     +
          :resorigtbl 16907                      +
          :resorigcol 1                          +
          :resjunk false                         +
          :col_no_quoted false                   +
          :rtindex 0                             +
          }                                      +
          {TARGETENTRY                           +
          :expr                                  +
             {VAR                                +
             :varno 65000                        +
             :varattno 2                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 2                         +
             :varoattno 2                        +
             :location 7                         +
             :name <>                            +
             }                                   +
          :resno 4                               +
          :resname col                           +
          :ressortgroupref 0                     +
          :resorigtbl 16907                      +
          :resorigcol 2                          +
          :resjunk false                         +
          :col_no_quoted false                   +
          :rtindex 0                             +
          }                                      +
       )                                         +
       :qual <>                                  +
       :lefttree                                 +
          {SEQSCAN                               +
          :plan_node_id 2                        +
          :parent_node_id 1                      +
          :exec_type 0                           +
          :startup_cost 0.00                     +
          :total_cost 16.00                      +
          :plan_rows 1000                        +
          :multiple 1                            +
          :plan_width 8                          +
          :targetlist (                          +
             {TARGETENTRY                        +
             :expr                               +
                {VAR                             +
                :varno 1                         +
                :varattno 1                      +
                :vartype 23                      +
                :vartypmod -1                    +
                :varcollid 0                     +
                :varlevelsup 0                   +
                :varnoold 1                      +
                :varoattno 1                     +
                :location -1                     +
                :name <>                         +
                }                                +
             :resno 1                            +
             :resname <>                         +
             :ressortgroupref 0                  +
             :resorigtbl 0                       +
             :resorigcol 0                       +
             :resjunk false                      +
             :col_no_quoted false                +
             :rtindex 0                          +
             }                                   +
             {TARGETENTRY                        +
             :expr                               +
                {VAR                             +
                :varno 1                         +
                :varattno 2                      +
                :vartype 23                      +
                :vartypmod -1                    +
                :varcollid 0                     +
                :varlevelsup 0                   +
                :varnoold 1                      +
                :varoattno 2                     +
                :location -1                     +
                :name <>                         +
                }                                +
             :resno 2                            +
             :resname <>                         +
             :ressortgroupref 0                  +
             :resorigtbl 0                       +
             :resorigcol 0                       +
             :resjunk false                      +
             :col_no_quoted false                +
             :rtindex 0                          +
             }                                   +
          )                                      +
          :qual <>                               +
          :lefttree <>                           +
          :righttree <>                          +
          :ispwj false                           +
          :paramno -1                            +
          :subparamno 0                          +
          :initPlan <>                           +
          :distributed_keys <>                   +
          :exec_nodes                            +
             {EXEC_NODES                         +
             :primarynodelist <>                 +
             :nodeList <>                        +
             :group_oid 0                        +
             :bms_data_nodeids (b)               +
             :baselocatortype H                  +
             :en_expr <>                         +
             :en_relid 0                         +
             :accesstype 0                       +
             :en_dist_vars <>                    +
             :bucketmapIdx -2147467264           +
             :nodelist_is_nil false              +
             :original_nodeList <>               +
             :dynamic_en_expr <>                 +
             :bucketid 0                         +
             :bucketexpr <>                      +
             :bucketrelid 0                      +
             }                                   +
          :extParam (b)                          +
          :allParam (b)                          +
          :vec_output false                      +
          :hasUniqueResults false                +
          :isDeltaTable false                    +
          :operatorMemKB[0] 0                    +
          :operatorMemKB[1] 0                    +
          :operatorMaxMem 0                      +
          :parallel_enabled false                +
          :hasHashFilter false                   +
          :var_list <>                           +
          :filterIndexList <>                    +
          :dop 1                                 +
          :recursive_union_plan_nodeid 0         +
          :recursive_union_controller false      +
          :control_plan_nodeid 0                 +
          :is_sync_plannode false                +
          :pred_rows 0                           +
          :pred_startup_time 0                   +
          :pred_total_time 0                     +
          :pred_max_memory 0                     +
          :scanrelid 1                           +
          :isPartTbl false                       +
          :itrs 0                                +
          :pruningInfo <>                        +
          :bucketInfo <>                         +
          :partScanDirection 0                   +
          :scan_qual_optimized false             +
          :predicate_pushdown_optimized false    +
          :tablesample <>                        +
          :opMem 0.00                            +
          :minMem 0.00                           +
          :maxMem 0.00                           +
          :regressCost 0.000000                  +
          :scanBatchMode false                   +
          }                                      +
       :righttree                                +
          {HASH                                  +
          :plan_node_id 3                        +
          :parent_node_id 1                      +
          :exec_type 0                           +
          :startup_cost 1.06                     +
          :total_cost 1.06                       +
          :plan_rows 6                           +
          :multiple 1                            +
          :plan_width 8                          +
          :targetlist (                          +
             {TARGETENTRY                        +
             :expr                               +
                {VAR                             +
                :varno 65001                     +
                :varattno 1                      +
                :vartype 23                      +
                :vartypmod -1                    +
                :varcollid 0                     +
                :varlevelsup 0                   +
                :varnoold 2                      +
                :varoattno 1                     +
                :location -1                     +
                :name <>                         +
                }                                +
             :resno 1                            +
             :resname <>                         +
             :ressortgroupref 0                  +
             :resorigtbl 0                       +
             :resorigcol 0                       +
             :resjunk false                      +
             :col_no_quoted false                +
             :rtindex 0                          +
             }                                   +
             {TARGETENTRY                        +
             :expr                               +
                {VAR                             +
                :varno 65001                     +
                :varattno 2                      +
                :vartype 23                      +
                :vartypmod -1                    +
                :varcollid 0                     +
                :varlevelsup 0                   +
                :varnoold 2                      +
                :varoattno 2                     +
                :location -1                     +
                :name <>                         +
                }                                +
             :resno 2                            +
             :resname <>                         +
             :ressortgroupref 0                  +
             :resorigtbl 0                       +
             :resorigcol 0                       +
             :resjunk false                      +
             :col_no_quoted false                +
             :rtindex 0                          +
             }                                   +
          )                                      +
          :qual <>                               +
          :lefttree                              +
             {SEQSCAN                            +
             :plan_node_id 4                     +
             :parent_node_id 3                   +
             :exec_type 0                        +
             :startup_cost 0.00                  +
             :total_cost 1.06                    +
             :plan_rows 6                        +
             :multiple 1                         +
             :plan_width 8                       +
             :targetlist (                       +
                {TARGETENTRY                     +
                :expr                            +
                   {VAR                          +
                   :varno 2                      +
                   :varattno 1                   +
                   :vartype 23                   +
                   :vartypmod -1                 +
                   :varcollid 0                  +
                   :varlevelsup 0                +
                   :varnoold 2                   +
                   :varoattno 1                  +
                   :location 7                   +
                   :name <>                      +
                   }                             +
                :resno 1                         +
                :resname <>                      +
                :ressortgroupref 0               +
                :resorigtbl 0                    +
                :resorigcol 0                    +
                :resjunk false                   +
                :col_no_quoted false             +
                :rtindex 0                       +
                }                                +
                {TARGETENTRY                     +
                :expr                            +
                   {VAR                          +
                   :varno 2                      +
                   :varattno 2                   +
                   :vartype 23                   +
                   :vartypmod -1                 +
                   :varcollid 0                  +
                   :varlevelsup 0                +
                   :varnoold 2                   +
                   :varoattno 2                  +
                   :location 7                   +
                   :name <>                      +
                   }                             +
                :resno 2                         +
                :resname <>                      +
                :ressortgroupref 0               +
                :resorigtbl 0                    +
                :resorigcol 0                    +
                :resjunk false                   +
                :col_no_quoted false             +
                :rtindex 0                       +
                }                                +
             )                                   +
             :qual <>                            +
             :lefttree <>                        +
             :righttree <>                       +
             :ispwj false                        +
             :paramno -1                         +
             :subparamno 0                       +
             :initPlan <>                        +
             :distributed_keys <>                +
             :exec_nodes                         +
                {EXEC_NODES                      +
                :primarynodelist <>              +
                :nodeList <>                     +
                :group_oid 0                     +
                :bms_data_nodeids (b)            +
                :baselocatortype H               +
                :en_expr <>                      +
                :en_relid 0                      +
                :accesstype 0                    +
                :en_dist_vars <>                 +
                :bucketmapIdx -2147467264        +
                :nodelist_is_nil false           +
                :original_nodeList <>            +
                :dynamic_en_expr <>              +
                :bucketid 0                      +
                :bucketexpr <>                   +
                :bucketrelid 0                   +
                }                                +
             :extParam (b)                       +
             :allParam (b)                       +
             :vec_output false                   +
             :hasUniqueResults false             +
             :isDeltaTable false                 +
             :operatorMemKB[0] 0                 +
             :operatorMemKB[1] 0                 +
             :operatorMaxMem 0                   +
             :parallel_enabled false             +
             :hasHashFilter false                +
             :var_list <>                        +
             :filterIndexList <>                 +
             :dop 1                              +
             :recursive_union_plan_nodeid 0      +
             :recursive_union_controller false   +
             :control_plan_nodeid 0              +
             :is_sync_plannode false             +
             :pred_rows 0                        +
             :pred_startup_time 0                +
             :pred_total_time 0                  +
             :pred_max_memory 0                  +
             :scanrelid 2                        +
             :isPartTbl false                    +
             :itrs 0                             +
             :pruningInfo <>                     +
             :bucketInfo <>                      +
             :partScanDirection 0                +
             :scan_qual_optimized false          +
             :predicate_pushdown_optimized false +
             :tablesample <>                     +
             :opMem 0.00                         +
             :minMem 0.00                        +
             :maxMem 0.00                        +
             :regressCost 0.000000               +
             :scanBatchMode false                +
             }                                   +
          :righttree <>                          +
          :ispwj false                           +
          :paramno 0                             +
          :subparamno 0                          +
          :initPlan <>                           +
          :distributed_keys <>                   +
          :exec_nodes                            +
             {EXEC_NODES                         +
             :primarynodelist <>                 +
             :nodeList <>                        +
             :group_oid 0                        +
             :bms_data_nodeids (b)               +
             :baselocatortype H                  +
             :en_expr <>                         +
             :en_relid 0                         +
             :accesstype 0                       +
             :en_dist_vars <>                    +
             :bucketmapIdx -2147467264           +
             :nodelist_is_nil false              +
             :original_nodeList <>               +
             :dynamic_en_expr <>                 +
             :bucketid 0                         +
             :bucketexpr <>                      +
             :bucketrelid 0                      +
             }                                   +
          :extParam (b)                          +
          :allParam (b)                          +
          :vec_output false                      +
          :hasUniqueResults false                +
          :isDeltaTable false                    +
          :operatorMemKB[0] 0                    +
          :operatorMemKB[1] 0                    +
          :operatorMaxMem 0                      +
          :parallel_enabled false                +
          :hasHashFilter false                   +
          :var_list <>                           +
          :filterIndexList <>                    +
          :dop 1                                 +
          :recursive_union_plan_nodeid 0         +
          :recursive_union_controller false      +
          :control_plan_nodeid 0                 +
          :is_sync_plannode false                +
          :pred_rows 0                           +
          :pred_startup_time 0                   +
          :pred_total_time 0                     +
          :pred_max_memory 0                     +
          :skewColumn 1                          +
          :skewInherit false                     +
          :skewColType 23                        +
          :skewColTypmod -1                      +
          }                                      +
       :ispwj false                              +
       :paramno -1                               +
       :subparamno 0                             +
       :initPlan <>                              +
       :distributed_keys <>                      +
       :exec_nodes                               +
          {EXEC_NODES                            +
          :primarynodelist <>                    +
          :nodeList (i 0)                        +
          :group_oid 0                           +
          :bms_data_nodeids (b 0)                +
          :baselocatortype R                     +
          :en_expr <>                            +
          :en_relid 0                            +
          :accesstype 0                          +
          :en_dist_vars <>                       +
          :bucketmapIdx 0                        +
          :nodelist_is_nil false                 +
          :original_nodeList <>                  +
          :dynamic_en_expr <>                    +
          :bucketid 0                            +
          :bucketexpr <>                         +
          :bucketrelid 0                         +
          }                                      +
       :extParam (b)                             +
       :allParam (b)                             +
       :vec_output false                         +
       :hasUniqueResults false                   +
       :isDeltaTable false                       +
       :operatorMemKB[0] 0                       +
       :operatorMemKB[1] 0                       +
       :operatorMaxMem 0                         +
       :parallel_enabled false                   +
       :hasHashFilter false                      +
       :var_list <>                              +
       :filterIndexList <>                       +
       :dop 1                                    +
       :recursive_union_plan_nodeid 0            +
       :recursive_union_controller false         +
       :control_plan_nodeid 0                    +
       :is_sync_plannode false                   +
       :pred_rows 0                              +
       :pred_startup_time 0                      +
       :pred_total_time 0                        +
       :pred_max_memory 0                        +
       :jointype 0                               +
       :joinqual <>                              +
       :optimizable false                        +
       :nulleqqual <>                            +
       :skewoptimize 0                           +
       :hashclauses (                            +
          {OPEXPR                                +
          :opno 96                               +
          :opfuncid 65                           +
          :opresulttype 16                       +
          :opretset false                        +
          :opcollid 0                            +
          :inputcollid 0                         +
          :args (                                +
             {VAR                                +
             :varno 65001                        +
             :varattno 1                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 1                         +
             :varoattno 1                        +
             :location 60                        +
             :name id                            +
             }                                   +
             {VAR                                +
             :varno 65000                        +
             :varattno 1                         +
             :vartype 23                         +
             :vartypmod -1                       +
             :varcollid 0                        +
             :varlevelsup 0                      +
             :varnoold 2                         +
             :varoattno 1                        +
             :location 65                        +
             :name id                            +
             }                                   +
          )                                      +
          :location -1                           +
          }                                      +
       )                                         +
       :streamBothSides false                    +
       :transferFilterFlag true                  +
       :rebuildHashTable false                   +
       :isSonicHash true                         +
       :opMem 4096.00                            +
       :minMem 8.01                              +
       :maxMem 256.23                            +
       :regressCost 20.000000                    +
       }                                         +
    :hasIgnore false                             +
    :rtable (                                    +
       {RTE                                      +
       :alias                                    +
          {ALIAS                                 +
          :aliasname a                           +
          :colnames <>                           +
          }                                      +
       :eref                                     +
          {ALIAS                                 +
          :aliasname a                           +
          :colnames ("id" "col")                 +
          }                                      +
       :rtekind 0                                +
       :relname table_1027465a                   +
       :partAttrNum <>                           +
       :relid 16904                              +
       :relkind r                                +
       :isResultRel false                        +
       :tablesample <>                           +
       :timecapsule <>                           +
       :partitionOid 0                           +
       :isContainPartition false                 +
       :isContainSubPartition false              +
       :subpartitionOid 0                        +
       :refSynOid 0                              +
       :ispartrel false                          +
       :ignoreResetRelid false                   +
       :pname <>                                 +
       :partid_list <>                           +
       :plist <>                                 +
       :relname table_1027465a                   +
       :relnamespace public                      +
       :lateral false                            +
       :inh false                                +
       :inFromCl true                            +
       :requiredPerms 2                          +
       :checkAsUser 0                            +
       :selectedCols (b 14 15)                   +
       :modifiedCols (b)                         +
       :insertedCols (b)                         +
       :updatedCols (b)                          +
       :extraUpdatedCols (b)                     +
       :orientation 2                            +
       :securityQuals <>                         +
       :subquery_pull_up false                   +
       :correlated_with_recursive_cte false      +
       :relhasbucket false                       +
       :isbucket false                           +
       :bucketmapsize 0                          +
       :buckets <>                               +
       :isexcluded false                         +
       :sublink_pull_up false                    +
       :is_ustore false                          +
       :partitionOidList <>                      +
       :subpartitionOidList <>                   +
       }                                         +
       {RTE                                      +
       :alias                                    +
          {ALIAS                                 +
          :aliasname b                           +
          :colnames <>                           +
          }                                      +
       :eref                                     +
          {ALIAS                                 +
          :aliasname b                           +
          :colnames ("id" "col")                 +
          }                                      +
       :rtekind 0                                +
       :relname table_1027465b                   +
       :partAttrNum <>                           +
       :relid 16907                              +
       :relkind r                                +
       :isResultRel false                        +
       :tablesample <>                           +
       :timecapsule <>                           +
       :partitionOid 0                           +
       :isContainPartition false                 +
       :isContainSubPartition false              +
       :subpartitionOid 0                        +
       :refSynOid 0                              +
       :ispartrel false                          +
       :ignoreResetRelid false                   +
       :pname <>                                 +
       :partid_list <>                           +
       :plist <>                                 +
       :relname table_1027465b                   +
       :relnamespace public                      +
       :lateral false                            +
       :inh false                                +
       :inFromCl true                            +
       :requiredPerms 2                          +
       :checkAsUser 0                            +
       :selectedCols (b 14 15)                   +
       :modifiedCols (b)                         +
       :insertedCols (b)                         +
       :updatedCols (b)                          +
       :extraUpdatedCols (b)                     +
       :orientation 2                            +
       :securityQuals <>                         +
       :subquery_pull_up false                   +
       :correlated_with_recursive_cte false      +
       :relhasbucket false                       +
       :isbucket false                           +
       :bucketmapsize 0                          +
       :buckets <>                               +
       :isexcluded false                         +
       :sublink_pull_up false                    +
       :is_ustore false                          +
       :partitionOidList <>                      +
       :subpartitionOidList <>                   +
       }                                         +
    )                                            +
    :resultRelations <>                          +
    :utilityStmt <>                              +
    :subplans <>                                 +
    :rewindPlanIDs (b)                           +
    :rowMarks <>                                 +
    :relationOids (o 16904 16907)                +
    :invalItems <>                               +
    :nParamExec 0                                +
    :num_streams 0                               +
    :max_push_sql_num 0                          +
    :gather_count 0                              +
    :num_nodes 1                                 +
    :instrument_option 0                         +
    :num_plannodes 4                             +
    :query_mem[0] 0                              +
    :query_mem[1] 0                              +
    :assigned_query_mem[0] 0                     +
    :assigned_query_mem[1] 0                     +
    :num_bucketmaps 0                            +
    :query_string <>                             +
    :subplan_ids <>                              +
    :initPlan <>                                 +
    :dataDestRelIndex 0                          +
    :MaxBloomFilterNum 0                         +
    :query_dop 1                                 +
    :in_compute_pool false                       +
    :has_obsrel false                            +
    :ng_num 0                                    +
    :isRowTriggerShippable false                 +
    :is_stream_plan false                        +
    :sqlbindname <>                              +
    }                                            +

(1 row)

4、删除索引,使得原本计划走索引的bind3失效,再次查看Query3的执行计划:

drop index idx_a;  
explain select * from table_1027465a where id=1;  --查看Query3的执行计划

返回结果如下,由于索引已不存在,bind3失效,走原逻辑生成顺序扫描计划。

                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on table_1027465a  (cost=0.00..18.50 rows=1 width=8)
   Filter: (id = 1)
(2 rows)

5、清理环境。

DROP TABLE table_1027465a;
DROP TABLE table_1027465b;
SET log_min_duration_statement TO DEFAULT;
SET track_stmt_stat_level TO DEFAULT;
select DBE_SQL_UTIL.drop_sql_bind('bind1');
select DBE_SQL_UTIL.drop_sql_bind('bind2');
select DBE_SQL_UTIL.drop_sql_bind('bind3');