注: 以下の翻訳の正確性は検証されていません。AIPを利用して英語版の原文から機械的に翻訳されたものです。
このページでは、Postgresでのクエリの最適なパフォーマンスを目指してクエリをチューニングすることに焦点を当てています。一般的には、オブジェクトセットやActionsのような機能を使用して、データの読み取りと書き戻しを行うオントロジーの機能を基にSlateアプリケーションを構築することをお勧めします。
レスポンシブなアプリケーションを設計するには、慎重な計画と考慮が必要です。データモデル、クエリ構造、依存関係グラフは、アプリケーションのパフォーマンスと使いやすさに重要な役割を果たします。このガイドでは、Slateで記述できるPostgreSQLクエリのパフォーマンスチューニングの側面に焦点を当てており、パフォーマンスのヒューリスティック、チューニングの機会を特定する方法、パフォーマンスを改善するための提案を含んでいます。
クエリのパフォーマンスチューニングは反復的なプロセスです。"正しい"答えを生成するために同じ要求を書くための多くの異なる方法があります。
PostgreSQLクエリの目標実行時間は<= 500msです。対象となるデータセットが1000万レコード以下である場合、ほとんどのユースケースでこれが可能であるはずですが、データモデルを設計する際には慎重な計画と考慮が必要です。
クエリのパフォーマンスを評価する際、特に新しいデータセットを使用する場合は、統計を収集し分析する前にクエリを複数回実行することを確認してください。これにより、データセットが適切にキャッシュされ、パフォーマンスが期待される使用法により適合するようになります。PostgreSQLのキャッシュについて詳しく知りたい場合は、以下の外部リンクを参照してください：
EXPLAINは、クエリ実行プランを返すPostgresが提供する特に有用なコマンドです。クエリプランは、Postgresが受け取るすべてのリクエストに対して作成され、クエリの構造とデータのプロパティを使用して、リクエストを処理する最速の方法を決定します。ガイド全体で参照されるEXPLAINコマンドの簡単なレビューから始めます。
ノードをクエリの評価のステップ、または作業の論理単位と考えることができます。ノードは反転したグラフとして返され、これは応答の最初の行が最後に行われる作業の単位であることを意味します。各ノードは -> で先行されます。
-> event上のevent_type_idxを使用したIndex Scan (...)
なぜこれが重要なのか：ノードは、クエリ実行プランの非効率的な操作を特定し、パフォーマンスチューニングの努力を優先するために使用されます。
最初の数字はスタートアップコスト（最初のレコードを取得するまでの時間）です。二番目の数字は、スタートアップから完了までの全ノードの処理コストです。
(**コスト=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**)
なぜこれが重要なのか：WIDTHは、非常に大きなプロパティを持つ行を出力するノードを特定するため、または大量の列を出力するノードを特定するために使用できます。
_Cost_と同様に、最初の数字はスタートアップに必要な実際の時間（ミリ秒）です。二番目の数字はスタートアップから完了までの全ノードの処理に必要な実際の時間です。
(**actual time=10.313..12.530** rows=4857 loops=1)
名前が示す通り、actual timeはステートメントの実行によって取得されます。キーワードANALYZEはPostgresに実行計画を表示するとともにクエリを実行するように指示します。クエリがタイムアウトする問題がある場合は、ANALYZEを削除するとクエリプランのみが返され、クエリの実行よりもはるかに高速になります。
なぜこれが重要なのか：これはパフォーマンス問題を引き起こしているノードまたは操作を最も明確に示す指標です。
ノードによって出力される行の推定数。
(actual time=10.313..12.530 **rows=4857** loops=1)
なぜこれが重要なのか：ROWSは、特定の操作がなぜ予想よりも長い時間をかけているのかについての文脈を提供するのに役立ちます。
ノードの実行総数を報告します。表示される実際の時間と行の値は、実行あたりの平均です。ノードで過ごした合計時間を得るには、LOOPSの値を実際の時間で掛けます。
(actual time=10.313..12.530 rows=150 **loops=10**)
sequential scans(seq scan)：Seq Scan操作は、ディスク上に保存されている関係（テーブル）全体をスキャンします（TABLE ACCESS FULLのようなもの）。関係のスキーマ、サイズ、制約、インデックスの存在に関係なく、関係に対してseq scansを実行することは常に可能です。
index scans：Index ScanはB-treeをトラバースし、すべての一致するエントリを見つけるために葉ノードを通過し、対応するテーブルデータをフェッチします。これはINDEX RANGE SCANに続いてTABLE ACCESS BY INDEX ROWID操作を行うものと同じです。
bitmap index/heap scans：単純なIndex Scanは、インデックスから一度に1つのタプルポインタを取得し、すぐにそのタプルをテーブルで訪問します。bitmap scanは、インデックスからすべてのタプルポインタを一度に取得し、それらをメモリ内の"bitmap"データ構造を使用してソートし、次に物理的なタプルロケーションの順序でテーブルタプルを訪問します。
index only：Index Only ScanはB-treeをトラバースし、すべての一致するエントリを見つけるために葉ノードを通過します。インデックスがクエリを満足させるすべての列を持っているため、テーブルへのアクセスは必要ありません（MVCC可視性情報は例外です）。結合操作は通常、一度に2つのテーブルのみを処理します。クエリが2つ以上のテーブルを結合する場合、結合は順次に実行されます：最初に2つのテーブル、次に中間結果と次のテーブル、というように。結合の文脈では、「テーブル」という用語はしたがって「中間結果」を意味することもあります。
nested loop：一方のテーブルから結果を取得し、そのテーブルの各行に対して他方のテーブルをクエリすることで2つのテーブルを結合します。
merge join：（sort）merge joinは、ジッパーのように2つのソートされたリストを結合します。結合の両側が事前にソートされている必要があります。
hash joins：hash joinは、結合の一方の候補レコードをハッシュテーブル（計画内でHashとマークされている）にロードし、結合の他方の各レコードに対してそれを調査します。
GroupAggregate：group by句に従って事前にソートされたセットを集約します。この操作は大量のデータをバッファリングしません。HashAggregate：一時的なハッシュテーブルを使用してレコードをグループ化します。HashAggregate操作は、事前にソートされたデータセットを必要とせず、代わりに大量のメモリを使用して中間結果を具体化します（パイプライン化されません）。出力は何らかの意味で順序付けされません。UniqueWindowAgg：ウィンドウ関数の使用を示します。Sort
work_memに収まる場合、クイックソートを使用できます。Limit
DISTINCTはPostgreSQLのコードベースの古い部分から来ており、一意のレコードを識別するための効率的でない方法を使用します。DISTINCTの使用を避けてください。a.id = b.person_idなど）を使用してみてください。これにより、より効率的な結合手法が使用できるようになります（Nested Loop JoinではなくHash Joinなど）。Deeper into Postgres 9.5 - New Group By Options for Aggregationを参照してください。
UNIONは重複レコードを除去します。これには問題のテーブルをソートする必要があります。
リクエストに関連するコストを考慮に入れ、UNIONの使用を強くお勧めします。これが必要なインスタンスは非常に少なく、そうであれば、Slateからクエリする前にTransformsレイヤーで行うべきです。
UNION ALLは重複を除去せず、効率的に一方のテーブルの行を他方に追加します。
Common Mistakes: UNION VS. UNION ALLを参照してください。
以下の条件でインデックスの追加を検討してください：
Copied!1 2 3 4 5 6 7 8 9 10 11 12 13 14SELECT 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 7SELECT 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のデータを選択
index-only scansの適切な使用方法と利点について詳しく読む。