はじめに

Laravel Eloquent には whereHas() というメソッドがあります。このメソッドは、「リレーション先が存在するか」という条件を追加することができます。

最近whereHas()を使用した際に、以前のレビューコメント「whereHas()は相関サブクエリを発生させる可能性がある」と言う内容を思い出し、今回はPostgreSQLでその検証を試みました。

この記事では、その検証過程やEXPLAINの読み解き方について解説します。

対象のクエリ

以下に記載してあるSQLがwhereHas()を使用して発行されたSQLです。

簡単に概要を説明すると、「タグidが9または2であるタグと紐づいている記事」を取得するSQLです。 ※ただし、上記条件に付け加えて「同一のテナントに属する」という条件も追加されています。

SELECT
  *
FROM
  "articles"
WHERE
  EXISTS (
    SELECT
      *
    FROM
      "tags"
    INNER JOIN "article_tag"
      ON "tags"."id" = "article_tag"."tag_id"
    WHERE
      "articles"."id" = "article_tag"."article_id"
    AND
      "tags"."id" in('9', '2')
    AND
      "tags"."tenant_id" = 'f26f0996-d942-45dc-a021-1a0e56c33c06'
  )
AND
  "articles"."tenant_id" = 'f26f0996-d942-45dc-a021-1a0e56c33c06'
LIMIT 2 
OFFSET 0

特に注目すべきは、以下の部分です。この部分により、相関サブクエリが発生する可能性が考えられます。

    WHERE
      "articles"."id" = "article_tag"."article_id"

articles.idがサブクエリの中で参照されており、このarticles.idがarticlesの1レコードごとに変化する値である為、1レコードごとにこのサブクエリが実行される可能性があります。
全ての記事に対して、タグと紐づいているか確認するクエリが実行されるイメージです。

EXPLAINしてみる

EXPLAINの結果を見ると、以下のような内容が出力されました。

Limit  (cost=0.43..24.51 rows=1 width=1661)
  ->  Nested Loop Semi Join  (cost=0.43..24.51 rows=1 width=1661)
        ->  Index Scan using articles_tenant_id_slug_unique on articles  (cost=0.14..8.16 rows=1 width=1661)
              "Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
        ->  Nested Loop  (cost=0.29..16.34 rows=1 width=8)
              ->  Index Scan using tags_tenant_id_slug_unique on tags  (cost=0.14..8.16 rows=1 width=8)
                    "Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
                    "Filter: (id = ANY ('{9,2}'::bigint[]))"
              ->  Index Only Scan using article_tag_article_id_tag_id_unique on article_tag  (cost=0.15..8.17 rows=1 width=16)
                    Index Cond: ((article_id = articles.id) AND (tag_id = tags.id))

まずはcost, rows, widthが何箇所かに書かれているのが見えると思います。
括弧内の数値に関しては全て同じものを表しているので、まずはこれに関して説明していきます。

Limit  (cost=0.43..24.51 rows=1 width=1661)

cost

cost=0.43..24.51は、この操作のコストを示します。コストは、クエリの実行にかかる予想時間を示す抽象的な単位であり、0.43だから0.43sということではないので注意が必要です。

左側の数値(0.43)は初期処理の推定コストです。
これは、クエリが実際にデータを返し始める前に、どれだけの「時間」がかかるかの推定です。
例：ソートを行う場合、実際のソート操作の時間がこれに該当します。
右側の数値(24.51)は全体推定コストです。
これは、クエリを完全に実行するのにどれだけの「時間」がかかるかの推定です。
ただし、例えば「LIMIT」句を使っている場合、実際には必要な行だけ取得してすぐに処理が終わることがあります。
そのため、この数値は必ずしも正確ではありません。

rows

rowsはこの計画ノードが出力する行の推定数です。
このクエリが完了したときに、何行のデータが返されるかの推定数です。
※LIMIT=2 としているはずなのに結果が rows=1 となっていますが、
ここで重要なのは、EXPLAINによって表示されるrowsの数は「予測される行数」であり、実際の実行結果とは必ずしも一致しないという点です。

width

widthはこの計画ノードが出力する行の（バイト単位での）推定平均幅です。
このクエリが返す1行あたりのデータの「サイズ」の推定値です。
これはバイト単位で表示されます。

cost, rows, width に関しては、今回に関してはこれ以上説明しないので以降の説明では表記を省略します。

順番に関しての説明

EXPLAINした結果は木構造になっています。基本的には以下の２つのルールで順番に実行されていきます。

兄弟ノードであれば上から順に実行
子ノードがあればそちらを先に実行

子ノードの実行結果を親ノードが使用して、またその結果を親ノードが、、、という流れになっています。

わかりやすいように先ほどのEXPLAIN結果に番号を振ってみます。 ->がついているのがノードを表しているので、

6: Limit
  ->  5: Nested Loop Semi Join
        ->  1: Index Scan using articles_tenant_id_slug_unique on articles
              "Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
        ->  4: Nested Loop
              ->  2: Index Scan using tags_tenant_id_slug_unique on tags
                    "Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
                    "Filter: (id = ANY ('{9,2}'::bigint[]))"
              ->  3: Index Only Scan using article_tag_article_id_tag_id_unique on article_tag
                    Index Cond: ((article_id = articles.id) AND (tag_id = tags.id))

という順番になります。では1から何を処理しているか見ていきましょう。

`EXPLAIN`の内容に関しての説明

1つめ

Index Scan using articles_tenant_id_slug_unique on articles
"Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"

行ごとの説明

一行目

articlesテーブルに対して、articles_tenant_id_slug_uniqueというインデックスを使用してIndex Scanという方法でデータの検索を行います。

二行目

検索の条件として、tenant_idというカラムの値が'f26f0996-d942-45dc-a021-1a0e56c33c06'と等しいデータを探しています。

補足

このノードでしている処理を端的に説明すると、テナントidで記事を絞り込む処理になります。該当のSQLは以下の部分です。

  "articles"."tenant_id" = 'f26f0996-d942-45dc-a021-1a0e56c33c06'

インデックスに関しての説明ですが、 articles_tenant_id_slug_uniqueはテナントidとスラッグの複合ユニーク制約を表します。

雰囲気で読めると思いますが、Index Scanはインデックスを使ってデータの検索を行うことを表しています。
ちなみにインデックスが効いてない場合は、Seq Scanとかになっていると思います。
ざっとスキャンの種類を見たい人はこちらがおすすめです。

一方Index Condはインデックス条件を表しています。
インデックス条件とは、インデックススキャンが行われる際に、どのような条件でインデックスを使ってデータを検索するかを示しています。
具体的には、インデックスの中でどの値を基にしてデータを探すかの条件を表しています。

今回の検索条件だと、複合ユニーク制約のうち先に指定しているtenant_idだけで絞り込んでいる為、Index Scanが行われています。
複合ユニーク制約と順番に関してはこちらを見るとわかりやすいと思います。

2つめ

Index Scan using tags_tenant_id_slug_unique on tags
"Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
"Filter: (id = ANY ('{9,2}'::bigint[]))"

行ごとの説明

一行目

tagsテーブルに対して、tags_tenant_id_slug_uniqueというインデックスを使用してIndex Scanという方法でデータの検索を行います。

二行目

検索の条件として、tenant_idというカラムの値が'f26f0996-d942-45dc-a021-1a0e56c33c06'と等しいデータを探しています。

三行目

インデックスを使用して得られた結果の中からさらに特定の条件を満たす行をフィルタリングすることを示しています。
この場合、その条件は「id」カラムの値が9または2である行だけを選択するというものです。

補足

このノードでしている処理を端的に説明すると、Index Scanで取得したデータの中から、さらにidカラムが9または2の行だけを取り出す操作が行われます。

該当のSQLは以下の部分です。

    AND
      "tags"."id" in('9', '2')
    AND
      "tags"."tenant_id" = 'f26f0996-d942-45dc-a021-1a0e56c33c06'

PostgreSQL（および多くのRDBMS）では、一つのクエリオペレーションで使用できるインデックスは基本的に1つだけなので、複数の条件がある場合、最も効果的なインデックスが選択され、そのインデックスを使用してスキャンが行われます。
そして他の条件はフィルタリングとして後処理されます。

その為、 in() の条件がフィルタリング処理になっています。

フィルタリング処理に関して補足ですが、
通常idは主キーでインデックスが貼られているため、通常は非常に効率的にスキャンできます。
しかし、このクエリではtenant_idに基づくスキャンが優先され、idの条件は後からフィルタリングされる形となっています。
もしidの条件だけで絞り込みたい場合や、idのインデックスを優先的に使用したい場合は、クエリの構造やインデックスの設計を見直すことを検討することが必要になります。

3つめ

Index Only Scan using article_tag_article_id_tag_id_unique on article_tag
Index Cond: ((article_id = articles.id) AND (tag_id = tags.id))

行ごとの説明

一行目

article_tagテーブルに対して、article_tag_article_id_tag_id_uniqueというインデックスを使用してIndex Only Scanという方法でデータの検索を行います。

二行目

検索の条件として、article_idというカラムの値articles.idと等しいかつ、tag_idがtags.idと等しいデータを探しています。

補足

このノードでしている処理を端的に説明すると、中間テーブルarticle_tagをarticle_idとtag_idで絞り込む処理です。

インデックスに関しての説明ですが、 article_tag_article_id_tag_id_uniqueは記事idとタグidの複合ユニーク制約を表しています。

article_tagテーブルは、article_idとtag_idだけを持つテーブルです。
結果を返すのに必要な列が全てインデックスに含まれているので、テーブルアクセスが不要なIndex Only Scanとなっています。

ここではarticlesとtagsがありますが、どこからきているかわからないと思います。
以降の親ノードの説明ではっきりするのでここでは深く考えなくて良いです。

4つ目

Nested Loop

行ごとの説明

一行目

Nested Loop は、データベースのクエリ実行計画において、2つのテーブル（またはサブクエリの結果セット）の結合を行うための基本的なアルゴリズムの一つです。
具体的には、一方のテーブルの各行に対して、もう一方のテーブルを順番にスキャンして結合条件を満たす行を探すという操作を行います。

`Nested Loop`に関しての詳細な説明

このような記述は、

4: Nested Loop
    ->  2: Index Scan using tags_tenant_id_slug_unique on tags
        "Index Cond: ((tenant_id)::text = 'f26f0996-d942-45dc-a021-1a0e56c33c06'::text)"
        "Filter: (id = ANY ('{9,2}'::bigint[]))"
    ->  3: Index Only Scan using article_tag_article_id_tag_id_unique on article_tag
         Index Cond: ((article_id = articles.id) AND (tag_id = tags.id))

このように表せます。

4: Nested Loop
    ->  2: `tags`テーブルを`tenant_id`で検索した後、`id`が9,2のものをフィルタリング処理
    ->  3: `article_tag`テーブルを`article_id`と`tag_id`で検索

Nested Loopの動作を細かく説明します。

外側のテーブル（2:の結果、tagsテーブル）の最初の行を取得します。
内側のテーブル（3:の結果、article_tagテーブル）の全行をスキャンし、結合条件に合致する行を探します。
結合条件に合致する行を結合し、結果として出力します。
外側のテーブル（2:の結果）の次の行を取得し、2と3のステップを繰り返します。
外側のテーブルの全行に対してこの処理を繰り返します。

2. の「結合条件に合致する行」の結合条件に関してですが、結合条件は通常、Nested Loopの中のIndex Condや他の条件として表示されることが多いです。

今回は以下の部分が結合条件です。

Index Cond: ((article_id = articles.id) AND (tag_id = tags.id))

tagsテーブルの一行に対して、tag_id = tags.idの結合条件が指定されます。
articles.idはさらに親ノードを見ていくとわかります。
その説明は次で。

ちなみに2.の時、全行スキャンが走るので結合条件に関するインデックスが貼られていると、効率的に動作する可能性が高まります。
今回の場合は3:の部分を見るとわかるように、Index Only Scanとなっているので、効率的な動作だと言えると思います。

5つめ

Nested Loop Semi Join

`Semi Join`に関しての説明

Semi Joinは、左側のテーブル（この場合はarticles）の各行に対して、右側のテーブル（この場合はtagsとarticle_tagの結合結果）でマッチする行が1つでも存在する場合に、左側のテーブルの行を結果に含めるタイプの結合です。
つまり、具体的なマッチする行の詳細は結果には含まれず、マッチする行が存在するかどうかだけが考慮されます。

Nested Loopの結合しないバージョンと言えると思います。結果として欲しいのはarticlesだけなので、Semi Join (セミ結合、フィルタリング結合)を行っています。
つまり2つ目のテーブルはフィルタリングをかけるためだけに使用するということです。

`Nested Loop Semi Join`動作に関する説明

整理しやすいように言葉で整理すると、以下のように表現できます。

->  5: Nested Loop Semi Join
    ->  1: `articles`テーブルを`tenant_id`で検索
    ->  4: Nested Loop
        ->  `tags`テーブルと`article_tag`テーブルを下記条件で結合
                  (article_id = articles.id) AND (tag_id = tags.id)

Nested Loop Semi Joinの部分の動作を追っていきたいと思います。

外側のテーブル（1:の結果、articlesテーブル）の最初の行を取得します。
(2:の結果、 tagsテーブル)の最初の行を取得します。
article_tagテーブルの全行をスキャンし、結合条件(tag_id = tags.idかつarticle_id = articles.id)に合致する行を探します。
結合条件に合致した行のarticlesテーブルのみを結果として出力します。
（2:の結果、tagsテーブル）の次の行を取得し、3と4のステップを繰り返します。全行に対してこの処理を繰り返します。
(1:の結果、articlesテーブル)の次の行を取得し、3,4,5のステップを繰り返します。全行に対してこの処理を繰り返します。

少しわかりにくいですが、基本的にはNested Loopと同じです。
Nested Loop Semi Join自体は明示的に結合条件を持たないようで、内部で行われるテーブルスキャンやフィルタリングの条件が、事実上その結合条件として機能するようです。

正確にこのような処理だと断言はできないのですが、自分的にはこの流れだと認識しています。

6つめ

Limit  (cost=0.43..24.51 rows=1 width=1661)

これはLimitです。

結論

PostgreSQLではwhereHas()を使用しても相関サブクエリとならないことがわかった。
ちなみに相関サブクエリの場合は、InitPlanやSubPlanと表示されるらしいです。

自分自身EXPLAINあまりしたことなかったのでとても勉強になりました。

ちなみにhashとか出てくると、ハッシュテーブルをメモリ内に作って処理を行うのでメモリに乗らなくなるとディスクIOが発生する可能性があるのでデータのサイズに気をつける必要があるようです。

Innovator Japan Engineers’ Blog

whereHas()を通してEXPLAIN入門してみる

はじめに

対象のクエリ

EXPLAINしてみる

cost

rows

width

順番に関しての説明

`EXPLAIN`の内容に関しての説明

1つめ

行ごとの説明

一行目

二行目

補足

2つめ

行ごとの説明

一行目

二行目

三行目

補足

3つめ

行ごとの説明

一行目

二行目

補足

4つ目

行ごとの説明

一行目

`Nested Loop`に関しての詳細な説明

5つめ

`Semi Join`に関しての説明

`Nested Loop Semi Join`動作に関する説明

6つめ

結論

参考記事など

はじめに

対象のクエリ

EXPLAINしてみる

cost

rows

width

順番に関しての説明

EXPLAINの内容に関しての説明

1つめ

行ごとの説明

一行目

二行目

補足

2つめ

行ごとの説明

一行目

二行目

三行目

補足

3つめ

行ごとの説明

一行目

二行目

補足

4つ目

行ごとの説明

一行目

Nested Loopに関しての詳細な説明

5つめ

Semi Joinに関しての説明

Nested Loop Semi Join動作に関する説明

6つめ

結論

参考記事など

`EXPLAIN`の内容に関しての説明

`Nested Loop`に関しての詳細な説明

`Semi Join`に関しての説明

`Nested Loop Semi Join`動作に関する説明