15
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 10

NRI xPalette Advent Calendar 2025

@gen-suzukiin株式会社野村総合研究所

AWS Aurora PostgreSQLのスロークエリ分析をマスター！クエリエディタで実践

15
Last updated at Posted at 2025-12-09

はじめに

  • アプリケーションのログよりSQLが遅いところまで特定した後に、そのSQLがなぜ遅いのかを分析するために Amazon Aurora PostgreSQL で実行プランをログに出力する方法を紹介します。

  • Amazon Aurora PostgreSQLは既に準備できていて、ログ出力方法だけ知りたい方は「STEP2」にジャンプしてください。

本検証のために作成したリソースは、検証後に必ず削除してください。AWS利用料金が思わぬ金額になる可能性があります。

目的

  • AWS Aurora PostgreSQL 15.10 で auto_explain を用いて、ログに出力した実行プランをCloudWatchLogsから確認します。（おまけでスロークエリ解消までの手順に触れていますが、実行プランをログに出力して、確認できるようにすることがゴールです）

auto_explain
手動で EXPLAIN の実行をせずに自動的に遅いクエリの実行プランをログ記録する手段を提供します。

前提

  • RDBMS、PostgreSQLの基本的な知識はある前提で、実行プランが何かという説明はこの記事では触れません。
  • 実行プランのログ出力するための検証環境構築とログ出力手順の紹介なので、本番稼働を目的としたAWSリソース・データベースの設計・設定は本記事の対象外とします。
  • AWS初心者が最速かつ最低限の金額で検証を進めることを目標とするため、IaCは利用せずにAWSマネジメントコンソールのみで完結する手順とします。（DBへのSQL発行はクエリエディタを利用）

データベースエンジン

  • データベース：AWS Aurora PostgreSQL 15.10 (LTS)1
  • 拡張パッケージ：auto_explain

2025/12/10時点で長期サポート対象のPostgreSQL 15.10で検証を行います。

実行プランをログ出力するまでの流れ

ステップ 1：AWS Aurora PostgreSQL 環境の構築と接続準備（所要時間：15分）

1. Auroraデータベースの作成

データベースを作成するを選択した後、標準作成を選択したうえで、以下４か所を変更して、ページ最下段のデータベースの作成を押下します。（簡単に作成だとACUが変更できないので予期せぬ高額請求を防ぐために標準作成を選択）

  • 長期サポート対象の利用可能なバージョンAurora PostgreSQL (Compatible with PostgreSQL 15.10)を選択
  • コストを抑えるために容量の範囲（最大キャパシティ (ACU)）を 4 に設定
  • クエリディタを利用するためにRDS Data API の有効化 にチェック
  • CloudWatch Logsで参照するためにログのエクスポート（PostgreSQL ログ）にチェック

ご利用されているAWSアカウントでは、データベース作成時の初期選択設定が、本記事記載の設定と一致していないことがあります。

作成の詳細条件は添付画像の通り
image.png
image.png
image.png

2. Auroraクラスターの作成完了確認（所要時間：10分弱）

以下のようにAuroraクラスターとインスタンスの両方のステータスが利用可能になるまで待ちます。
image.png

3. AWSコンソールからの接続確認（クエリエディタの起動）

クエリエディタからデータベースにアクセスするためには、データベース名・データベースユーザ名・認証情報が必要になります。本記事では、データベース作成時に、認証情報管理で AWS Secrets Manager で管理を選択しているため、AWS Secrets ManagerにアクセスしてシークレットのARN2を控えます。
image.png
image.png

Tips：Amazon リソースネーム (ARN) は機密情報？
ARN自体は機密情報ではありません2が、そのARNが指すリソースに機密情報が含まれている可能性があるため、公開することはやめましょう。

Amazon リソースネーム (ARN) は、AWS リソースを一意に識別します。IAM ポリシー、Amazon Relational Database Service (Amazon RDS) タグ、API コールなど、すべての AWS 全体でリソースを明確に指定する必要がある場合は ARN が必要になります。ARN は、他の識別情報と同様に、慎重に使用および共有する必要がありますが、秘密情報、センシティブ情報、または機密情報とは見なされません。

再度Aurora and RDSのリソース画面でクエリエディタを選択し、データベース接続情報画面に必要情報を記入します。

  • データベースインスタンスまたはクラスター：database-1（デフォルト設定で作成している場合）
  • データベースユーザー名：Secrets Manager ARN と接続するをプルダウンから選択
  • Secrets manager ARN：前手順で控えたデータベースユーザー名のシークレットのARNを貼り付け
  • データベースの名前を入力：postgres（デフォルト設定で作成している場合）

https___qiita-image-store.s3.ap-northeast-1.amazonaws.com_0_355390_c0c1c894-cdc4-46ef-8653-c015345bbcc8.jpg

データベースに正常に接続されたポップアップを確認します。その後、エディタに初期入力されているクエリを変更せずにそのまま実行し、出力のステートメントのStatusがsuccessであることを確認できればOKです。
image.png

ついでに現時点で、この後設定する拡張機能auto_explainが有効でないことを確認します。

SHOW shared_preload_libraries;

image.png

ステップ 2：スロークエリのログ出力設定（所要時間：10分）

Aurora PostgreSQLを作成時点では、変更不可能なデフォルトのパラメータグループが適用されているため、カスタムパラメータグループの作成と適用が必要です。

1. Auroraのカスタムパラメーターグループの作成/変更

パラメータグループを選択し、パラメータグループの作成を押下します。※今回インスタンス用パラメータグループは利用しません。クラスター用パラメータグループのみ作成します。
image.png

  • クラスター用パラメータグループ
    • パラメータグループ名：custom-aurora-cluster-postgresql-15（任意）
    • 説明：Custom cluster parameter group for aurora-postgresql 15（任意）
    • エンジンのタイプ：Aurora PostgreSQL
    • パラメータグループファミリー：aurora-postgresql15
    • タイプ：DB Cluster Parameter Group

image.png

2. 重要なパラメーターの設定 3

  • shared_preload_libraries パラメータに auto_explain を追加し、設定を保存
  • auto_explain.log_min_duration を 0 msに設定し、設定を保存

auto_explain.log_min_duration
本記事では、検証目的ですべての実行計画を記録するために 0 msとしていますが、auto_explain.log_min_duration パラメータを 0 に設定すると、パフォーマンスが低下し、記憶域スペースが広範囲に消費されます。これにより、インスタンスに問題が発生する可能性があります。

  • auto_explain.log_analyze を 1に設定し、設定を保存

auto_explain.log_analyze
実際に費やされた時間 actual time をログ出力する目的で設定します。

3. 設定の適用

  • DBクラスターに先ほど作成したクラスター用パラメータグループを適用し、続行を押下
  • DBクラスターの変更画面のすぐに適用を選択し、クラスターの変更を押下
  • DBインスタンス（database-1-instance-1）を再起動
  • パラメータ反映確認
    image.png
    image.png
    image.png
    image.png
    image.png
    image.png 
SHOW shared_preload_libraries;
SHOW auto_explain.log_min_duration;
SHOW auto_explain.log_analyze;

image.png

ステップ 3：スロークエリの分析と改善（所要時間：15分）

1. 検証用データの準備

a. 100 万件規模のテスト用データを作成
b. purchase_history を作り、ランダムデータを投入

purchase_history テーブル
CREATE TABLE purchase_history (
    purchase_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    quantity INT NOT NULL,
    purchase_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

image.png

100万レコードを生成・挿入
INSERT INTO purchase_history (
    user_id,
    product_name,
    price,
    quantity
)
SELECT
    -- ユーザーIDを1から5,000の間でランダムに生成
    (RANDOM() * 4999 + 1)::INT,
    -- 商品名を100種類からランダムに生成
    'Product-' || LPAD(((RANDOM() * 99) + 1)::TEXT, 3, '0'),
    -- 単価をランダムに生成
    ROUND((RANDOM() * 4900 + 100)::NUMERIC, 2),
    -- 数量をランダムに生成
    (RANDOM() * 9 + 1)::INT
FROM
    GENERATE_SERIES(1, 1000000);

-- データ投入後、オプティマイザのために統計情報を更新
VACUUM ANALYZE purchase_history;

-- 投入結果の確認 (1,000,000件)
SELECT COUNT(*) FROM purchase_history;

image.png
image.png

2. スロークエリとなる（想定される）SQLを発行

purchase_history検索
SELECT 
    user_id,
    COUNT(purchase_id) AS total_purchases,
    SUM(price * quantity) AS total_sales_amount
FROM 
    purchase_history
GROUP BY 
    user_id
ORDER BY 
    total_sales_amount DESC
LIMIT 10;

image.png

3. CloudWatch Logs で auto_explain による実行プランのログ出力を確認

  • Cloud WatchLogsインサイトからpostgresqlのロググループを選択し、auto_explainによる実行プランのログが出力されていることを確認します。※auto_explainによる実行プランはplan:から始まる
fields @timestamp, @message
| filter @message like /plan:/
| sort @timestamp desc
| limit 100

image.png

実行プラン（auto_explain）
2025-11-01 07:31:59 UTC:[local]:postgres@postgres:[3420]:LOG:  duration: 537.934 ms  plan:
	Query Text: SELECT 
	    user_id,
	    COUNT(purchase_id) AS total_purchases,
	    SUM(price * quantity) AS total_sales_amount
	FROM 
	    purchase_history
	GROUP BY 
	    user_id
	ORDER BY 
	    total_sales_amount DESC
	LIMIT 10
	Limit  (cost=20042.59..20042.61 rows=10 width=44) (actual time=537.812..537.922 rows=10 loops=1)
	  ->  Sort  (cost=20042.59..20055.09 rows=5002 width=44) (actual time=537.811..537.919 rows=10 loops=1)
	        Sort Key: (sum((price * (quantity)::numeric))) DESC
	        Sort Method: top-N heapsort  Memory: 26kB
	        ->  Finalize HashAggregate  (cost=19871.97..19934.49 rows=5002 width=44) (actual time=535.060..537.033 rows=5000 loops=1)
	              Group Key: user_id
	              Batches: 1  Memory Usage: 3281kB
	              ->  Gather  (cost=18709.00..19771.93 rows=10004 width=44) (actual time=478.306..524.114 rows=15000 loops=1)
	                    Workers Planned: 2
	                    Workers Launched: 2
	                    ->  Partial HashAggregate  (cost=17709.00..17771.53 rows=5002 width=44) (actual time=466.902..469.714 rows=5000 loops=3)
	                          Group Key: user_id
	                          Batches: 1  Memory Usage: 2529kB
	                          Worker 0:  Batches: 1  Memory Usage: 2529kB
	                          Worker 1:  Batches: 1  Memory Usage: 2529kB
	                          ->  Parallel Seq Scan on purchase_history  (cost=0.00..12500.67 rows=416667 width=18) (actual time=0.006..99.242 rows=333333 loops=3)

Duration：537.934 msであり、auto_explain.log_min_duration:0 msを超えているため、実行プランが記録されていることを確認できました！！:tada:
auto_explain.log_analyzeにより計画ノードごとのactual timeが出力されていることも確認できます。

ステップ 4：検証用のリソース削除（所要時間：20分）

もう一度検証するかもしれませんが、すぐに作成できます。忘れないうちに不要なリソースは削除しておきましょう。

  • データベースインスタンス
  • データベースクラスター
  • CloudWatchLogsのPostgresSQLログ

まとめ

本記事では、実行プランの確認方法としてauto_explainを紹介しました。そのほかにも、apg_plan_mgmtによる実行プラン管理の方法もあります。用途に応じて使い分けてください。

  • auto_explain：実行プランをログに出力するのみで、実行プランの管理や変更はできない。
  • apg_plan_mgmt：実行プランの管理や変更（保存、承認、固定）ができる。

おまけ：スロークエリの解消

せっかくなので、このクエリをどのように高速化するかまで検証しました。

1. ボトルネックの改善策の検討

集計処理とワーカー結果の収集（GatherとPartial HashAggregate、約524msまで） に最も時間がかかっていることがわかります。集計に時間を要していたのであらかじめ集計したテーブル（マテリアライズドビュー）を作成しておくことで改善を図ります。

実行プラン（auto_explain）※マテリアライズドビュー無し
2025-11-01 07:31:59 UTC:[local]:postgres@postgres:[3420]:LOG:  duration: 537.934 ms  plan:
	Query Text: SELECT 
	    user_id,
	    COUNT(purchase_id) AS total_purchases,
	    SUM(price * quantity) AS total_sales_amount
	FROM 
	    purchase_history
	GROUP BY 
	    user_id
	ORDER BY 
	    total_sales_amount DESC
	LIMIT 10
	Limit  (cost=20042.59..20042.61 rows=10 width=44) (actual time=537.812..537.922 rows=10 loops=1)
	  ->  Sort  (cost=20042.59..20055.09 rows=5002 width=44) (actual time=537.811..537.919 rows=10 loops=1)
	        Sort Key: (sum((price * (quantity)::numeric))) DESC
	        Sort Method: top-N heapsort  Memory: 26kB
	        ->  Finalize HashAggregate  (cost=19871.97..19934.49 rows=5002 width=44) (actual time=535.060..537.033 rows=5000 loops=1)
	              Group Key: user_id
	              Batches: 1  Memory Usage: 3281kB
	              ->  Gather  (cost=18709.00..19771.93 rows=10004 width=44) (actual time=478.306..524.114 rows=15000 loops=1)
	                    Workers Planned: 2
	                    Workers Launched: 2
	                    ->  Partial HashAggregate  (cost=17709.00..17771.53 rows=5002 width=44) (actual time=466.902..469.714 rows=5000 loops=3)
	                          Group Key: user_id
	                          Batches: 1  Memory Usage: 2529kB
	                          Worker 0:  Batches: 1  Memory Usage: 2529kB
	                          Worker 1:  Batches: 1  Memory Usage: 2529kB
	                          ->  Parallel Seq Scan on purchase_history  (cost=0.00..12500.67 rows=416667 width=18) (actual time=0.006..99.242 rows=333333 loops=3)

2. ボトルネックの改善策の実行

  • マテリアライズドビューの作成をクエリエディタから実行
マテリアライズドビューの作成
CREATE MATERIALIZED VIEW user_sales_summary AS
SELECT
    user_id,
    COUNT(purchase_id) AS total_purchases,
    SUM(price * quantity) AS total_sales_amount
FROM
    purchase_history
GROUP BY
    user_id
WITH NO DATA; -- データは作成後、明示的にリフレッシュして投入します
マテリアライズドビューにデータ初期投入 (リフレッシュ)
REFRESH MATERIALIZED VIEW user_sales_summary;

3. 改善後の効果測定

user_sales_summary検索
SELECT
    user_id,
    total_purchases,
    total_sales_amount
FROM
    user_sales_summary
ORDER BY
    total_sales_amount DESC
LIMIT 10;
実行プラン（auto_explain）※マテリアライズドビュー有り
2025-11-01 07:58:26 UTC:[local]:postgres@postgres:[690]:LOG:  duration: 1.091 ms  plan:
	Query Text: SELECT
	    user_id,
	    total_purchases,
	    total_sales_amount
	FROM
	    user_sales_summary
	ORDER BY
	    total_sales_amount DESC
	LIMIT 10
	Limit  (cost=195.05..195.07 rows=10 width=20) (actual time=1.084..1.085 rows=10 loops=1)
	  ->  Sort  (cost=195.05..207.55 rows=5000 width=20) (actual time=1.083..1.084 rows=10 loops=1)
	        Sort Key: total_sales_amount DESC
	        Sort Method: top-N heapsort  Memory: 26kB
	        ->  Seq Scan on user_sales_summary  (cost=0.00..87.00 rows=5000 width=20) (actual time=0.005..0.337 rows=5000 loops=1)

以前のクエリ（537ミリ秒）と比較して、このマテリアライズドビューを使ったクエリは1ミリ秒で完了することができました。

項目 以前のクエリ (元テーブル/集計) 現在のクエリ (マテリアライズドビュー)
総実行時間 456.350 ms 1.091 ms
スキャン行数 約10万行 5,000行
主要なボトルネック HashAggregate (約430ms) 無し

  1. Aurora PostgreSQL 長期サポート (LTS) リリースの使用｜AWS Aurora のユーザーガイド ↩

  2. Amazon リソースネーム (ARN) で AWS リソースを識別する｜AWS Identity and Access Management ユーザーガイド ↩ ↩2

  3. Amazon RDS PostgreSQL または Aurora PostgreSQL のクエリの実行プランをログ記録して、クエリのパフォーマンスを調整する方法を教えてください。｜AWS re:Post knowledge Center ↩

15
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?