Aurora PostgreSQLのクエリ実行計画管理機能を試してみた

一体いつからクエリ実行計画の管理がOracleだけのものだと錯覚していた?

岩田智哉

2019.08.30

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

CX事業本部@大阪の岩田です

たまにはAuroraでも触ってみようとAuroraについて調べていたところ、Aurora PostgreSQLにクエリの実行計画管理機能があるのを見つけました。クエリの実行計画管理といえばOracleのイメージだったのですが、Aurora PostgreSQLでもDBAが自由にクエリの実行計画を管理できるだと？！ということで簡単に触ってみました。

クエリ実行計画の管理とは？

Aurora PostgreSQLのクエリ実行計画管理=Query Plan Management(以後QPMと略します)は、クエリの実行計画をオプティマイザ任せにするのではなく、DBA側で管理するための機能です。

Aurora PostgreSQLが取得・蓄積した実行計画に対してDBA側で承認、拒否、無効化といった操作を行うことで、オプティマイザが選択する実行計画をコントロールすることが可能です。

やってみる

実際にQPMの機能を触ってみます。以後はAurora PostgreSQL (compatible with PostgreSQL 10.7)を利用して検証しています。

Auroraのインスタンス作成

まずAuroraのインスタンスを作成します。QPMを利用するためには、いくつかパラメータの設定が必要になるので新しくパラメータグループを作成します。

設定すべきパラメータは以下の通りです。

rds.enable_plan_managementを1に
apg_plan_mgmt.capture_plan_baselinesをautomaticに
apg_plan_mgmt.use_plan_baselinesをtrueに更新

apg_plan_mgmt...のパラメータは後ほど変更します。

作成したパラメータグループを使用してAurora PostgreSQLのインスタンスを作成します。

QPMの有効化

インスタンスが作成できたらQPMを有効化していきます。

まずはAuroraのバージョンを確認

template1=> select aurora_version(),version();
aurora_version | version
----------------+-----------------------------------------------------------------------------
2.3.3 | PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
(1 row)

Extension apg_plan_mgmtの作成

template1=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION

Extensionが作成されていることを確認

template1=> select extname,extversion from pg_extension where extname='apg_plan_mgmt';
extname | extversion
---------------+------------
apg_plan_mgmt | 1.0.1
(1 row)

パラメータが設定されていることを確認

template1=>show rds.enable_plan_management;
rds.enable_plan_management
----------------------------
1
(1 row)

template1=>show apg_plan_mgmt.capture_plan_baselines;
apg_plan_mgmt.capture_plan_baselines
--------------------------------------
automatic
(1 row)

template1=>show apg_plan_mgmt.use_plan_baselines;
apg_plan_mgmt.use_plan_baselines
----------------------------------
on
(1 row)

OKそうです。

テスト用DBの作成と初期化

実際にQPMの機能を試すためにテスト用のDBを作成し、pgbenchで適当なデータを突っ込みます。

template1=> CREATE DATABASE pgbench;
CREATE DATABASE

データベースpgbenchを初期化

$ pgbench -i -h <Auroraのエンドポイント> -U <適当なDBユーザー> pgbench
Password:
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

QPMを使ってみる

実際にQPMを使ってみます。

適当なSELECT文のEXPLAINを実行してみます。

EXPLAIN (hashes true)
SELECT Sum(delta),
           Sum(bbalance)
FROM   pgbench_history h,
          pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=1435.99..1436.00 rows=1 width=16)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=462.31..462.32 rows=1 width=8)
           ->  Seq Scan on pgbench_history mn  (cost=0.00..417.85 rows=17785 width=8)
   InitPlan 2 (returns $1)
     ->  Aggregate  (cost=462.31..462.32 rows=1 width=8)
           ->  Seq Scan on pgbench_history mx  (cost=0.00..417.85 rows=17785 width=8)
   ->  Nested Loop  (cost=0.00..510.90 rows=89 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..3.01 rows=1 width=8)
               Filter: (bid = ANY ('{1,2,3}'::integer[]))
         ->  Seq Scan on pgbench_history h  (cost=0.00..506.77 rows=89 width=8)
               Filter: ((mtime >= $0) AND (mtime <= $1))
 SQL Hash: 1561242727, Plan Hash: 368943769
(14 rows)

先ほどEXPLAINしたSQLの実行計画を確認してみましょう。 apg_plan_mgmt.dba_plansというビューから確認が可能です。 EXPLAINの実行結果に含まれるPlan HashをWHERE句に指定して問い合わせてみます。

SELECT
sql_hash,
plan_hash,
status,
estimated_total_cost "cost",
sql_text
FROM
apg_plan_mgmt.dba_plans
WHERE
plan_hash = 368943769;

sql_hash | plan_hash | status | cost | sql_text
------------+-----------+----------+------------+--------------------------------------------------------------------------------------
1561242727 | 368943769 | Approved | 1436.00125 | SELECT Sum(delta), +
| | | | Sum(bbalance) +
| | | | FROM pgbench_history h, +
| | | | pgbench_branches b +
| | | | WHERE b.bid = h.bid +
| | | | AND b.bid IN ( 1, 2, 3 ) +
| | | | AND mtime BETWEEN (SELECT Min(mtime) +
| | | | FROM pgbench_history mn) AND (SELECT Max(mtime) +
| | | | FROM pgbench_history mx);
(1 row)

実行計画の概要だけ抽出してみます。少しフォーマットは異なりますが、先ほどのEXPLAIN実行結果と同様の実行計画となっています。

SELECT
plan_outline
FROM
apg_plan_mgmt.dba_plans
WHERE
plan_hash = 368943769;

plan_outline
----------------------------------------
{ +
"Fmt": "01.00", +
"Outl": { +
"Op": "Agg", +
"QB": 1, +
"Plans": [ +
{ +
"Op": "NLJoin", +
"Jt": "IJ", +
"Plans": [ +
{ +
"Op": "SScan", +
"S": "public", +
"Tbl": "pgbench_branches",+
"Rid": 2 +
}, +
{ +
"Op": "SScan", +
"S": "public", +
"Tbl": "pgbench_history", +
"Rid": 1 +
} +
] +
}, +
{ +
"Op": "Agg", +
"QB": 2, +
"Plans": [ +
{ +
"Op": "SScan", +
"S": "public", +
"Tbl": "pgbench_history", +
"Rid": 1 +
} +
] +
}, +
{ +
"Op": "Agg", +
"QB": 3, +
"Plans": [ +
{ +
"Op": "SScan", +
"S": "public", +
"Tbl": "pgbench_history", +
"Rid": 1 +
} +
] +
} +
] +
} +
}
(1 row)

pgbench_historyテーブルのmtimeにインデックスを作成してみます。

pgbench=> create index pgbench_hist_mtime on pgbench_history(mtime);
CREATE INDEX

これでインデックスが使えるようになって、クエリが早くなるはず？！再度EXPLAINを実行してみましょう。

explain (hashes true)
SELECT Sum(delta),
           Sum(bbalance)
FROM   pgbench_history h,
          pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx);
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=1412.30..1412.31 rows=1 width=16)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=454.93..454.94 rows=1 width=8)
           ->  Seq Scan on pgbench_history mn  (cost=0.00..411.94 rows=17194 width=8)
   InitPlan 2 (returns $1)
     ->  Aggregate  (cost=454.93..454.94 rows=1 width=8)
           ->  Seq Scan on pgbench_history mx  (cost=0.00..411.94 rows=17194 width=8)
   ->  Nested Loop  (cost=0.00..502.00 rows=86 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..3.01 rows=1 width=8)
               Filter: (bid = ANY ('{1,2,3}'::integer[]))
         ->  Seq Scan on pgbench_history h  (cost=0.00..497.91 rows=86 width=8)
               Filter: ((mtime >= $0) AND (mtime <= $1))
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1561242727, Plan Hash: 368943769, Minimum Cost Plan Hash: -640924154
(15 rows)

インデックスを作成したものの、実行計画は先ほどと変わらずSeq Scanが利用されていることが分かります。よく見るとこんな出力が追加されています。

Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1561242727, Plan Hash: 368943769, Minimum Cost Plan Hash: -640924154

再度apg_plan_mgmt.dba_plansを確認してみます。

SELECT sql_hash,
        plan_hash,
       status,
       estimated_total_cost "cost",
       sql_text
FROM apg_plan_mgmt.dba_plans
WHERE sql_hash = 1561242727;


  sql_hash  | plan_hash  |   status   |       cost       |                                       sql_text
------------+------------+------------+------------------+--------------------------------------------------------------------------------------
 1561242727 | -640924154 | Unapproved | 19.5381695463635 | SELECT Sum(delta),                                                                  +
            |            |            |                  |            Sum(bbalance)                                                            +
            |            |            |                  | FROM   pgbench_history h,                                                           +
            |            |            |                  |           pgbench_branches b                                                        +
            |            |            |                  | WHERE  b.bid = h.bid                                                                +
            |            |            |                  |        AND b.bid IN ( 1, 2, 3 )                                                     +
            |            |            |                  |        AND mtime BETWEEN (SELECT Min(mtime)                                         +
            |            |            |                  |                           FROM   pgbench_history mn) AND (SELECT Max(mtime)         +
            |            |            |                  |                                                           FROM  pgbench_history mx);
 1561242727 |  368943769 | Approved   |       1436.00125 | SELECT Sum(delta),                                                                  +
            |            |            |                  |            Sum(bbalance)                                                            +
            |            |            |                  | FROM   pgbench_history h,                                                           +
            |            |            |                  |           pgbench_branches b                                                        +
            |            |            |                  | WHERE  b.bid = h.bid                                                                +
            |            |            |                  |        AND b.bid IN ( 1, 2, 3 )                                                     +
            |            |            |                  |        AND mtime BETWEEN (SELECT Min(mtime)                                         +
            |            |            |                  |                           FROM   pgbench_history mn) AND (SELECT Max(mtime)         +
            |            |            |                  |                                                           FROM  pgbench_history mx);
(2 rows)

status列がUnapproved(未承認)の実行計画が増えていることが分かります。

続いて未承認状態の実行計画を承認してみます。

SELECT apg_plan_mgmt.set_plan_status(sql_hash, plan_hash,'Approved')
FROM apg_plan_mgmt.dba_plans
WHERE plan_hash = -640924154;

set_plan_status
-----------------
0
(1 row)

再度apg_plan_mgmt.dba_plansを確認してみます。

 SELECT sql_hash,
        plan_hash,
       status,
       estimated_total_cost "cost",
       sql_text
FROM apg_plan_mgmt.dba_plans
WHERE sql_hash = 1561242727;
 

 sql_hash  | plan_hash  |  status  |    cost    |                                       sql_text
------------+------------+----------+------------+--------------------------------------------------------------------------------------
 1561242727 | -640924154 | Approved |      19.54 | SELECT Sum(delta),                                                                  +
            |            |          |            |            Sum(bbalance)                                                            +
            |            |          |            | FROM   pgbench_history h,                                                           +
            |            |          |            |           pgbench_branches b                                                        +
            |            |          |            | WHERE  b.bid = h.bid                                                                +
            |            |          |            |        AND b.bid IN ( 1, 2, 3 )                                                     +
            |            |          |            |        AND mtime BETWEEN (SELECT Min(mtime)                                         +
            |            |          |            |                           FROM   pgbench_history mn) AND (SELECT Max(mtime)         +
            |            |          |            |                                                           FROM  pgbench_history mx);
 1561242727 |  368943769 | Approved | 1436.00125 | SELECT Sum(delta),                                                                  +
            |            |          |            |            Sum(bbalance)                                                            +
            |            |          |            | FROM   pgbench_history h,                                                           +
            |            |          |            |           pgbench_branches b                                                        +
            |            |          |            | WHERE  b.bid = h.bid                                                                +
            |            |          |            |        AND b.bid IN ( 1, 2, 3 )                                                     +
            |            |          |            |        AND mtime BETWEEN (SELECT Min(mtime)                                         +
            |            |          |            |                           FROM   pgbench_history mn) AND (SELECT Max(mtime)         +
            |            |          |            |                                                           FROM  pgbench_history mx);
(2 rows)

plan_hash-640924154の実行計画がApproved(承認)に変わっていることが分かります。この状態で再度EXPLAINを実行してみましょう。

EXPLAIN (hashes true)
SELECT Sum(delta),
           Sum(bbalance)
FROM   pgbench_history h,
          pgbench_branches b
WHERE  b.bid = h.bid
       AND b.bid IN ( 1, 2, 3 )
       AND mtime BETWEEN (SELECT Min(mtime)
                          FROM   pgbench_history mn) AND (SELECT Max(mtime)
                                                          FROM  pgbench_history mx);

                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19.53..19.54 rows=1 width=16)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.33..0.34 rows=1 width=8)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.29..0.33 rows=1 width=8)
                   ->  Index Only Scan using pgbench_hist_mtime on pgbench_history mn  (cost=0.29..748.48 rows=17194 width=8)
                         Index Cond: (mtime IS NOT NULL)
   InitPlan 4 (returns $3)
     ->  Result  (cost=0.33..0.34 rows=1 width=8)
           InitPlan 3 (returns $2)
             ->  Limit  (cost=0.29..0.33 rows=1 width=8)
                   ->  Index Only Scan Backward using pgbench_hist_mtime on pgbench_history mx  (cost=0.29..748.48 rows=17194 width=8)
                         Index Cond: (mtime IS NOT NULL)
   ->  Nested Loop  (cost=0.29..18.42 rows=86 width=8)
         Join Filter: (h.bid = b.bid)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..3.01 rows=1 width=8)
               Filter: (bid = ANY ('{1,2,3}'::integer[]))
         ->  Index Scan using pgbench_hist_mtime on pgbench_history h  (cost=0.29..14.33 rows=86 width=8)
               Index Cond: ((mtime >= $1) AND (mtime <= $3))
 SQL Hash: 1561242727, Plan Hash: -640924154
(20 rows)

今度はインデックスを利用していることが分かります！

まとめ

オプティマイザが最適と判断する実行計画は統計情報の偏り等、さまざまな要因で変化し得ます。元々は高速に実行できていたクエリがある時を境に意図せぬ実行計画で実行されるようになり、パフォーマンストラブルを誘発した。という経験をお持ちの方も多いのではないでしょうか？オプティマイザはオプティマイザで色々と頑張ってはくれますが、常に100%最適な実行計画を選択してくれる訳ではありません。実行計画を安定させるためにQPM機能は良い選択肢になりそうです。

今回は簡単に触った程度ですが、時間ができたらもう少し色々と試してみようと思います。

Aurora PostgreSQLのクエリ実行計画管理機能を試してみた

目次

クエリ実行計画の管理とは？

やってみる

Auroraのインスタンス作成

QPMの有効化

テスト用DBの作成と初期化

QPMを使ってみる

まとめ

参考

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

Aurora PostgreSQLのクエリ実行計画管理機能を試してみた

目次

クエリ実行計画の管理とは？

やってみる

Auroraのインスタンス作成

QPMの有効化

テスト用DBの作成と初期化

QPMを使ってみる

まとめ

参考

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

関連記事

ElectricSQLでLocal-firstなアプリを作る

安定したAWSアカウントのデータベース利用費が2024年3月から増えていたらRDS延長サポートを要チェック!

小ネタ：runnでローカルに立てたPostgreSQLサーバーに接続するテストを行う

2024年3月から始まるサポート期間が伸びるけれども費用もかかるAmazon RDS延長サポートとの付き合い方~MySQL 5.7/PostgreSQL 11を例に~

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！