(8.0.22)
調べた限りなし
(19c)
適応問合せ計画(Adaptive Plans)
デフォルトで有効
show parameter OPTIMIZER_ADAPTIVE_PLANS;
適応計画は次の最適化を制御します
・ネステッド・ループおよびハッシュ結合選択
・スター型変換ビットマップ・プルーニング
・適応パラレル配分方法
-- テストデータ作成
drop table tab1 purge;
drop table tab2 purge;
drop table tab3 purge;
drop table tab4 purge;
create table tab1(col1 int, col2 int, col3 int);
create table tab2(col1 int, col2 int, col3 int);
create table tab3(col1 int, col2 int, col3 int);
create table tab4(col1 int, col2 int, col3 int);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);
alter table tab3 add constraint tab3pk primary key(col1);
alter table tab4 add constraint tab4pk primary key(col1);
declare
begin
for i in 1..100000 loop
insert into tab1 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab2 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab3 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
insert into tab4 values(i,floor(dbms_random.value(1, 10001)),floor(dbms_random.value(1, 101)) );
commit;
end loop;
end;
/
select count(*) from tab1;
select count(*) from tab2;
select count(*) from tab3;
select count(*) from tab4;
drop index ind11;
drop index ind12;
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);
exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');
exec dbms_stats.gather_table_stats('TEST','TAB3');
exec dbms_stats.gather_table_stats('TEST','TAB4');
-- 動作確認
show parameter OPTIMIZER_ADAPTIVE_PLANS;
alter session set OPTIMIZER_ADAPTIVE_PLANS = true;
alter session set OPTIMIZER_ADAPTIVE_PLANS = false;
explain plan for
with tab0 as (
select t2.col1 col1 ,t2.col2 col2 ,t3.col2 col3 ,t4.col2 col4,count(*) col5
from tab2 t2
inner join tab3 t3
on t2.col1 = t3.col1
left outer join tab4 t4
on t2.col1 = t4.col1
group by t2.col1 ,t2.col2 ,t3.col2 ,t4.col2
)
select
t1.col1 ,t0.col2 ,t0.col3 ,t0.col4 ,t0.col5
from tab1 t1
inner join tab0 t0
on t1.col1 = t0.col1
where t1.col2 = 1
;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT => 'ADAPTIVE'));
(13)
Adaptive query optimization
https://github.com/postgrespro/aqo
前提：ソースからコンパイル済
yum install git patch
su - postgres
cd /usr/local/src/postgresql-13.2
git clone https://github.com/postgrespro/aqo.git contrib/aqo
patch -p1 --no-backup-if-mismatch < contrib/aqo/aqo_pg13.patch
make clean
make
sudo make install
cd contrib/aqo
make
sudo make install
make check
CREATE EXTENSION aqo;
vim postgresql.conf
shared_preload_libraries = 'aqo'
pg_ctl -w stop
pg_ctl -w start
BEGIN;
SET aqo.mode = 'learn';
EXPLAIN ANALYZE select * from tab1 where col1 = 125;
RESET aqo.mode;
EXPLAIN ANALYZE select * from tab1 where col1 = 125;
EXPLAIN ANALYZE select * from tab1 where col1 = 125;
COMMIT;
select * from aqo_query_texts;
select * from aqo_queries;
select * from aqo_query_stat;
select * from aqo_data;
show aqo.mode;
set aqo.show_details = 'on';
set aqo.show_hash = 'on';
adaptive join
デフォルトで有効
-- 動作条件
①The database compatibility level is 140 or higher.
②The query is a SELECT statement (data modification statements are currently ineligible).
③The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
④The Hash join uses Batch mode, enabled through the presence of a Columnstore index in the query overall, a Columnstore indexed table being referenced directly by the join, or through the use of the Batch mode on rowstore.
⑤The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).
-- 無効にする場合(データベースレベル)
alter database scoped configuration set BATCH_MODE_ADAPTIVE_JOINS = off;
-- 設定確認
select * from sys.database_scoped_configurations;
-- テストデータ作成
drop table tab1;
drop table tab2;
create table tab1(col1 int not null, col2 int, col3 int);
create table tab2(col1 int not null, col2 int, col3 int);
alter table tab1 add constraint tab1pk primary key(col1);
alter table tab2 add constraint tab2pk primary key(col1);
set nocount on
declare @i int;
set @i = 1;
while (@i <= 10000)
begin
insert into tab1 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
insert into tab2 values(@i,floor(rand() * 10000)+1,floor(rand() * 100)+1);
set @i = @i + 1;
end
select count(*) from tab1;
select count(*) from tab2;
create nonclustered columnstore index ind11 on tab1(col1,col2,col3);
create nonclustered index ind21 on tab2(col1,col2,col3);
create nonclustered index ind22 on tab2(col2);
update statistics tab1;
update statistics tab2;
-- 動作確認
select t1.col1 col11,t1.col2 col12,t2.col1 col21, t2.col2 col22
from tab1 t1
inner join tab2 t2
on t1.col2 = t2.col2
where t1.col3 > 100
and t2.col2 < 100
;
