ノード
ノード統計
ノード・タイプ
クエリの最適化
最適化できないクエリ
- カーディナリティと選択性
クエリの書き換え
悪い計画とは
クエリプランの作成
- データベースラボエンジン
  - ヒント
- Rails コンソール
さらに読む

EXPLAINの実行計画を理解する

PostgreSQLでは、EXPLAIN コマンドを使用してクエリ計画を取得することができます。このコマンドは、クエリがどのように実行されるかを調べようとする時に非常に役に立ちます。クエリがこのコマンドで始まる限り、SQLクエリの中でこのコマンドを直接使用することができます：

EXPLAIN
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

GitLab.com でこのコマンドを実行すると、次のような出力が表示されます：

Aggregate  (cost=922411.76..922411.77 rows=1 width=8)
  ->  Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))

EXPLAIN_だけを_使用した場合、PostgreSQLはクエリを実際に実行するのではなく、利用可能な統計情報に基づいて_推定_実行計画を作成します。これは、実際の計画がかなり異なる可能性があることを意味します。幸いなことに、PostgreSQLはクエリを実行するオプションも提供しています。そのためには、EXPLAIN ではなく、EXPLAIN ANALYZE を使用する必要があります：

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

これは

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
Planning time: 2.861 ms
Execution time: 3428.596 ms

見ての通り、このプランはかなり異なっており、より多くのデータが含まれています。それでは順を追って説明しましょう。

EXPLAIN ANALYZE 、クエリを実行するので、データを書き込んだりタイムアウトする可能性のあるクエリを使用する場合は注意が必要です。クエリがデータを変更する場合は、このように自動的にロールバックするトランザクションで包むことを検討してください：

BEGIN;
EXPLAIN ANALYZE
DELETE FROM users WHERE id = 1;
ROLLBACK;

EXPLAIN コマンドは、BUFFERS のような追加オプションも取ります：

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

こうすると

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  Buffers: shared hit=208846
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
        Buffers: shared hit=208846
Planning time: 2.861 ms
Execution time: 3428.596 ms

詳細については、EXPLAIN 公式ドキュメントおよびusingEXPLAIN ガイドを参照してください。

ノード

全てのクエリプランはノードで構成されます。ノードは入れ子にすることができ、内側から外側に向かって実行されます。つまり、最も内側のノードが外側のノードの前に実行されます。これは、関数の呼び出しが入れ子になっていると考えるのが最も適切で、関数の呼び出しがほぐれるにつれて結果が返されます。たとえば、Aggregate で始まり、Nested Loop が続き、Index Only scan が続く計画は、次のようなRubyコードと考えることができます：

aggregate(
  nested_loop(
    index_only_scan()
    index_only_scan()
  )
)

ノードは、-> の後にノードの種類を続けます。例えば

Aggregate  (cost=922411.76..922411.77 rows=1 width=8)
  ->  Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))

ここで最初に実行されるノードはSeq scan on projects です。Filter: はノードの結果に適用される追加フィルタです。フィルタはRubyのArray#select とよく似ています。入力行を受け取り、フィルタを適用し、新しい行のリストを生成します。ノードが終了したら、その上のAggregate を実行します。

ネストされたノードは次のようになります：

Aggregate  (cost=176.97..176.98 rows=1 width=8) (actual time=0.252..0.252 rows=1 loops=1)
  Buffers: shared hit=155
  ->  Nested Loop  (cost=0.86..176.75 rows=87 width=0) (actual time=0.035..0.249 rows=36 loops=1)
        Buffers: shared hit=155
        ->  Index Only Scan using users_pkey on users users_1  (cost=0.43..4.95 rows=87 width=4) (actual time=0.029..0.123 rows=36 loops=1)
              Index Cond: (id < 100)
              Heap Fetches: 0
        ->  Index Only Scan using users_pkey on users  (cost=0.43..1.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=36)
              Index Cond: (id = users_1.id)
              Heap Fetches: 0
Planning time: 2.585 ms
Execution time: 0.310 ms

ここでは、まず2つの別々の「インデックスのみ」スキャンを実行し、次にこれら2つのスキャンの結果に対して「入れ子ループ」を実行します。

ノード統計

プランの各ノードには、コスト、生成された行数、実行されたループ数など、関連する統計情報があります。例えば

Seq Scan on projects  (cost=0.00..908044.47 rows=5746914 width=0)

ここでは、コストの範囲は0.00..908044.47 （これについては後で説明します）であり、（EXPLAIN ANALYZE ではなくEXPLAIN を使用しているため）このノードで生成される行数は合計5,746,914行と推定されることがわかります。width 統計情報には、各行の推定幅がバイト単位で記述されています。

costs フィールドは、ノードのコストを指定します。コストは、クエリプランナのコストパラメータによって決定される任意の単位で測定されます。何がコストに影響を与えるかは、seq_page_cost 、cpu_tuple_cost 、その他様々な設定に依存します。costsフィールドの書式は以下の通りです：

STARTUP COST..TOTAL COST

起動コストはノードを起動するためにどれだけのコストがかかったかを示し、総コストはノード全体がどれだけのコストがかかったかを示します。一般的に、値が大きいほどノードは高価です。

EXPLAIN ANALYZE を使用する場合、これらの統計情報には、実際にかかった時間（ミリ秒単位）やその他の実行時の統計情報（例えば、実際に生成された行数）も含まれます：

Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)

ここでは、5,746,969行が返されると見積もっていましたが、実際には5,746,940行が返されたことがわかります。また、このシーケンシャルスキャンの_実行に_2.98秒かかっていることもわかります。

EXPLAIN (ANALYZE, BUFFERS) を使用すると、フィルタによって削除された行数、使用されたバッファ数などの情報も得られます。例えば

Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
  Filter: (visibility_level = ANY ('{0,20}'::integer[]))
  Rows Removed by Filter: 65677
  Buffers: shared hit=208846

ここで、フィルタは65,677行を削除しなければならず、208,846個のバッファを使用していることがわかります。PostgreSQLの各バッファは8KB（8192バイト）なので、上のノードでは1.6GBのバッファを使用していることになります。これは大変な量です！

統計情報の中にはループごとの平均値もあれば、合計値もあることに注意してください：

フィールド名	値の種類
実際の合計時間	ループ当たり平均
実際の行数	ループ当たり平均
バッファシェアードヒット	合計値
バッファ共有リード	合計値
共有バッファ汚れ	合計値
バッファシェアード・ライト	合計値
I/O読み取り時間	合計値
I/O リードライト	合計値

使用例：

 ->  Index Scan using users_pkey on public.users  (cost=0.43..3.44 rows=1 width=1318) (actual time=0.025..0.025 rows=1 loops=888)
       Index Cond: (users.id = issues.author_id)
       Buffers: shared hit=3543 read=9
       I/O Timings: read=17.760 write=0.000

ここでは、このノードが3552個のバッファ(3543 + 9)を使用し、888行(888 * 1)を返し、実際の時間は22.2ミリ秒(888 * 0.025)であったことがわかります。 17.総所要時間のうち76ミリ秒は、キャッシュにないデータを取得するためのディスクからの読み込みに費やされました。

ノード・タイプ

ノードには非常に多くの種類があるので、ここでは一般的なものだけを取り上げます。

利用可能なすべてのノードとその説明の完全なリストは、PostgreSQLソースファイルplannodes.hにあります。 pgMustardのEXPLAINドキュメントでも、ノードとそのフィールドの詳細な説明を見ることができます。

シーケンススキャン

データベーステーブルの（チャンクの）シーケンシャルスキャン。これはArray#each をデータベーステーブルに対して使用するようなものです。シーケンシャルスキャンは、多くの行を取得する際に非常に時間がかかることがあります。

インデックスのみのスキャン

テーブルから何もフェッチする必要のないインデックスに対するスキャン。インデックスのみのスキャンでもテーブルからデータをフェッチする場合があります。この場合、ノードにはHeap Fetches: 統計情報が含まれます。

インデックススキャン

テーブルからデータを取得する必要があるインデックスに対するスキャン。

ビットマップインデックススキャンとビットマップヒープスキャン

ビットマップスキャンはシーケンシャルスキャンとインデックススキャンの中間に位置します。通常、インデックススキャンではデータが多すぎるが、シーケンシャルスキャンではデータが少なすぎる場合に使用されます。ビットマップスキャンはビットマップインデックスと呼ばれるものを使ってスキャンを行います。

PostgreSQLのソースコードにはビットマップスキャンについて以下のように書かれています：

ビットマップインデックススキャンはタプルの潜在的な位置のビットマップを提供します。ビットマップは先祖のビットマップヒープスキャンノードによって使用され、場合によっては、他のビットマップインデックススキャンの結果と結合するために、中間のBitmap AndノードやBitmap Orノードを通過した後に使用されます。

リミット

入力行にLIMIT を適用します。

並び替え

ORDER BY ステートメントを使用して指定されたとおりに入力行をソートします。

入れ子ループ

入れ子ループは、その前にあるノードが生成する行ごとに子ノードを実行します。例えば

->  Nested Loop  (cost=0.86..176.75 rows=87 width=0) (actual time=0.035..0.249 rows=36 loops=1)
      Buffers: shared hit=155
      ->  Index Only Scan using users_pkey on users users_1  (cost=0.43..4.95 rows=87 width=4) (actual time=0.029..0.123 rows=36 loops=1)
            Index Cond: (id < 100)
            Heap Fetches: 0
      ->  Index Only Scan using users_pkey on users  (cost=0.43..1.96 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=36)
            Index Cond: (id = users_1.id)
            Heap Fetches: 0

ここでは、最初の子ノード(Index Only Scan using users_pkey on users users_1)は36行を生成し、1回(rows=36 loops=1)実行されます。次のノードは1行(rows=1)を生成しますが、36回(loops=36)繰り返されます。これは、前のノードが36行を生成したからです。

つまり、様々な子ノードが多くの行を生成し続けると、入れ子ループはクエリの速度をすぐに低下させます。

クエリの最適化

クエリを最適化する方法を見てみましょう。次のクエリを例にしてみましょう：

SELECT COUNT(*)
FROM users
WHERE twitter != '';

このクエリは、Twitterのプロファイルを設定しているユーザー数をカウントします。EXPLAIN (ANALYZE, BUFFERS) を使って実行してみましょう：

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM users
WHERE twitter != '';

これは以下のプランを生成します：

Aggregate  (cost=845110.21..845110.22 rows=1 width=8) (actual time=1271.157..1271.158 rows=1 loops=1)
  Buffers: shared hit=202662
  ->  Seq Scan on users  (cost=0.00..844969.99 rows=56087 width=0) (actual time=0.019..1265.883 rows=51833 loops=1)
        Filter: ((twitter)::text <> ''::text)
        Rows Removed by Filter: 2487813
        Buffers: shared hit=202662
Planning time: 0.390 ms
Execution time: 1271.180 ms

このクエリプランから以下のことがわかります：

users テーブルに対してシーケンシャルスキャンを実行する必要があります。
このシーケンシャルスキャンでは、Filter を使用して2,487,813行をフィルタリングします。
202,622個のバッファを使用しており、これは1.58GBのメモリに相当します。
これをすべて実行するのに1.2秒かかります。

ユーザーを数えただけだと考えると、かなり高いですね！

変更を始める前に、users テーブルに既存のインデックスがあるかどうかを確認してみましょう。この情報は、psql コンソールで\d users を実行し、Indexes: セクションまでスクロールダウンすることで取得できます：

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_confirmation_token" UNIQUE, btree (confirmation_token)
    "index_users_on_email" UNIQUE, btree (email)
    "index_users_on_reset_password_token" UNIQUE, btree (reset_password_token)
    "index_users_on_static_object_token" UNIQUE, btree (static_object_token)
    "index_users_on_unlock_token" UNIQUE, btree (unlock_token)
    "index_on_users_name_lower" btree (lower(name::text))
    "index_users_on_accepted_term_id" btree (accepted_term_id)
    "index_users_on_admin" btree (admin)
    "index_users_on_created_at" btree (created_at)
    "index_users_on_email_trigram" gin (email gin_trgm_ops)
    "index_users_on_feed_token" btree (feed_token)
    "index_users_on_group_view" btree (group_view)
    "index_users_on_incoming_email_token" btree (incoming_email_token)
    "index_users_on_managing_group_id" btree (managing_group_id)
    "index_users_on_name" btree (name)
    "index_users_on_name_trigram" gin (name gin_trgm_ops)
    "index_users_on_public_email" btree (public_email) WHERE public_email::text <> ''::text
    "index_users_on_state" btree (state)
    "index_users_on_state_and_user_type" btree (state, user_type)
    "index_users_on_unconfirmed_email" btree (unconfirmed_email) WHERE unconfirmed_email IS NOT NULL
    "index_users_on_user_type" btree (user_type)
    "index_users_on_username" btree (username)
    "index_users_on_username_trigram" gin (username gin_trgm_ops)
    "tmp_idx_on_user_id_where_bio_is_filled" btree (id) WHERE COALESCE(bio, ''::character varying)::text IS DISTINCT FROM ''::text

twitter 。つまり、PostgreSQLはこの場合シーケンシャルスキャンを実行しなければなりません。以下のインデックスを追加して、これを修正してみましょう：

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

EXPLAIN (ANALYZE, BUFFERS) を使用してクエリを再実行すると、以下のようになります：

Aggregate  (cost=61002.82..61002.83 rows=1 width=8) (actual time=297.311..297.312 rows=1 loops=1)
  Buffers: shared hit=51854 dirtied=19
  ->  Index Only Scan using twitter_test on users  (cost=0.43..60873.13 rows=51877 width=0) (actual time=279.184..293.532 rows=51833 loops=1)
        Filter: ((twitter)::text <> ''::text)
        Rows Removed by Filter: 2487830
        Heap Fetches: 26037
        Buffers: shared hit=51854 dirtied=19
Planning time: 0.191 ms
Execution time: 297.334 ms

データを取得するのにかかる時間は1.2秒ではなく、300ミリ秒弱になりました。しかし、まだ51,854個のバッファを使用しており、これは約400MBのメモリに相当します。このような単純なクエリで300ミリ秒はかなり遅いです。このクエリがまだ高い理由を理解するために、以下を見てみましょう：

Index Only Scan using twitter_test on users  (cost=0.43..60873.13 rows=51877 width=0) (actual time=279.184..293.532 rows=51833 loops=1)
  Filter: ((twitter)::text <> ''::text)
  Rows Removed by Filter: 2487830

インデックスのみのスキャンから始めていますが、Filter 、2,487,830行を除外しています。なぜでしょうか？インデックスの作成方法を見てみましょう：

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter);

PostgreSQLには、twitter 列のすべての可能な値を、空文字列であってもインデックス化するように指示しました。このクエリはWHERE twitter != '' を使用します。これは、シーケンシャルスキャンを行う必要がないため、インデックスが改善されることを意味します。つまり、PostgreSQLはインデックスの結果にフィルタを適用し、これらの値を取り除く_必要が_あります。

幸いなことに、”部分インデックス “を使用することで、これをさらに改善することができます。部分インデックスとは、インデックス作成時に適用されるWHERE 条件を持つインデックスのことです。例えば

CREATE INDEX CONCURRENTLY some_index ON users (email) WHERE id < 100

このインデックスは、WHERE id < 100 にマッチする行のemail 値のみをインデックス化します。部分インデックスを使用して、Twitterインデックスを以下のように変更することができます：

CREATE INDEX CONCURRENTLY twitter_test ON users (twitter) WHERE twitter != '';

作成後、クエリを再度実行すると、以下のようなプランになります：

Aggregate  (cost=1608.26..1608.27 rows=1 width=8) (actual time=19.821..19.821 rows=1 loops=1)
  Buffers: shared hit=44036
  ->  Index Only Scan using twitter_test on users  (cost=0.41..1479.71 rows=51420 width=0) (actual time=0.023..15.514 rows=51833 loops=1)
        Heap Fetches: 1208
        Buffers: shared hit=44036
Planning time: 0.123 ms
Execution time: 19.848 ms

_かなり_良くなりました！これで、データを取得するのに20ミリ秒しかかからなくなり、（元の1.58GBではなく）約344MBのバッファしか使用しなくなりました。twitter これがうまくいく理由は、PostgreSQLがFilter を適用する必要がなくなったからです。

クエリを最適化する度に部分インデックスを追加するだけではいけないことに注意してください。全てのインデックスは書き込みの度に更新されなければならず、インデックスされたデータの量によってはかなりの容量を必要とします。そのため、まず再利用できそうな既存のインデックスがあるかどうかを確認します。ない場合は、既存のクエリと新しいクエリの両方に合うように、既存のインデックスを少し変更できるかどうかを確認します。既存のインデックスがどれも使用できない場合にのみ、新しいインデックスを追加してください。

実行計画を比較する場合、タイミングだけを重要なメトリクスと考えないでください。良いタイミングはどのような最適化でも主な目標ですが、比較に使用するにはあまりに不安定です（例えば、キャッシュの状態に大きく依存します）。クエリを最適化する場合、通常は扱うデータ量を減らす必要があります。そのため、最適化の際には、使用するバッファの数（読み込みとヒット）を調べ、その数を減らすようにします。バッファ数を減らすことで、タイミングを減らすことができます。Database Lab Engineは、プランが本番環境と構造的に同じであることを保証します(そして、全体のバッファ数は本番環境と同じです)が、キャッシュ状態とI/O速度の違いにより、タイミングが異なる可能性があります。

最適化できないクエリ

クエリを最適化する方法について見てきましたが、最適化できない可能性のある別のクエリを見てみましょう：

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM projects
WHERE visibility_level IN (0, 20);

EXPLAIN (ANALYZE, BUFFERS) の出力は以下のようになります：

Aggregate  (cost=922420.60..922420.61 rows=1 width=8) (actual time=3428.535..3428.535 rows=1 loops=1)
  Buffers: shared hit=208846
  ->  Seq Scan on projects  (cost=0.00..908053.18 rows=5746969 width=0) (actual time=0.041..2987.606 rows=5746940 loops=1)
        Filter: (visibility_level = ANY ('{0,20}'::integer[]))
        Rows Removed by Filter: 65677
        Buffers: shared hit=208846
Planning time: 2.861 ms
Execution time: 3428.596 ms

出力を見てみると、次のようになります。 Filter：

Filter: (visibility_level = ANY ('{0,20}'::integer[]))
Rows Removed by Filter: 65677

フィルタによって削除された行数を見ると、projects.visibility_level にインデックスを追加して、シーケンシャルスキャン＋フィルタをインデックスのみのスキャンにしたくなるかもしれません。

しかし、残念ながら、そんなことをしても何も改善されません。一部の人が信じているかもしれませんが、インデックスが存在_したからといって_、PostgreSQLがそれを実際に使用する_保証は_ありません。例えば、SELECT * FROM projects 、インデックスを使用してテーブルからデータを取得するのではなく、テーブル全体をスキャンした方がはるかに安上がりです。このような場合、PostgreSQLはインデックスを使用しないことを決定するかもしれません。

次に、このクエリが何をするのかを少し考えてみましょう。上の計画では、この問い合わせが非常に多くの行（5,745,940）を生成していることがわかります。以下のクエリを実行して調べてみましょう：

SELECT visibility_level, count(*) AS amount
FROM projects
GROUP BY visibility_level
ORDER BY visibility_level ASC;

GitLab.com の場合は次のようになります：

 visibility_level | amount
------------------+---------
                0 | 5071325
               10 |   65678
               20 |  674801

プロジェクトの総数は 5,811,804 で、そのうち 5,746,126 がレベル 0 あるいは 20 です。これはテーブル全体の 98% にあたります！

つまり、何をしても、このクエリはテーブル全体の98%を取得します。ほとんどの時間がまさにこの作業に費やされているため、このクエリを改善するためにできることは、このクエリをまったく実行_しない_以外にあまりありません。

ここで重要なのは、シーケンシャルスキャンを見た瞬間にインデックスを追加することを勧める人もいるかもしれませんが、クエリが何を行っているのか、どれだけのデータを取得しているのかなどを理解することの方が_はるかに重要_だということです。結局のところ、理解できないものを最適化することはできません。

カーディナリティと選択性

先ほど、クエリはテーブルの98%の行を取得しなければならないことがわかりました。データベースでは、カーディナリティと選択性という2つの用語がよく使われます。カーディナリティとは、テーブルの特定のカラムに含まれる一意な値の数のことです。

選択性とは、オペレーション（例えば、インデックススキャンやフィルタ）によって生成される一意な値の数のことで、行の総数に対する相対値です。選択性が高ければ高いほど、PostgreSQLはインデックスを使用できる可能性が高くなります。

上記の例では、一意な値は3つしかありません：0、10、20です。これは、カーディナリティが3であることを意味します。また、選択率も非常に低く、0.0000003%（2 / 5,811,804）です。これは、Filter が2つの値（0 と20 ）のみを使用してフィルタリングしているためです。このような低い選択性では、PostgreSQLがインデックスを使用する価値がないと判断しても不思議ではありません。

クエリの書き換え

上記のクエリはそのままでは最適化できません。しかし、このクエリの目的を少し変えてみたらどうでしょうか？visibility_level 0または20のすべてのプロジェクトを検索する代わりに、ユーザーが何らかの形でやりとりしたプロジェクトを検索するとしたらどうでしょう？

幸い、GitLab にはこのための答えがあります。それはuser_interacted_projects というテーブルです。このテーブルのスキーマは次のとおりです：

Table "public.user_interacted_projects"
   Column   |  Type   | Modifiers
------------+---------+-----------
 user_id    | integer | not null
 project_id | integer | not null
Indexes:
    "index_user_interacted_projects_on_project_id_and_user_id" UNIQUE, btree (project_id, user_id)
    "index_user_interacted_projects_on_user_id" btree (user_id)
Foreign-key constraints:
    "fk_rails_0894651f08" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    "fk_rails_722ceba4f7" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE

このクエリを書き換えて、JOIN このテーブルをプロジェクトに追加し、特定のユーザーのプロジェクトを取得してみましょう：

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM projects
INNER JOIN user_interacted_projects ON user_interacted_projects.project_id = projects.id
WHERE projects.visibility_level IN (0, 20)
AND user_interacted_projects.user_id = 1;

ここでは以下のようにします：

私たちのプロジェクトをご覧ください。
INNER JOIN user_interacted_projectsつまり、user_interacted_projects に対応する行がある、projects の行だけが残ります。
これを、visibility_level が 0 か 20 のプロジェクトと、 ID 1 のユーザーが対話したプロジェクトに限定します。

このクエリを実行すると、以下のようなプランが得られます：

 Aggregate  (cost=871.03..871.04 rows=1 width=8) (actual time=9.763..9.763 rows=1 loops=1)
   ->  Nested Loop  (cost=0.86..870.52 rows=203 width=0) (actual time=1.072..9.748 rows=143 loops=1)
         ->  Index Scan using index_user_interacted_projects_on_user_id on user_interacted_projects  (cost=0.43..160.71 rows=205 width=4) (actual time=0.939..2.508 rows=145 loops=1)
               Index Cond: (user_id = 1)
         ->  Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)
               Index Cond: (id = user_interacted_projects.project_id)
               Filter: (visibility_level = ANY ('{0,20}'::integer[]))
               Rows Removed by Filter: 0
 Planning time: 2.614 ms
 Execution time: 9.809 ms

ここでは、データを取得するのに10ミリ秒弱しかかかっていません。また、はるかに少ないプロジェクトを取得していることもわかります：

Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)
  Index Cond: (id = user_interacted_projects.project_id)
  Filter: (visibility_level = ANY ('{0,20}'::integer[]))
  Rows Removed by Filter: 0

ここでは、145 回のループ (loops=145) を実行し、すべてのループで 1 行 (rows=1) を生成しています。これは以前よりはるかに少なく、クエリのパフォーマンスも向上しています！

プランを見ると、コストが非常に低いこともわかります：

Index Scan using projects_pkey on projects  (cost=0.43..3.45 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=145)

ここでは、コストはわずか3.45で、これに7.25ミリ秒かかります（0.05 * 145）。次のインデックススキャンはもう少し高価です：

Index Scan using index_user_interacted_projects_on_user_id on user_interacted_projects  (cost=0.43..160.71 rows=205 width=4) (actual time=0.939..2.508 rows=145 loops=1)

ここでは、コストは160.71（cost=0.43..160.71 ）で、約2.5ミリ秒かかります（actual time=.... の出力に基づく）。

ここで最もコストがかかる部分は、これら2つのインデックススキャンの結果に対して動作する “入れ子ループ “です：

Nested Loop  (cost=0.86..870.52 rows=203 width=0) (actual time=1.072..9.748 rows=143 loops=1)

ここでは、203行、9.748ミリ秒、870.52ディスクページフェッチを実行し、1つのループで143行を生成しなければなりませんでした。

ここで重要なことは、クエリをより良くするために（クエリの一部を）書き直さなければならないことがあるということです。それは、より良いパフォーマンスを得るために、機能を少し変更しなければならないこともあるということです。

悪い計画とは

というのも、「悪い」の定義は、解決しようとしている問題によって異なるからです。しかし、次のようないくつかのパターンは、ほとんどの場合、避けたほうがよいでしょう：

大きなテーブルのシーケンシャルスキャン
多くの行を削除するフィルタ
_大量の_バッファを必要とする特定のステップの実行（たとえば、GitLab.com のインデックススキャンで 512 MB 以上を必要とするような場合）。

一般的なガイドラインとしては、次のようなクエリを目指してください：

10ミリ秒以下。1回のリクエストでSQLに費やす時間は約100ミリ秒が目標なので、すべてのクエリは可能な限り高速であるべきです。
作業量に対して過剰なバッファを使用しないこと。例えば、10行を取得するのに1GBのバッファを必要とすべきではありません。
ディスクIOオペレーションを長時間実行しないこと。このデータをEXPLAIN ANALYZE の出力に含めるには、設定track_io_timing を有効にする必要があります。
SELECT * FROM users のように、集計せずに行を取得する場合はLIMIT を適用します。
特に、クエリが返される行の数を制限するためにLIMIT を使用していない場合は、Filter を使用して、多すぎる行をフィルタリングしません。フィルタは通常、（部分的な）インデックスを追加することで取り除くことができます。

これらは_ガイドライン_であり、厳しい要求ではありません。唯一の_ルールは_、EXPLAIN (ANALYZE, BUFFERS) や以下のような関連ツールを使用して、クエリを（できれば実運用に近いデータベースを使用して）_必ず測定しなければ_ならないということです：

クエリプランの作成

クエリプランの出力を得るにはいくつかの方法があります。もちろん、psql コンソールでEXPLAIN クエリを直接実行することもできますし、以下のような方法もあります。

データベースラボエンジン

GitLabのチームメンバーはDatabase Lab EngineとSQL最適化ツールJoe Botを使うことができます。

Database Lab Engineは開発者に本番データベースのクローンを提供し、Joe Botは実行プランの探索を支援します。

Joe Botはウェブインタフェースから利用できます。

Joe Bot を使用すると、DDL ステートメント（インデックス、テーブル、カラムの作成など）を実行し、SELECT 、UPDATE 、DELETE ステートメントのクエリプランを取得することができます。

例えば、本番環境にはまだ存在しないカラムに新しいインデックスをテストするには、次のようにします：

カラムを作成します：

exec ALTER TABLE projects ADD COLUMN last_at timestamp without time zone

インデックスを作成します：

exec CREATE INDEX index_projects_last_activity ON projects (last_activity_at) WHERE last_activity_at IS NOT NULL

テーブルを分析し、統計情報を更新します：

exec ANALYZE projects

クエリプランを取得します：

explain SELECT * FROM projects WHERE last_activity_at < CURRENT_DATE

完了したら、変更をロールバックすることができます：

reset

利用可能なオプションの詳細については、実行してください：

help

ウェブインターフェースには、以下の実行計画ビジュアライザーが含まれています：

ヒント

データベースの接続はセッション中ずっとメンテナーに維持されます。そのため、enable_seqscan やwork_mem などのセッション変数にexec set ... を使うことができます。これらの設定は、リセットするまで、それ以降のすべてのコマンドに適用されます。例えば、以下のコマンドで Parallels クエリを無効にすることができます。

exec SET max_parallel_workers_per_gather = 0

Rails コンソール

activerecord-explain-analyze 、Railsコンソールからクエリプランを直接生成できます：

pry(main)> require 'activerecord-explain-analyze'
=> true
pry(main)> Project.where('build_timeout > ?', 3600).explain(analyze: true)
  Project Load (1.9ms)  SELECT "projects".* FROM "projects" WHERE (build_timeout > 3600)
  ↳ (pry):12
=> EXPLAIN for: SELECT "projects".* FROM "projects" WHERE (build_timeout > 3600)
Seq Scan on public.projects  (cost=0.00..2.17 rows=1 width=742) (actual time=0.040..0.041 rows=0 loops=1)
  Output: id, name, path, description, created_at, updated_at, creator_id, namespace_id, ...
  Filter: (projects.build_timeout > 3600)
  Rows Removed by Filter: 14
  Buffers: shared hit=2
Planning time: 0.411 ms
Execution time: 0.113 ms

さらに読む

Dalibo.orgのプレゼンテーションに、クエリプランを理解するためのより広範なガイドがあります。

Depeszブログにもクエリプランに特化した良いセクションがあります。