Adaptive Plan Selection ​

Overview ​

Adaptive plan selection applies to scenarios where a general cache plan is used for plan execution. Cache plan exploration is performed by using range linear expansion, and plan selection is performed by using range coverage matching. Adaptive plan selection makes up for the performance problem caused by the traditional single cache plan that cannot change according to the query condition parameter, and avoids frequent calling of query optimization.

Prerequisites ​

The database is running properly. The GUC parameter enable_cachedplan_mgr is set to on, indicating that the adaptive plan selection function is enabled.

Usage Guide ​

On the live network, use hints to enable the plan adaptation management capability for queries with cache plan problems.

select /*+ choose_adaptive_gplan */ * from tab where c1 = xxx;

By default, the JDBC client converts the preceding SQL statements with hints to the PBE model and creates a query template. In addition to directly modifying SQL statements, hints can be added through SQL patches.

In the gsql environment, you can manually create a query template.

prepare test_stmt as select /*+ choose_adaptive_gplan */ * from tab where c1 = $1;

Best Practice ​

Adaptive selection of multiple indexes is supported. The following is an example:

create table t1(c1 int, c2 int, c3 int, c4 varchar(32), c5 text);
create index t1_idx2 on t1(c1,c2,c3,c4);
create index t1_idx1 on t1(c1,c2,c3);

insert into t1( c1, c2, c3, c4, c5) SELECT (random()*(2*10^9))::integer , (random()*(2*10^9))::integer,  (random()*(2*10^9))::integer, (random()*(2*10^9))::integer,  repeat('abc', i%10) ::text from generate_series(1,1000000) i;
insert into t1( c1, c2, c3, c4, c5) SELECT (random()*1)::integer, (random()*1)::integer, (random()*1)::integer, (random()*(2*10^9))::integer, repeat('abc', i%10) ::text from generate_series(1,1000000) i;

Performance comparison:

Random parameters: c1~ random(1, 20); c2~ random(1, 20); c3~ random(1, 20); c4 ~ random(2, 10000)

The number of threads is 50, the number of clients is 50, and the execution duration is 60s.

Troubleshooting ​

For complex slow queries, this feature may not be able to correctly select a plan due to feature range restrictions. You are advised to use CPLAN to generate a query plan.