📖

re:Invent 2025: PostgreSQL実践チューニング - CPU100%から5つの最適化でクエリ数2倍改善

2025/12/03に公開

AWS

reinvent2025sessio

tech

はじめに

海外の様々な講演を日本語記事に書き起こすことで、隠れた良質な情報をもっと身近なものに。そんなコンセプトで進める本企画で今回取り上げるプレゼンテーションはこちら！

re:Invent 2025 の書き起こし記事については、こちらの Spreadsheet に情報をまとめています。合わせてご確認ください

📖 re:Invent 2025: AWS re:Invent 2025 - PostgreSQL performance: Real-world workload tuning (DAT410)

この動画では、AWS のシニアデータベースエンジニアである Baji Shaik と Vlad Vlasceanu が、PostgreSQL の実践的なパフォーマンスチューニング手法を実演しています。e コマースアプリケーションを題材に、CPU 使用率 100% の状態から 5 つの重大なパフォーマンス問題を段階的に解決していきます。具体的には、関数呼び出しでの SELECT サブクエリの活用、部分インデックスによる最適化、Aurora の Query Plan Management を使ったプラン切り替え、fill factor 調整による heap-only tuple updates の活用、そしてパーティション数削減による lock manager イベントの解消などが紹介されます。EXPLAIN ANALYZE の詳細な読み方や、pg_stat_activity、Performance Insights を使った監視手法も実践的に解説され、最終的にクエリ数を 12,000 から 24,000 へと 100% 以上改善する過程が示されています。

※ こちらは既存の講演の内容を最大限維持しつつ自動生成した記事になります。誤字脱字や誤った内容が記載される可能性がありますのでご留意下さい。

本編

PostgreSQLパフォーマンスチューニングの概要とJohnの課題

みなさん、こんにちは。DAT 410: PostgreSQL Performance—Real-world Workload Tuning へようこそ。ちょっとお聞きしたいのですが、朝の3時にデータベースのパフォーマンス低下によるアラームを受け取ったことはありますか？CPU使用率が高かったり、クエリの実行時間が長かったり、クエリプランが切り替わったりすることがあるかもしれません。もしそうなら、今日は私たちのほとんどが直面する一般的なパフォーマンスの課題をいくつか見て、一緒に解決していきます。私の名前は Baji Shaik で、RDS と Aurora PostgreSQL データベースのシニアデータベースエンジニアです。ここに共演者がいます。みなさん、こんにちは。Vlad Vlasceanu です。AWS のデータベースのシニアプリンシパルデータベース SA です。Baji をサポートするためにここにいます。ご質問がある場合は、手を挙げてください。マイクを持ってお伺いしますので、みんなが聞こえるように質問していただき、その後、できる限りの知識でご質問にお答えするようにします。

では始めましょう。コンテンツを始める前に、ロードジェネレータを起動したいと思います。このロードジェネレータについて説明するスライドがあります。ロードジェネレータを起動しました。では、John さんに会いましょう。彼は AnyCompany のシニアデータベースエンジニアです。彼の会社の PostgreSQL データベースの管理を担当しています。PostgreSQL データベースのインストールとセットアップを非常に上手くこなし、チューニングも非常に良くしていて、すべてのモニタリングとすべてのアラームをセットアップしました。しかし、結局のところデータは増え続け、高いクエリ実行時間と高い CPU 使用率により、朝の3時にアラームが出始めました。これは聞き覚えがありませんか？では、John の PostgreSQL パフォーマンスの問題を一緒に解決しましょう。

その前に、パフォーマンスチューニングに焦点を当てるべき主要な領域を見てみましょう。まず CPU 使用率から始めます。これは最適でないクエリが原因である可能性があり、フルテーブルスキャンを行うことになり、これは CPU 集約的です。アプリケーションのワークロードがアンダーサイズされている可能性があります。例えば、高い CPU 使用率が見られる場合です。デフォルトでは、PostgreSQL は並列クエリを使用します。したがって、多数の並列クエリがある場合、より多くの接続が CPU を使用することになります。これが高い CPU 使用率が見られる場所です。

次に見るべきことはメモリです。繰り返しになりますが、最適でないクエリとメモリ集約的なクエリが要因です。PostgreSQL はプロセスベースのアーキテクチャを使用しているため、各接続はプロセスであり、いくらかのメモリを消費します。接続数が多い場合、高いメモリ使用率が見られます。PostgreSQL にはメモリ関連のパラメータがあり、これに基づいてメモリを制御します。しかし、これらのパラメータが過剰に設定されている場合、高いメモリ使用率が見られるのはそこです。

次に見るべきことはストレージと IOPS です。PostgreSQL はマルチバージョン同時実行制御メカニズムを使用しています。したがって、データベースへの変更はすべて、行の古いバージョンと新しいバージョンの2つのバージョンを持つことになります。行の古いバージョンは dead tuples と呼ばれ、VACUUM などのメンテナンスアクティビティによってクリーンアップされます。そうしないと、高いストレージと IOPS 使用率が見られます。未使用または重複したインデックスが多い場合、データベースへの変更のたびにこれらのインデックスが不必要に更新され、ストレージと IOPS 使用率が見られるのはそこです。PostgreSQL は work memory を使用してソートなどのクエリ操作を制御します。しかし、そのメモリが不足している場合、ディスク上に一時ファイルが作成され、高い IOPS アクティビティが発生します。

次はアプリケーションパターンです。大量のクエリが互いにブロックし合っている場合、これはクエリの実行が遅いのではなく、クエリ同士が競合することでデータベースのパフォーマンスが低下します。長時間実行されるトランザクションや、長期間アイドル状態のトランザクションがあると、メンテナンスをブロックしてしまい、最終的にデータベースが遅くなります。また、アイドル接続が多数ある場合、何もしていなくてもリソースを消費します。こういった場合は、コネクションプーラーが接続を最適化するのに役立ちます。これらが注目すべき重要な領域です。

クエリチューニング方法論とEXPLAIN ANALYZEプランの基礎

次に、クエリチューニング方法論について見てみましょう。これはステップバイステップのプロセスです。まず Database Insights や Performance Insights の Active Session Summary を確認するか、データベース内の pg_stat_activity ビューを見ることから始めます。リソースを消費している上位の SQL と上位の待機イベントを確認します。次に、BUFFERS オプション付きで EXPLAIN ANALYZE プランを生成します。これにより、共有バッファ情報も確認できます。そこから調査を進めます。これはステップバイステップの反復的なプロセスです。上位の SQL を特定して、そこから修正を始める必要があります。

EXPLAIN ANALYZE プランはどのように見えるのでしょうか？これはシンプルなプランです。矢印で示された各マークはプランノードを表し、最上行はすべてのプランノードの統合です。最初の行と各プランノードを見ると、それぞれ 2 つのセクションに分かれています。最初の部分は推定値で、次の部分は実際の値です。数字を見てみましょう。最初の数字は推定スタートアップコスト、次に総推定コスト、次に推定行数、そして行の平均幅です。次のセクションは実際の値で、ミリ秒単位の実際のスタートアップ時間から始まり、次にミリ秒単位の総時間、そしてそのクエリの総行数です。各プランノードにはこれら 2 つのセクションがあります。

各プランノードの 2 番目の部分を見ると、それはそのプランノードの実行時間で、統合は最上行です。最上行にはそのクエリで実行された行数があり、次はループ数です。複雑なクエリの場合、ループ数が多くなります。これが EXPLAIN ANALYZE プランの基本的な基礎です。EXPLAIN ANALYZE プランで何を見るべきでしょうか？デモでは詳細に見るべき問題がいくつかありますが、高レベルでは、まず悪い推定値から始めます。プランナーは統計に依存しています。統計が最新でない場合、悪いプランが生成され、実行時間が長くなります。シーケンシャルスキャンやフルテーブルスキャンを見ることができます。これらは CPU 集約的で、インデックスが役に立ちます。ただし、インデックスが既にある場合は、戦略的なインデックスがパフォーマンスの向上に役立ちます。また、EXPLAIN ANALYZE プランの BUFFERS オプションでバッファレートを確認することもできます。前述の通り、各プランノードを見て、そのプランノード内の遅い操作を特定し、そこから調査を始める必要があります。

これは e コマースアプリケーションです。シンプルな e コマースアプリがあり、ロードジェネレーターを起動しました。このアプリには、orders、 products、users といったシンプルな e コマーステーブルがあり、ロードジェネレーターがあります。1150 を超える接続でデータベースへの接続を開き、バランスの取れた読み取りと書き込みがあり、30 分ごとに実行されたクエリ数の概要を提供します。デモでそれが見られます。これが対応する 5 つの重大なパフォーマンス問題です。デモで詳細に対応しますが、高レベルでは、最初のものはクエリの書き直しがどのように役立つかです。2 番目は、クエリが異なるプランに進む場合ですが、別のプランが役に立つ場合です。プラン間でどのように切り替えますか？3 番目は、既にインデックスがある場合、戦略的なインデックスがどのようにパフォーマンスの向上に役立つかです。4 番目は heap-only tuple updates です。これがパフォーマンスの向上にどのように役立つかです。そして、デモで見られるいくつかの軽量ロックがあり、それらのロックは何で、どのように修正できるかです。

関数呼び出しの最適化：SELECTサブクエリによるインデックススキャンの実現

では、デモから始めましょう。デモに戻します。これが起動させたロードジェネレータです。8つのクエリが実行されていて、各クエリが何回実行されたか、そしてクエリの集計情報が表示されています。この30秒間のインターバルで、クエリを修正していくにつれて、これらのクエリ数の改善が見られます。コンソールに戻ると、ここが Performance Insights に移動できるインスタンスです。過去10分間を選択すると、異なるウェイトイベントが表示され、CPU が最も上位のウェイトイベントになっています。60～70以上のセッションが CPU を消費しているのが見えますが、多くのウェイトイベントがあります。Database Insights に移動します。これは Performance Insights の拡張版で、そこから監視を続けます。

過去10分間を選択すると、CPU が最も上位である同じウェイトイベントが表示されます。では、ターミナルに戻ります。データベースに接続しましょう。pg_stat_activity についても話していました。pg_stat_activity ビューをクエリして、pg_stat_activity ビューから maybe 20文字のクエリの部分文字列を選択し、データベース名が e-commerce であるものを抽出できます。このようにして、データベースへのすべての接続が見られます。150以上の接続があります。これらはすべてデータベースに対して実行されています。すべての insert、select、update、その他のクエリを見ると、データベースへの読み書きのバランスが取れていることがわかります。コンソールに戻って、最も重要な CPU ウェイトイベントを選択しましょう。スクロールダウンすると、CPU を消費している上位のクエリが表示されます。今日は、上位5つのクエリを1つずつ修正していきます。

監視するもう1つのことは、データベースのテレメトリセクションです。CPU、メモリ、I/O、その他のすべての監視に関する個別のメトリクスがあります。メトリクスと CloudWatch を組み合わせると、これらのメトリクスを監視するための独自のダッシュボードを作成できます。CPU、読み取りオペレーション、書き込みオペレーション、その他の基本的なメトリクスを含む簡単なダッシュボードを作成しました。コミットレイテンシなどもあります。過去15分間の CPU を見ると、100パーセントに達しています。では、Database Insights に戻って、これらの上位クエリを取り出し、1つずつ修正していきましょう。過去10分間を選択してから、CPU ウェイトイベントを選択します。スクロールダウンすると、クエリが表示されます。これは select 関数呼び出しで、より多くの CPU リソースを消費しています。そのクエリを確認すると、これがこのデータベースに対して実行されているクエリです。このクエリとこの関数の DDL を見て、内部に何があるかを確認しましょう。

述語として関数呼び出しを持つ単一の select クエリのみがあります。ですから、この関数を実行すると、sale_movement_type がこのクエリに来て、全体的な関数はすべての sales movement type のカウントを取得します。しかし、このクエリに対して EXPLAIN ANALYZE を実行すると、時間がかかります。数分前に、時間を節約するためにこの関数に対して EXPLAIN ANALYZE を実行しました。1分以上かかるためです。BUFFERS 付きで EXPLAIN ANALYZE を関数呼び出しに対して実行すると、EXPLAIN ANALYZE で関数に対して直接多くの情報が表示されません。このため、PL profiler 拡張機能を使用できます。これは関数の内部クエリの詳細を提供します。または、関数を確認して、その内部のクエリに対して手動で EXPLAIN ANALYZE を実行できます。クエリが1つだけなので、そのクエリに対して EXPLAIN ANALYZE を実行しました。このEXPLAIN ANALYZE プランを深く見るのに数分かけて、他のクエリについてはより少ない時間を費やします。

これが EXPLAIN ANALYZE プランです。私が話した矢印マークはこのプランノードです。クエリが1つ、テーブルが1つ、単純なストレートクエリなので、プランノードが1つあります。そして、最上行はすべてのプランノードの集計です。では、最初にすることは、どのプランノードが遅いかを判断することです。プランノードが1つだけなので、プランノードを見ると、推定コストは300万以上で、行数はほぼ200万です。実際の合計時間は約60秒で、このプランノードが返す行数は100万だけです。もう1つか2つ見るべきことは、フィルタで削除された行が1000万行ということです。しかし、このプランノードが返しているのは100万行だけであることに注意してください。

この矛盾が生じているのは、同じテーブルの行数を見ると1100万行あって、movement typeで売上をグループ化すると、これが100万行なんです。つまり、この売上のmovement typeがここに書かれているものです。もしその売上を除外すると、1000万行になって、これがフィルターで削除されるところです。

今何が起きているかというと、各行に対してこの関数呼び出しが実行されるんです。だからこそ1100万行すべてに対して実行されるわけです。その後、plannerが売上のmovement typeは100万行だけだと気づいて、処理された1000万行のすべての行を削除する必要があるんです。これを見ると、plannerが1100万行を処理するために実行するのは不要な作業なんです。shared hitを見ると、これらはブロック単位です。つまり87,008 KBのブロックがメモリから取得されています。これは多いです。87に8 KBを掛けると、800メガバイト程度になるので、それくらいのデータが読み込まれているということです。

今、このplanned nodeを見ると、sequential scanに行っていて、これはfull table scanです。でも、このテーブルのDDLを見ると、バックスラッシュdオプションで、テーブル構造が見えます。もう一度接続し直して、このテーブルのDDLを見ます。これはmovement typeのインデックスです。つまり、movement_typeカラムにインデックスがあるのに、このクエリではsequential scanに行っているんです。これはplannerがこの関数から来ている値を知らないからです。この関数をselectコールで実行すると、saleが返ってきますが、plannerはそこから何の値が来るのか知らないんです。だから各行に対してその関数を実行しているわけです。

optimizerに売上のmovement typeを認識させるために、クエリを変更すれば、ここからこのクエリを取ってexplain analyze buffersをします。その関数に対してselectコールを使えば、関数名に戻ると、その関数にselectコールを追加して何が起きるか見ると、saleがそのフィルターカラムに来ることを知っているので、インデックスを拾うはずです。初回は時間がかかりますが、見ると、index scanに行っていて、これが私たちが望んでいたものです。つまり、selectコールはインデックススキャンに行くべきだというインデックスヒントを与えるんです。

IOタイミングを見ると、これは初回でインデックスをメモリに読み込む必要があるからです。だからIOタイミングが見えるんです。でも、もう一度実行すると、既にメモリにあるので、以前は10秒かかっていたのに、今は800ミリ秒になりました。でも、最初はほぼ1分でした。つまり、実行時間を1分から800ミリ秒に短縮したんです。それだけじゃなく、ここではrows removed by filterが見えません。つまり、plannerは正確に売上のmovement typeの行に行くんです。shared buffer hitsを見ると、87,008 KBのブロックでしたが、今は2,008 KBのブロックだけで、plannerの作業がずっと少なくなっています。だから実行時間が遅いんです。でも、すべてのメトリクスを見ると、コストは以前は300万でしたが、443,000になったので、すべてが低下しました。

このクエリを修正するには、このクエリを select call を使うように書き直すだけでいいんです。やってみます。その関数を select call を使うように修正しているところです。そして今、その関数の定義の詳細を見ると、select call が追加されているのが分かります。ですから、この修正の後に来るすべての接続は index scan に行くはずです。load generator に戻ると、30秒ごとの関数呼び出しはたった9クエリです。監視を続けていれば、次のサマリーが表示されるのを待てば、その関数呼び出しの改善も見ることができます。関数呼び出しが12クエリに改善されました。数分待てば、実際の改善が見えるでしょう。もう一つやっていることは、コンソールに戻って他の統計情報を見ることです。ダッシュボードに行ってスクロールダウンすれば、 commit latency メトリクスを選びます。過去15分間で選択すると、ゆっくり下がってきているのが見えます。トランザクションあたり約23マイクロ秒だったのが、今は21に下がってきています。ですから、これらの関数呼び出しの commit latency が削減され、他のクエリにもより多くのリソースを提供します。他のクエリのクエリ数にも少し改善が見えます。この関数呼び出しが消費していたリソースが今は少なくなったからです。

queries finished のような他のメトリクスも見ることができます。このメトリクスにしましょう。CloudWatch に行ってすべてのメトリクスを選択解除して、queries finished だけを選択すると、これも時間とともに改善されるはずです。クエリの数が改善されているのが見えます。total query time を見ると、これは削減されるはずです。500万で始まったのが、今は525万になっています。他のクエリも実行されているので、他のクエリのためにリソースが生まれました。でも最終的には、下がります。これがクエリを書き直してパフォーマンスを改善する方法です。これはクエリを書き直す例です。関数呼び出しを述語として使っている場合は、必ず SELECT サブクエリを使ってください。そうしないと、オプティマイザーが混乱して、index scan の代わりに sequential full table scan を実行してしまいます。

部分インデックスの活用：アクティブ製品のフィルタリング最適化

Database Insights に戻ると、これを常にやらない理由があるんですか？それとも、なぜ常に SELECT を前に付けないんですか？関数を直接呼び出すためです。質問を理解していれば、なぜ SELECT サブクエリを追加する必要があるのか？なぜそれを常にやらないんですか？これが存在することさえ知りませんでした。書いたすべてのクエリのことを考えています。これをどこでもやるべきだったんです。これをやりたくない理由があるんですか？

質問を言い換えてみます。データベースエンジンが自動的にそれを SELECT サブクエリとして選ばないのはなぜかということですね。はい、理解しました。つまり、オプティマイザーがそれを自動的に SELECT サブクエリとして選ばないのはなぜかということですね。そうですね、それはオプティマイザーの仕組みです。SELECT サブクエリがなければ、関数呼び出しだけを見ます。その場合、フィルタータイプの値を取得するためにその呼び出しを実行する必要があります。でも SELECT サブクエリがあれば、それはサブクエリなので、すでに最初にその呼び出しを実行します。実際のフィルターではなく、サブクエリなんです。ですから、サブクエリを最初に実行してから、その値をさらに行に与えます。

次のクエリを見てみましょう。この3番目のクエリを最初に修正します。何か質問ありますか？はい、この例についても質問があるんですが、その前に。さっき EXPLAIN プランを実行したときに、修正をテストする前に2番目のものを実行しましたか？これは I/O のタイミングのせいです。最初の実行時はインデックスをメモリに読み込む必要があるので、余分な行はすべて I/O に関連していて、この I/O タイミングの行を取得します。ここにあります。EXPLAIN に何かパラメータを追加する必要があったんですか、それともこれは通常の EXPLAIN ですか？これは通常の EXPLAIN です。

それが違いですね。ありがとうございます。それが知りたかったことです。それからもう1つ、PostgreSQL のコマンドラインでやってることはすべて、EXPLAIN PLAN とか backslash D 関数とか、そういったことは AWS コンソールでもできたり、やりたいと思ったりするんですか？AWS コンソールではなく、PGAdmin とか他のクライアントツールみたいなクライアントツールなら可能です。そういったクライアントツールを使ってテーブルの詳細やインデックス、関数など、すべてを確認できます。もう1つ簡単なフォローアップなんですが、ここで見せてくれたものと Aurora PostgreSQL を使ってる場合で大きな違いはありますか？これは Aurora PostgreSQL です。申し訳ありません、まだ言及していませんでした。コンソールに戻ると、これが私が見始めた Aurora インスタンスです。EXPLAIN プランはそれの標準なので、どの PostgreSQL フレーバーのデータベースでも使用できます。RDS Aurora コミュニティ PostgreSQL の場合、特に特別なことはありません。

では、私が選びたい3番目のクエリを見てみましょう。クエリを見ると、特定のカテゴリの製品の平均価格を取得していて、アクティブな製品のみです。このクエリを選択して EXPLAIN ANALYZE プランを生成すると、インデックススキャン、具体的には Bitmap インデックススキャンに進みます。通常のインデックススキャンと Bitmap インデックススキャンの違いは、インデックススキャンの場合、1行を取得してインデックスファイルから取得するので、1行に対して1回の反復があります。しかし Bitmap インデックススキャンの場合、複数の行を取得してすべての行を一度に取得するので、インデックスファイルへの反復回数が減ります。だから Bitmap インデックスは場合によっては通常のインデックスより効率的です。

ですから Bitmap インデックススキャンに進んでいて、前のクエリで見た2つのことを、ここでも再び見ています。30,000行がフィルタで削除され、13,000個の 8 KB ブロックが取得されているのが見えます。クエリを見ると、特定のカテゴリの平均価格を取得していて、アクティブ、つまりアクティブな製品のみです。しかし products テーブルからアクティブとインアクティブの両方の製品を見ると、アクティブな製品は33パーセントだけです。ですから常にアクティブな製品を選択していて、インアクティブな製品は選択していないことがわかっているなら、同じカテゴリ列に部分インデックスを作成できますが、アクティブな製品のみです。このインデックスを作成してみましょう。すべてのクエリが並行して実行されているので CONCURRENTLY オプションを使用しています。ですから IDX for category and only active on the products テーブルの category 列に active equals true です。これが作成したい部分インデックスです。このインデックスを作成して EXPLAIN プランを実行し、違いを見てみましょう。

今度は新しいインデックスに対して Bitmap インデックススキャンに進みます。これら2つのメトリクスを見ると、フィルタで削除された行は30,000だったのに対して900だけになっています。実行時間は360ミリ秒で、今は23ミリ秒です。バッファは13,000だったのに対して10,000だけです。ですから実行する作業が少なくなっています。インデックスを作成したので、ロードジェネレータに戻ります。見てみると、関数呼び出しは最初のクエリを修正する前は912だったのに、今は最終的に418クエリがその関数呼び出しに対して実行されているのが見えます。ですから最初のクエリのそのインデックススキャンのおかげで大幅な改善があります。では status インデックスを見てみましょう。クエリの数は1,200、1,200です。

このインデックスを作成した後、1200 から 2000 への改善が見られます。これはほぼ 100% の改善です。クエリの集計は、いくつかのクエリを修正した後 20,000 になりましたが、最初に始めたときは 15,000 でした。つまり、同じワークロードで同じインスタンス上で 5,000 個のクエリが多く実行されているということです。インスタンスレベルでは何も変更していません。クエリを修正するだけで、パフォーマンスが 15,000 から 20,000 クエリに改善されました。では、ここに戻って、次に修正するクエリに進みましょう。

すべての列に個別にインデックスを作成する代わりに、複合インデックスを使用することはできませんか？複合インデックスは役に立たなかったのでしょうか？質問を正しく理解していれば、category と is_active に複合インデックスを作成できない理由は何ですか？理由は、異なるステータスに対して多くのインデックスを作成することができないからです。その通りです。しかし、複合インデックスを作成した場合、is_active には非アクティブな製品もあります。私たちは常にアクティブな製品を検索するため、アクティブな製品のみをフィルタリングしたいのです。複合インデックスを使用する場合、category には多くの非アクティブな製品が含まれている可能性があるため、フィルタによって削除される行数が多くなります。複合インデックスは正しい選択ではないかもしれません。部分インデックスがうまく機能するでしょう。だから私たちはアクティブな製品に特に焦点を当てたインデックスを作成したのです。

インデックススキャンは述語の左端の列から発生すると長い間考えていました。今、私たちは中間の何かを選んでいます。これは本番環境にロールアウトする前にベンチマークすべき何かですか？これはデータの成長のためにも起こります。最初はアクティブな製品が製品テーブルの 90% しかなく、category 列インデックスがうまく機能していたとしましょう。やがてデータが成長し、一部の製品がアクティブになったり非アクティブになったりします。今、非アクティブな量がテーブルの 66% になっているので、そこで変化が見られます。しかし、最初にベンチマークしていれば、最高のパフォーマンスが得られていたでしょう。

その質問に基づいて、作成するインデックスが多いほど、本質的に定期的に監視、観察、最適化する必要があることを示しています。それがおそらく全体的な計画だと思います。なぜなら、そうしないと、すべての列にインデックスを作成してしまい、事前に計画することができないからです。作成するインデックスが多いほど、テーブルの変更のパフォーマンスが低下します。その部分インデックスを作成した場合、category の最初のインデックスを監視し続けて、そのインデックスがこのインデックス以外で使用されていないかどうかを確認できます。その特定のテーブルとその特定のインデックスのインデックススキャンを監視し続けます。そのインデックスが使用されていないことを観察した場合、そのインデックスを削除できます。今後、未使用のインデックスを削除する方法もありますが、それは良い質問です。

最後の例を見たとき、そのクエリに何か問題があったという兆候は何でしたか？また、インデックスがそれを修正することをどのようにして知りましたか？テーブル内の非アクティブな製品とアクティブな製品を見ました。そのため、テーブルの 30% だけがアクティブであることを知っています。赤信号は、フィルタによって削除される行が多いことでした。クエリプランナーは非アクティブな製品でも動作し、その後、すべての非アクティブな行を削除したことに気付きました。フィルタによって削除される行の数を少なくしたいのです。なぜなら、それはより多くの作業をしているからです。それは毎回そうですね。そこにヒントがあります。では、このクエリに進みましょう。このクエリを選択した場合、このクエリを観察すると、見えるかどうか確認できません。このクエリを EXPLAIN ANALYZE プランで表示するだけです。

Aurora Query Plan Managementによるプラン切り替えとヒントの問題解決

EXPLAIN ANALYZE プランでこのクエリを見せてみましょう。まず、EXPLAIN ANALYZE で実行してみます。では、このクエリを観察してみましょう。ヒントが付いていますね。これは Oracle で使うようなものと似ています。これは pg_hint_plan という拡張機能を使って実現できます。私は既に pg_hint_plan を作成していて、アプリケーションの初期段階で、このクエリに対してこの特定のインデックスを使うというヒントを提供しました。なぜなら、そのインデックスがクエリに対して良いパフォーマンスを発揮していたからです。この特定のインデックスにヒントを付けたので、ご覧の通り、その特定のインデックスを使ったインデックススキャンが行われています。ここまでは良いのですが、また赤信号が出ています。フィルタで削除された行数が 400,000 で、フェッチされたバッファが 400,000 個の 8 KB ブロックです。でも、なぜオプティマイザはこんな悪いインデックスに行ったのでしょうか？それはあなたがヒントを付けたからです。述語を見てみると、total_amount に対して作成されていますね。ここが複合インデックスの出番です。orders テーブルの詳細を見てみると、私は既にその複合インデックスを持っています。このインデックスを見ると、私は既にそれを持っていますが、ヒントを付けたので単一列インデックスに行ってしまいます。簡単な解決策は、そのヒントを削除することです。そうすればオプティマイザは十分に賢いので、正しいインデックスを選択します。ヒントを削除して何が起こるか見てみましょう。その複合インデックスを選択するはずです。素晴らしい。その複合インデックスを作成しに行きました。行の削除を見てみると、フィルタで削除された行がないので、正確に何行を見るべきかを知っています。共有バッファのヒット数は 400,000 から 37,000 に減りました。これは大幅な削減です。これで解決策がわかりました。簡単な解決策はそのヒントを削除することで、うまく機能しますが、アプリケーションの変更が必要です。そのクエリはいろいろな場所にあるでしょうし、ヒントを削除することは短期的な解決策ではありません。長期的な解決策として削除を計画するかもしれません。しかし、短期的な解決策として、クエリを変更せずに別のインデックスを使うようにするにはどうしたらいいでしょうか？ここで Aurora の Query Plan Management 拡張機能が役に立ちます。この拡張機能を使うと、正しいプランをキャプチャして、クエリ間でプランを切り替えることができます。これは APG Plan Management 拡張機能を使って実現できます。この拡張機能を作成すると、DBA plans テーブルが得られ、データベースにヒットしたすべてのプランをキャプチャします。このプランを作成してそのクエリを実行すると、拡張機能を削除して再作成するだけです。拡張機能についてあなたに質問があります。現在のところ、Aurora 製品チームは拡張機能が前方互換性を持つことを保証していません。これはロードマップで変わるのでしょうか？例えば、この拡張機能に依存し始めて、製品が進化していく中でそれがもはやサポートされなくなったら、私たちは難しい状況に陥る可能性があります。あなたはそれを支持していますか、それとも製品チームと協力して、私たちが選ぶ拡張機能が前方互換性を持つようにするにはどうしたらいいでしょうか？正直なところ、私たちはサービスチームと協力して、これらの機能が存在し、より互換性が高くなるようにしています。しかし、まだそれを保証できるという段階には至っていません。一般的には、そうなることを期待できると思いますが、まだそれを保証として作ることはできません。時間が経つにつれて、これらの拡張機能がより多くの成熟度を得て、私たちが快適に感じるレベルに達すれば、そのような保証ができるようになるでしょう。しかし、今のところはまだそこまで至っていません。それでも、これはツールです。保証ではありませんが、このツールは現在 AWS によってサポートされているので、必要であれば、ぜひ使ってください。

拡張機能を作成してしばらく前にそれを削除したので、インスタンスを再起動する必要がありました。データベースの再起動が必要です。ちょうど再起動したところです。

これは Aurora だけで利用可能ですか、それとも RDS でも利用可能ですか？これは Aurora 専用です。では、DBA plans テーブルを見てみると、実行されているすべてのクエリのプランをキャプチャしました。すべての insert、update、select、すべてです。私たちが取り組んでいる特定のクエリについてクエリすると、それを作成したもので、物事を再起動しています。拡張機能を作成して削除すると、再起動が必要です。はい、このプランはここでキャプチャされていますが、可視性と明確な可視性のために、JSON 関数を使ってそのクエリで使用されているインデックスを表示するクエリを持っています。DPA plans にありますが、簡単なクエリです。このクエリを実行すると、このクエリのプランが承認されたステータスで表示され、これはプランハッシュで、これが SQL ハッシュです。このクエリのプランは既にキャプチャされています。

それはインデックスを作成しに行きます。では、ヒントを削除してクエリを実行し、プランを再度キャプチャしましょう。では、このクエリを実行すると、ノートに承認されたプランが取られたと書かれています。なぜなら、それは持っていますが、最小コストプランの代わりに。このクエリプランは既にキャプチャされていますが、Aurora は承認されたプランがあることを知っているので、それに行くべきです。それでも作成されたインデックスプラン、古いプランに行きました。しかし、新しいプランをキャプチャしました。では、この DBA plans テーブルを見て、作成されたもう 1 つのプランがあります。 amount テーブル、複合インデックス。これは未承認です。これを未承認にして、これを拒否して、これを承認すれば、新しいプランに行くはずです。これがプランを切り替える方法です。

その前に、Aurora stat plans 関数があります。これはライブクエリで使用されているプランを表示します。クエリを実行していて、どのプランが使用されているのか混乱している場合は、Aurora stat plans 関数を使って、正確にどのプランが使用されているかを確認できます。私は単に my queries タブに行き、これは Aurora stat plans 関数に対する簡単なクエリで、その特定のクエリをフィルタリングしています。これを実行すると、インデックスはヒントクエリができます。これは orders created at 単一列インデックスに行きます。新しいプランを承認していないので、まだそうです。呼び出し数を見ると、1800、同じクエリを再度実行すると、今は 2000 です。これはこれらのクエリが来ていて、まだ古いプランを取っているからです。だから、このプランの呼び出し数が増加しているのを見ているわけです。

では、これらのプランをどのように切り替えるのかというと、Aurora のプラン管理には set plan status という関数が付いています。この set plan status 関数を使うことで、特定のプランを拒否したり承認したりできます。必要な入力は SQL hash とプランだけです。私はすでにこの関数を使って2つのステートメントを作成しました。新しいプランと古いプランの SQL hash を使ってです。新しいプランを承認するために実行するだけで、申し訳ありません、ウィンドウを切り替えるのに少し時間がかかりますが、プランを承認して拒否して、これに戻ると、composite index が作成されたプランが承認されて、古いプランは拒否されています。想定としては、新しいプランと composite index を使うべきです。どのように確認するのかというと、

Aurora の query plan view をその特定のクエリに対して実行できます。結果を見ると、2行あります。古い index が作成されたものは 2,493 回のコールを示しています。新しい composite index では、1,200 回のコールになっています。もう一度実行すると、この数字は変わりません。古いプランを使わなくなったからです。これは私たちが続けていく中で増え続けます。では、このクエリを修正したので、ここに戻りましょう。 Plan instability のクエリは 255 から 260 の周辺でした。では、plan instability のクエリは 800 になっています。255 から 800 に上がっています。そして、クエリの総数は 24,000 に増えています。以前は 16,000 でした。 20,000 の周辺で 20,000 に達しました。2つのクエリを修正して、クエリ数は 24,000 に増えています。

では、クエリを書き直す方法を見てきました。最初のアプローチは query rewriting です。2番目は、インデックスがあるけれど、strategic index または partial index を作成してパフォーマンスを向上させることです。3番目は、すでに正しいインデックスが作成されているけれど、プランを切り替えて正しいインデックスを使うようにするにはどうするかということです。次に見るべきことは Claudia のメトリクスです。update クエリがより多くのリソースを消費しています。この update クエリを見ると、products テーブルの notes カラムを更新しています。このproducts テーブルの DDL とそのインデックスを見ると、 notes カラムにはインデックスがありません。PostgreSQL には heap-only tuples という機能があります。データブロック内のインデックスされていないカラムを更新していて、その中に空きスペースがある場合、インデックスを更新する代わりに、これはインデックスされていないカラムなので、そのデータブロック内にポインタを作成するだけです。インデックスを更新する必要がないので、その作業の反復が減ります。これを heap-only tuples と呼びますが、データブロック内に空きスペースが必要です。

これは PostgreSQL の fill factor で定義されます。このテーブルの fill factor を見ると、100 パーセントです。つまり、ブロック全体に書き込んで、スペースを残さないということです。これが fill factor 100 パーセントの意味です。では、heap-only tuples にはスペースが必要です。そのスペースは vacuum によって空きスペースが作成されるか、または特定のパーセンテージが空いている必要があります。ちょっと質問があります。同じクラスター内の writer インスタンスと reader インスタンスで、クエリプランは同じになるのでしょうか？同じでない場合、それぞれに対して最適化するために、どこで異なるのかを確認できるのでしょうか？

質問を理解しましたか？画面に表示されているクエリプランについての質問は、複数の reader または writer と reader のクラスターがある場合、その変更を1回だけ行う必要があるのか、それとも各インスタンスで行う必要があるのかということですね。writer インスタンスで変更を行う必要があります。そうすると reader インスタンスが更新されます。変更は writer から伝播します。プラン変更が伝播します。read-only ワークロードになります。reader と writer の間で異なる必要がある場合はありますか？私は認識していません。見たことがありません。同じクエリであれば、同じインデックスである必要があります。それがより良いパフォーマンスをもたらすからです。

つまり、クエリの結果は、レプリケーションラグの影響を受けることはありますが、どこで実行しようとも決定論的になるということです。ただし、クエリに少しでも変更があると、SQL ハッシュが変わってしまい、そのクエリに対して機能しなくなります。

ですから、あなたが言及したのは、ライターインスタンスで行ったクエリ最適化が自動的にリーダーインスタンスに伝播するということですね。ただし、これは双方向ではありません。リーダーインスタンスで最適化したものはフィードバックされません。なぜなら、リーダーインスタンスはデータウェアハウスのようなダウンストリームシステムに多くの読み取りを処理することが多いからです。リーダーインスタンスは常にリソース集約的な性質を持っているため、リーダーインスタンスで多くの最適化を行うことになります。リーダーインスタンス用の具体的な最適化とはどのようなものですか？クエリプランについてはどうですか？ライターインスタンスでも同様の最適化を行いますが、これらのクエリはリーダーインスタンスにヒットしています。それは同じ質問ですね。双方向ではないという性質です。ただし、リーダーインスタンスで異なるプランが必要な場合、それは同じ質問です。つまり、同じクエリであれば、同じプランでなければなりません。ライターとリーダーで異なるプランが必要な同じクエリのケースは聞いたことがありません。そのようなケースは聞いたことがありません。構造的には、データは物理的にストレージ内で同じ方法でレイアウトされています。同じストレージなので、異なるインスタンスで同じクエリが異なるパフォーマンス最適化を必要とする理由はありません。問題は、そのクエリがダウンストリームシステムにフィードしているため、リーダーインスタンスでのみ実行され、ライターでは実行されないということかもしれませんが、それはライターのパフォーマンスに影響を与えません。ただし、その最適化は本質的に価値があります。なぜなら、そのクエリは決定論的であり、同じ入力データが与えられれば常に同じ応答を生成するからです。

Heap-Only Tuple (HOT) Updatesとfillfactorの最適化

HOT アップデートに戻ると、各ブロック内のインデックスポインタ用に空き領域を保持するために fillfactor を削減する必要があります。これは、このコマンドを使用してその特定のテーブルの fillfactor を変更することで実行できます：set fillfactor to 80%。fillfactor を変更しましたが、すべてのブロックが既に満杯なため、これは即座には効果がありません。各ブロックに空き領域を残すためにブロックを再編成する必要があります。これは VACUUM FULL で実行できます。 VACUUM FULL はアクセス排他ロックを取得するため、VACUUM FULL の時間を改善するために接続を削除する必要があります。接続を削除するだけで、ロードジェネレータはクエリを削除すると再起動するのに十分なスマートさを持っています。PG_STAT_ACTIVITY を使用して、データベース名が e-commerce で、PID が現在作業中の PID に含まれていない場合、その PID が削除されると、セッションを進めることができません。そこで、products テーブルで VACUUM FULL ANALYZE を実行します。その VACUUM FULL の実行には数秒、おそらく 5 ～ 6 秒かかります。その VACUUM FULL の後、これで HOT アップデート用に 20% の領域が確保されます。ロードジェネレータに戻って、変更されたかどうかを確認しましょう。テーブル DDL は fillfactor が 80% であることを示しています。アップデートが HOT に向かっているかどうかは、PG_STAT_USER_TABLES を使用して確認できます。PG_STAT_USER_TABLES には N_TUP_HOT_UPD という列があります。この列は、その特定のテーブルの HOT アップデート数を示しています。パーセンテージを取得しましょう。クエリに移動します。これは HOT アップデートクエリです。アップデート数、HOT アップデート数、およびその特定のテーブルのパーセンテージを選択しています。ここでこの単純な select を実行すると、70% のタプルが HOT アップデートに向かっています。観察を続けると、パフォーマンス比率が増加するのが見えます。72、72.1 というように増加し続けます。つまり、テーブルへの HOT アップデートがより多くなっています。ロードジェネレータに戻ると、 2,300 の HOT アップデートがあります。以前は 3,800 でしたが、インスタンスを再起動したため、ピックアップに時間がかかります。最終的にはより多くの HOT アップデート数が見られるようになります。

これが HOT アップデートが役立つところです。インデックス化されていない列のアップデートがある場合、各データブロックに空き領域を作成して、インデックスを更新する代わりにそれらのポインタを持つことができます。これで、ほぼ 4,000 のクエリが見えます。以前は約 2,000 ～ 3,000 だったので、これは大きな改善です。

これに戻ると、5 番目のクエリはこの関数呼び出しです。元々、その列にインデックスがないと言うことから始まったため、基本的にテーブル内のスペースを更新することになっていました。その後、改善方法は fillfactor を 100 から 80 に削減することだと言いました。長期的には、その列にインデックスを作成することと fillfactor を削減することのどちらが良いでしょうか？

インデックスがそのカラムにないからではないんです。PostgreSQL は DML に対して、非インデックスカラムを更新しているかどうかに関わらず、すべてのインデックスを更新する必要があるんです。それらのインデックスはまだ更新される必要があります。もし私がそのカラムにインデックスを持っていなかったとしても、そのカラムにインデックスは必要ないんですが、それでもすべてのインデックスを不必要に更新するために進めているんです。しかし、それらのインデックスはこれらの更新を認識する必要があります。

では、どうすればいいのか。ここでこの機能が役に立つんです。インデックスを更新する代わりに、ポインタを作成することができます。少なくともインデックスを更新する時間を削減することができます。これらのポインタは同じブロック内に存在する必要があります。新しいブロックに置くことはできません。だからこそ、そのブロック内にこれらのポインタを更新するためのスペースが必要なんです。

もう一つの欠点は、そのブロック内に空きスペースがあると、インサートがより多くのブロックを必要とするということです。データを更新するためにオペレーティングシステムにブロックを与えてもらうことを要求するのは、またオーバーヘッドなんです。ですから、テーブルに対してより多くのアップデートがあり、インサートが少ない場合は、そのスペースを残すために fill factor を使用することができます。そうでなければ、各ブロック内にそのスペースを残しているため、インサートはより多くのブロックを必要とします。

パーティションテーブルのロック管理と未使用インデックスの削除

この関数定義を見ると、これは order_items カラムに対する select です。これは単純な select ですが、order_items テーブルを見ると、これは日次パーティションを持つパーティション化されたテーブルです。731 個のパーティションが見えます。では、WHERE 句がパーティションキーにある場合、partition pruning に進むことを知っています。しかし、その前に、オプティマイザは最初に各パーティションのロックを取得する必要があり、その後、データの正確なパーティションをプルーニングします。しかし、それでもすべてのパーティションをロックする必要があります。

PostgreSQL はデフォルトで non-fast path と fast path のロッキングを許可しています。Fast path ロッキングはロック取得のオーバーヘッドを削減します。デフォルトでは、各クエリに対して 16 個の fast path ロックのみを取得できます。では、700 個のパーティションを持つパーティション化されたテーブルがある場合、700 個の異なる独立したテーブルなので、そのテーブルに対して 700 個のロックを取得する必要があります。明らかに、non-fast path ロッキングに進みます。これはいくらかのオーバーヘッドです。だからこそ、このクエリに対して特に lock manager イベントが見えるんです。

lock manager イベントは、クエリが非 fast path ロックの数が多い場合に発生します。確認するために、この関数を実行してみましょう。関数から select star を実行して、そのクエリのロック数を確認します。データベースに再接続します。pg_locks からロック数を select count します。約 1,300 個のロックを取得しました。しかし fast path ロックを除くと、まだ 800 個の非 fast path ロックがあります。これは各パーティションに対してロックを取得したためです。

これらの他のアイテム用に order_items_new という新しいテーブルがあります。これは日次パーティショニングではなく月次パーティショニングが付属しています。これは元の order_items パーティションテーブルと同じ量のデータを持っています。しかし、パーティション数が大幅に少なくなっています。この関数をパーティション数が少ないパーティションテーブルを使用するように更新して、トランザクション内でこの関数を select すると、非 fast path ログを確認できます。これらは大幅に削減されています。ロックのオーバーヘッドはすべて排除されました。観察を続けると、lock manager イベントは表示されないか、ここではほんの少ししか表示されません。このロックのオーバーヘッドはクエリのパフォーマンスに影響を与えており、この問題は修正されました。5 つのクエリを修正しました。そして、インデックスに関して最後に 1 つ見せたいことがあります。

未使用または重複したインデックスがある場合、データベースは不必要にそれらのインデックスにデータを更新または挿入する必要があり、これは重い作業です。このクエリは未使用のインデックスをチェックします。これは私が自分で作成したクエリではなく、Wiki で見つけることができます。このクエリを実行してデータベース内の未使用のインデックスを見つけると、多くの未使用のインデックスと重複したインデックスもあることがわかります。開発インスタンスでは、インデックスの使用状況をチェックし続けて、どんどんインデックスを作成してから、重複したものを削除するのを忘れることがあります。それが重複したインデックスの出所です。重複したインデックスをチェックすると、14 個の重複したインデックスがあります。それだけでなく、これらのインデックスは 5 から 6 ギガバイトのサイズで、不要なストレージです。これらのインデックスを削除してパフォーマンスの改善を確認できます。

インデックスの削除にはアクティブな接続がある場合、時間がかかるため、接続を削除しています。この drop index コマンドを実行すると、drop index コマンドのセットが得られます。重複したインデックスについても同様です。14 個の重複したインデックスがあります。ここで、すべての接続を終了しているクエリに戻り、重複コマンドを実行すると、実行するだけで、すべての drop コマンドが実行されます。重複したインデックスについても同じことが当てはまります。実行するだけです。これで、すべてのインデックスが削除され、すべての未使用および重複したインデックスが削除されました。ロードジェネレータがすべてのクエリを再開したため、観察を続けることができます。これらのクエリを観察して改善を確認できます。最初は約 12,000 から 14,000 クエリでしたが、5 つのクエリを修正した後、24,000 クエリになっています。このチューニングで 100 パーセント以上の改善です。

レポートの生成には時間がかかりますが、その間に質問があればお答えします。HOT updates を実行して変更を加えたときに、コードは何も変更しなかったということを確認したいのですが、正しいですか？スペアスペースがあったからですか？テーブルの fill factor を変更しただけです。わかりました、ありがとうございます。2 番目の質問は、PostgreSQL または Aurora PostgreSQL のクエリプランを統合するための開発について何かご存知ですか？たとえば、特定のコメント付きのクエリがあるが、残りのテキストが同じ場合、異なるハッシュが生成されます。またはスペースやキャリッジリターンがある場合、同じクエリ、同じ数のパラメータですが、スペーシングが少し異なると、異なるクエリハッシュになります。機能的に同じクエリをそれらを 1 つのハッシュに統合したり、何らかのマッピングを持つための取り組みについてご存知ですか？

正直なところ、私はそこまで詳しくないんですが、もしヒントがあれば、それを削除して特定のクエリに対する SQL ハッシュを生成します。あるいは、select の前に説明があれば、それらを削除して SQL ハッシュを生成するんです。ただし、スペースや改行があると、別の SQL ハッシュになってしまいます。これを改善するために開発されているものについては、私は認識していません。

これは機能リクエストとしてサービスチームに報告して、対応できるかどうか見てもらうことができます。このエクステンション全体は私たちだけが利用できるもので、私たちのエクステンションなので、対応することは可能なはずです。顧客のニーズに基づいて作業を優先順位付けするだけの問題です。このリクエストについては、確実にサービスチームに知らせることができます。これで認識できたので、このサービスチームへの機能リクエストを提出することができます。では、皆さんありがとうございました。Baji、素晴らしかったです。ご参加いただきありがとうございました。

※ こちらの記事は Amazon Bedrock を利用し、元動画の情報をできる限り維持しつつ自動で作成しています。