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

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

一体いつからクエリ実行計画の管理がOracleだけのものだと錯覚していた?
#PostgreSQL
#Amazon Aurora
#AWS
岩田智哉

岩田智哉

facebook logohatena logotwitter logo
Clock Icon2019.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...のパラメータは後ほど変更します。

enable_plan_managementの設定
capture_plan_baselinesの設定
use_plan_baselinesの設定

作成したパラメータグループを使用して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機能は良い選択肢になりそうです。

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

参考

Share this article

facebook logohatena logotwitter logo

関連記事

MySQLの暗黙的コミットについて調べて実際に試してみた

MySQLの暗黙的コミットについて調べて実際に試してみた

User avatar
sora
2024.10.29
テクサポと学ぶ PostgreSQL #5 psql

テクサポと学ぶ PostgreSQL #5 psql

User avatar
村上紗敏
2024.10.29
[アップデート] Amazon Aurora PostgreSQL でも遂にリードレプリカのローカル書き込み転送機能がサポートされました

[アップデート] Amazon Aurora PostgreSQL でも遂にリードレプリカのローカル書き込み転送機能がサポートされました

User avatar
いわさ
2024.10.18
EC2 Instance Connect Endpoint 経由で RDS for PostgreSQL に接続する構成を AWS CDK で構築してみた

EC2 Instance Connect Endpoint 経由で RDS for PostgreSQL に接続する構成を AWS CDK で構築してみた

User avatar
若槻龍太
2024.10.02
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.