ユースケース開発概要トラブルシューティングPostgres のクエリを最適化する

注: 以下の翻訳の正確性は検証されていません。AIPを利用して英語版の原文から機械的に翻訳されたものです。

Postgres のクエリを最適化する

このページでは、Postgres でクエリのパフォーマンスを最適化するためのチューニングに焦点を当てています。一般的には、データの読み書きにはオブジェクトセットや Actions のようなオントロジー機能を利用して、Slate アプリケーションを構築することをお勧めします。

応答性の高いアプリケーションを設計するには、慎重な計画と考慮が必要です。データモデル、クエリ構造、依存関係グラフは、それぞれアプリケーションのパフォーマンスと使いやすさに重要な役割を果たします。このガイドでは、Slate で記述できる PostgreSQL クエリのパフォーマンスチューニングの側面に焦点を当てており、パフォーマンスのヒューリスティックス、チューニングの機会を特定する方法、パフォーマンスを向上させるための提案を含んでいます。

クエリのパフォーマンスチューニングは反復的なプロセスです。同じ要求を「正しい」答えにするために、多くの異なる方法で書くことができます。

パフォーマンスヒューリスティックス

PostgreSQL クエリの目標実行時間は <= 500ms です。これは、対象となるデータセットが 10m レコード未満の場合、ほとんどのユースケースで可能ですが、データモデルの設計時には慎重な計画と考慮が必要です。

特に新しいデータセットでクエリパフォーマンスを評価する際は、統計を収集して分析する前にクエリを複数回実行することを確認してください。これにより、データセットが適切にキャッシュされ、パフォーマンスが予想される使用状況により一致するようになります。PostgreSQL のキャッシュについて詳しく知りたい場合は、以下のリンクを参照してください：

EXPLAIN... explained

EXPLAIN は、Postgres が提供するクエリ実行計画を返す特に有用なコマンドです。Postgres が受け取るすべての要求に対してクエリプランが作成され、クエリ構造とデータの特性を使用して要求を最速で処理する方法を決定します。このガイド全体で参照されるため、最初に EXPLAIN コマンドを簡単にレビューします。

EXPLAIN

ノード

ノードは作業の論理単位、つまりクエリ評価のステップと考えることができます。ノードは逆グラフとして返され、応答の最初の行が最後の作業単位を示します。各ノードは -> で始まります。

-> Index Scan using event_type_idx on event (...)

なぜこれが重要なのか: ノードはクエリ実行計画における非効率な操作を特定し、パフォーマンスチューニングの優先順位を付けるのに役立ちます。

コスト

最初の数値はスタートアップコスト（最初のレコードを取得する時間）です。2番目の数値はスタートアップから完了までのノード全体を処理するコストです。

(**cost=86.83..4577.07** rows=2368 width=10)

コストは、Postgres クエリプランナーがオブジェクト（一般的にはテーブル）統計に基づいて生成する推定値です。この数値は実際の実行時間を示すものではありませんが、実行時間と直接相関するはずです。

コストは、順次フェッチ、非順次（ランダム）フェッチ、行の処理、演算子（関数）の処理、インデックスエントリの処理など、いくつかの作業コンポーネントの組み合わせです。このコストは I/O および CPU 活動を表し、数値が大きいほど、Postgres がタスクを完了するために必要な作業量が多いことを示します。Postgres クエリオプティマイザはコストに基づいてどの実行プランを使用するかを決定します。

行

このプランノードが出力する予定の行数の推定値です。

(cost=86.83..4577.07 **rows=2368** width=10)

なぜこれが重要なのか: ROWS は、大量のデータを返しているか、期待通りに動作していないノードを特定するのに役立ちます。

幅

このノードが出力する行の推定平均サイズ（バイト単位）です。

(cost=86.83..4577.07 rows=2368 **width=10**)

なぜこれが重要なのか: 幅は、非常に大きなプロパティを持つ行や、多数の列を出力するノードを特定するのに役立ちます。

EXPLAIN ANALYZE

ノード

上記を参照。

実際の時間

コスト と同様に、最初の数値はスタートアップに必要な実際の時間（ミリ秒単位）です。2番目の数値はスタートアップから完了までのノード全体を処理する実際の時間です。

(**actual time=10.313..12.530** rows=4857 loops=1)

名前が示すように、実際の時間はステートメントの実行によって取得されます。キーワード ANALYZE は、実行プランを表示するのと同時にクエリを実行するよう Postgres に指示します。クエリのタイムアウトに悩んでいる場合、ANALYZE を削除するとクエリプランだけが返され、クエリを実行するよりもかなり高速になります。

なぜこれが重要なのか: これは、パフォーマンスの問題を引き起こしているノードや操作を特定する最も明確な指標です。

行

このノードが出力する予定の行数の推定値です。

(actual time=10.313..12.530 **rows=4857** loops=1)

なぜこれが重要なのか: ROWS は、特定の操作が予想以上に時間がかかる理由についてのコンテキストを提供するのに役立ちます。

ループ

ノードの実行回数の合計を報告します。表示される実際の時間および行の値は、実行ごとの平均値です。ループ値に実際の時間を掛けると、ノードに費やされた総時間が得られます。

(actual time=10.313..12.530 rows=150 **loops=10**)

操作（プランノード）の理解

スキャン

順次スキャン (seq scan): Seq Scan 操作は、ディスクに格納されているリレーション（テーブル）全体をスキャンします（TABLE ACCESS FULL のように）。順次スキャンはリレーションのスキーマ、サイズ、制約、インデックスの有無に関係なく、常に可能です。
- 順次スキャンの特徴は以下の通りです：
  - 起動が速い（順次 I/O はランダムアクセスよりもはるかに速い）。
  - 各ブロックは一度だけ読み取られる。
  - 無秩序な出力を生成する。
インデックススキャン: Index Scan は B ツリーの走査を実行し、リーフノードを通じてすべての一致するエントリを見つけ、対応するテーブルデータを取得します。これは、INDEX RANGE SCAN に続いて TABLE ACCESS BY INDEX ROWID 操作を行うようなものです。
- インデックススキャンの特徴は以下の通りです：
  - ランダムアクセスは順次 I/O よりもはるかに遅い。
  - インデックスにアクセスするための追加の I/O が必要です。
  - 同じブロックを複数回読み取る可能性があります。
  - 秩序ある出力を生成する。
ビットマップインデックス/ヒープスキャン: 通常の Index Scan は、インデックスからタプルポインタを1つずつ取得し、すぐにそのタプルをテーブルで訪れます。ビットマップスキャンは、インデックスからすべてのタプルポインタを一度に取得し、インメモリの "ビットマップ" データ構造を使用してそれらをソートし、その後、物理タプル位置の順序でテーブルタプルを訪れます。
- ビットマップインデックス/ヒープスキャンの特徴は以下の通りです：
  - インデックス選択性を伴う順次 I/O。
  - すべてのインデックスタプルが読み取られてソートされるため、起動が遅い。
  - IN や =ANY(array) 演算子、および低選択性インデックススキャンに頻繁に選択されます。
  - 複数のインデックスを組み合わせることができます。
  - 無秩序な出力を生成する。
インデックスのみ: Index Only Scan は、B ツリーの走査を実行し、リーフノードを通じてすべての一致するエントリを見つけます。インデックスにクエリを満たすためのすべての列が含まれているため、テーブルアクセスは必要ありません（MVCC 可視性情報を除く）。

結合

結合操作は通常、一度に2つのテーブルのみを処理します。クエリが2つ以上のテーブルの結合を含む場合、結合は順次実行されます：最初に2つのテーブルで、その後中間結果と次のテーブルで、というように。この文脈での "テーブル" という用語は、中間結果も意味する場合があります。

ネストループ: 1つのテーブルから結果を取得し、最初のテーブルの各行に対してもう1つのテーブルをクエリすることで2つのテーブルを結合します。
- 一般的に最もパフォーマンスの低い結合形式です。
- 最初のレコードを生成するのが速い。
- 2番目の子が遅い場合、パフォーマンスが悪くなる可能性があります。
- CROSS JOIN を実行できる唯一の結合です。
- 不等結合条件を実行できる唯一の結合です。
マージ結合: （ソート）マージ結合は、2つのソートされたリストをジッパーのように組み合わせます。結合の両側は事前にソートされている必要があります。
- 等価結合条件にのみ使用できます。
- 大規模なデータセットに対して最もパフォーマンスが高い。
- 入力が順序付けされている必要があり、これには遅いソートやインデックススキャンが必要になる場合があります。
- すべてのインデックスタプルが読み取られてソートされるため、起動が遅い。
ハッシュ結合: ハッシュ結合は、結合の片側から候補レコードをハッシュテーブルにロードし（プランで Hash とマークされています）、結合のもう一方の側からの各レコードに対してハッシュテーブルをプローブします。
- 等価結合条件にのみ使用できます。
- 大規模なテーブルと小規模なテーブルを結合する場合に最もパフォーマンスが高い。
- ハッシュ可能なデータ型にのみ使用されます。
- 小さいテーブルをハッシュするための起動が遅い。
- テーブル統計が古くて不正確な場合、パフォーマンスが悪くなります。

集計

GroupAggregate: GROUP BY 句に従ってプリソートされたセットを集計します。この操作は大量のデータをバッファリングしません。
HashAggregate: 一時的なハッシュテーブルを使用してレコードをグループ化します。HashAggregate 操作はプリソートされたデータセットを必要とせず、大量のメモリを使用して中間結果を実体化します（パイプライン化されていません）。出力は意味のある順序にはなりません。
Unique
WindowAgg: ウィンドウ関数の使用を示します。

その他

Sort
- ORDER BY、DISTINCT、GROUP BY、UNION、およびマージ結合で発生します。
- 起動時間がかなりかかります。
- ソートが work_mem に収まる場合、クイックソートが使用できます。
- ソートがメモリに収まらない場合、ディスクにスピルし、一時ファイルを使用します。これは非常に高価です。
Limit
- LIMIT および OFFSET の両方を処理します。
- WHERE 句がない場合、min() および max() に使用できます。
- OFFSET のためにスキップされたレコードは生成/実体化されますが、結果が返される前に破棄されます。
- 子スキャンのコストは依然として完全なコストです。
- ソートとリミットを組み合わせると、ソートの最適化形式が使用できます。

クエリチューニングのベストプラクティス

SELECT

表示に必要な属性のみを含めることで、リクエストをサポートするためにフェッチされるページを制限できます。

DISTINCT

DISTINCT は PostgreSQL の古い部分から来ており、重複レコードを識別するための効率の低い方法を使用しています。
可能であれば、GROUP BY またはサブクエリを使用して DISTINCT の使用を避けてください。

JOIN

テーブルを結合する際は、ON 句で単純な等価ステートメント（例：a.id = b.person_id）を使用するようにします。これにより、ハッシュ結合ではなくネストループ結合などのより効率的な結合技術が使用できるようになります。
可能な場合は、サブクエリを JOIN ステートメントに変換します。これにより、オプティマイザが意図を理解し、より良いプランを選択する可能性があります。
JOIN を適切に使用する: 重複した結果が得られるために GROUP BY や DISTINCT を使用していませんか？これは通常、JOIN の使用が不適切であり、コストが高くなる可能性があります。
実行プランがハッシュ結合を使用している場合、テーブルサイズの推定が間違っていると非常に遅くなる可能性があります。したがって、バキューム戦略を見直してテーブル統計が正確であることを確認してください。
可能な限り相関サブクエリを避けてください。これらはクエリコストを大幅に増加させる可能性があります。
条件に基づく行の存在を確認する場合は、EXISTS を使用してください。これは少なくとも1つの一致が見つかった時点で処理を停止します。

WHERE 句

可能であれば LIKE を避けてください。
大きなリストを IN() ステートメントに渡すのを避け、代わりに JOIN 条件を使用するか、ロジックを排除ベースに調整してください。
WHERE 句での関数呼び出しを避けてください。

GROUP BY & GROUPING SET

Deeper into Postgres 9.5 - New Group By Options for Aggregation ↗ を参照してください。

UNION vs UNION ALL

UNION は重複レコードを排除します。これにはテーブルのソートが必要です。

リクエストに関連するコストを考慮して、UNION の使用を避けることを強くお勧めします。これが必要な場合は

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  relname, -- テーブル名
  seq_scan - idx_scan AS too_much_seq, -- シーケンシャルスキャン数からインデックススキャン数を差し引いた値（インデックスが足りていない可能性がある場合、この値は大きくなります）
  CASE
    WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'missing index?' -- シーケンシャルスキャンの数がインデックススキャンの数よりも多い場合、インデックスが不足している可能性があります
    ELSE 'OK' -- インデックスは適切に設定されています
  END,
  pg_relation_size(relname::regclass) AS rel_size, -- テーブルのサイズ（バイト単位）
  seq_scan, -- シーケンシャルスキャンの回数
  idx_scan -- インデックススキャンの回数
FROM pg_stat_all_tables -- すべてのテーブルの統計情報を取得
WHERE schemaname = 'public' -- スキーマ名が'public'のテーブルのみを対象
AND pg_relation_size(relname::regclass) > 80000 -- テーブルのサイズが80000バイトより大きいもののみを対象
ORDER BY too_much_seq DESC; -- シーケンシャルスキャンとインデックススキャンの差（too_much_seq）が大きい順に並べ替える

未使用のインデックスを特定する

Copied!1
2
3
4
5
6
7
SELECT
  indexrelid::regclass as index,  -- indexrelidをregclassとしてindexという名前で取得
  relid::regclass as table,  -- relidをregclassとしてtableという名前で取得
  'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement  -- 'DROP INDEX '文字列とindexrelid::regclassを結合してdrop_statementという名前で取得
FROM pg_stat_user_indexes  -- pg_stat_user_indexesテーブルからデータを取得
JOIN pg_index USING (indexrelid)  -- pg_indexテーブルとindexrelidを用いて結合
WHERE idx_scan = 0AND indisunique is false;  -- idx_scanが0でかつindisuniqueがfalseのデータを選択

インデックスのみのスキャン（カバリングインデックス）

インデックスのみのスキャン ↗ の適切な使用法と利点について詳しく読む。