{Aurora}Aurora PostgreSQL のクエリ実行計画の管理

クエリプラン管理は、これらの Aurora PostgreSQL-Compatible Edition バージョンの
Aurora PostgreSQL 12.4、
Aurora PostgreSQL 11.6、
Aurora PostgreSQL 10.5、
Aurora PostgreSQL 9.6.11 以降のリリースで使用できます。

※デフォルトのパラメータグループで下記警告発生し、クエリ計画が登録できない
WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.

→max_worker_processesが1になっていた。8に増やしてエラー解消

-- 1. テスト用クラスタ作成
aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-postgresql12 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters ParameterName=rds.enable_plan_management,ParameterValue=1,ApplyMethod=pending-reboot

aws rds create-db-parameter-group \
--db-parameter-group-family aurora-postgresql12 \
--db-parameter-group-name pg01 \
--description pg01

aws rds modify-db-parameter-group \
--db-parameter-group-name pg01 \
--parameters ParameterName=max_worker_processes,ParameterValue=8,ApplyMethod=pending-reboot

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora-postgresql \
--engine-version 12.6 \
--master-username postgres \
--master-user-password 'password' \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.t3.medium \
--engine aurora-postgresql \
--no-auto-minor-version-upgrade \
--db-parameter-group-name pg01

-- 2. PostgreSQL 拡張機能をインストール

CREATE EXTENSION apg_plan_mgmt;
\dx

show rds.enable_plan_management;
show apg_plan_mgmt.use_plan_baselines;
show apg_plan_mgmt.capture_plan_baselines;

-- 3. テストデータ作成
drop table tab1;
create table tab1 (col1 int primary key,col2 int);
insert into tab1(col1,col2) select g,g from generate_series(1,1000000) g;
analyze tab1;

-- 4. 手動計画取り込みの実行
インデックス追加前と追加後の2種類の実行計画を取得する

SET apg_plan_mgmt.capture_plan_baselines = manual;

explain select * from tab1 where col2 = 1;
create index ind1 on tab1(col2);
explain select * from tab1 where col2 = 1;

SET apg_plan_mgmt.capture_plan_baselines = off;

-- 5. 取得された計画の表示

SELECT sql_hash, plan_hash, status, enabled, plan_outline, sql_text::varchar(40)
FROM apg_plan_mgmt.dba_plans
ORDER BY sql_text, plan_created;

-- 6. 管理計画の使用

SET apg_plan_mgmt.use_plan_baselines = true;

-- 7. 計画の拒否または無効化

select apg_plan_mgmt.set_plan_enabled( 574551574, -867144674, true);
select apg_plan_mgmt.set_plan_enabled( 574551574, -867144674, false);

select apg_plan_mgmt.set_plan_status( 574551574, -1646716698, 'Preferred');
select apg_plan_mgmt.set_plan_status( 574551574, -1646716698, 'Approved');
select apg_plan_mgmt.set_plan_status( 574551574, -1646716698, 'Rejected');
select apg_plan_mgmt.set_plan_status( 574551574, -1646716698, 'Unapproved');

explain select * from tab1 where col2 = 1;

-- 8. 計画の検証
利用不可能な計画を削除、または無効にする

drop index ind1;

select apg_plan_mgmt.validate_plans( 574551574, -1646716698, 'delete');

-- 9. 計画の削除

select apg_plan_mgmt.delete_plan( 574551574, -867144674);

-- 10. クリーンアップ

-- Auroraクラスタ削除

aws rds delete-db-instance \
--db-instance-identifier cluster11-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster11 \
--skip-final-snapshot

-- クラスタパラメータグループ削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name cpg01

-- パラメータグループ削除
aws rds delete-db-parameter-group --db-parameter-group-name pg01