あなたのクエリは遅い。PostgreSQLでJOINしたらDISTINCT ONを使え！

はじめに

我々カウンターワークスが開発しているアプリケーションの一つはPostgreSQLをRDBMSとして利用しています。
PostgreSQLはその柔軟性と強力な機能により、複雑なクエリも容易に実行できます。しかし、大量のデータを扱う際には、クエリのパフォーマンスが低下することがあります。特に、複数のテーブルをJOINした後にDISTINCTを適用する場合、データ量に比例して処理時間が遅くなることがあります。

実際に弊社のプロダクトでも、ある箇所で複数のテーブルをJOINした後に対象の列を指定せずにDISTINCTを行っている箇所があり、レスポンスの生成に30秒程度かかったことが判明しました(すでに修正済みです！)。

本記事では、このようにデータ量に比例してDISTINCTの処理時間が長くなってしまう問題の原因と対策を解説していこうと思います。

JOINした後になんとなくDISTINCTしてませんか？

Webアプリケーションでは、複数のテーブルを結合してデータを取得する場面が多くあります。
例として飲食店の予約注文システムを作るとしましょう。

• 顧客は複数の飲食店で予約することができる
• 1つの予約で複数の注文ができる
• 1つの注文で複数のメニューを頼むことができる
  顧客は customers、予約は reservations、注文は orders、メニューは menus というテーブルに格納されているとします。
  これらを結合して、顧客ごとに各予約内で注文したことがあるメニュー全てを取得するユースケースがあるとします。
  ここで重複したメニューのデータを排除するためにDISTINCTを使用する場合、クエリは以下のようになります

SELECT DISTINCT customers.*, menus.*
FROM customers
JOIN reservations ON customers.id = reservations.customer_id
JOIN orders ON reservations.id = orders.reservation_id
JOIN menus ON orders.id = menus.order_id;

パフォーマンスの問題

上記のようなクエリは、データ量が少ないうちは問題なく素早く動作します。
しかし、テーブルのサイズが大きくなったり、JOINするテーブル数が増えるとパフォーマンスが極端に悪化します。

なぜこのようなパフォーマンスの問題が発生するのかを理解するためには、データベースにおける中間結果について知る必要があります。
データベースにおける中間結果とは、最終的なクエリを生成するまでに一時的に生成されるデータセットです。
データセットとはクエリの実行によって生成される列と行の集合のことを指します。
特にJOINやサブクエリのような複雑な操作が実行される際、データベースエンジンは処理の段階ごとに中間結果を生成し、それを次の処理ステップに渡します。

SELECT DISTINCT customers.*, menus.*
FROM customers
JOIN reservations ON customers.id = reservations.customer_id
JOIN orders ON reservations.id = orders.reservation_id
JOIN menus ON orders.id = menus.order_id;

もう一度このクエリを見てみましょう。
このクエリの実行過程は四つに分けることができます。

1. まずcustomersテーブルとreservationsテーブルがJOINされ、第一の中間結果が作成されます
2. 次にこの中間結果とordersテーブルがJOINされ、第二の中間結果が作成されます
3. 最後にこの中間結果とmenusテーブルがJOINされ、最終結果が生成されます
4. 最終結果に対してDISTINCTが適用され、重複を排除された結果が生成されます

これがデータベースにおける中間結果の生成過程です。

さて、中間結果の概念を理解したところで、なぜDISTINCTを使ったクエリがパフォーマンス問題を引き起こすのか、その具体的な原因について説明していきましょう。
DISTINCTを使用する際のパフォーマンス低下の主な原因は次のとおりです。

1. データ量の増加

• JOINによって生成される中間結果の行数が膨大になります。
  例えば、1,000件のユーザーと10,000件の注文を結合すると、中間結果は1,000 × 10,000 = 1,000,000件となる可能性があります。
• この中間結果に対してDISTINCTを適用すると、すべての行を比較する必要があるため処理時間が膨大になります。

2. メモリ使用量の増加

• 中間結果のデータ量が大きくなると、PostgreSQLの作業メモリ（work_mem）を超えてしまい、一時ファイルにデータを書き出す「Temp落ち」が発生します。
• ディスクI/Oが発生するため、処理が著しく遅くなります。

3. フルスキャンの発生

• DISTINCTを適用する際、主キー以外のカラムが選択されていると、PostgreSQLはテーブル全体をスキャンします。
• 特に SELECT DISTINCT * のような形式では、すべてのカラムを比較対象とするため、非常に効率が悪くなります。

ではどうするか

PostgreSQLでは、DISTINCTのパフォーマンス問題を解決するためのいくつかの手法がありますが、以下に代表的な方法を3つ紹介します。

1. サブクエリを使う
2. DISTINCT ONを使う
3. DISTINCTするカラムを指定する

サブクエリを使う場合

まずサブクエリを使う場合ですが、DISTINCTを適用する前に必要なデータを絞り込むことで中間結果のサイズを小さくすることができます。

SELECT customers.*, menus.*
FROM (
  SELECT DISTINCT customers.id AS customer_id, reservations.id AS reservation_id, 
                  orders.id AS order_id, menus.id AS menu_id
  FROM customers
  JOIN reservations ON customers.id = reservations.customer_id
  JOIN orders ON reservations.id = orders.reservation_id
  JOIN menus ON orders.id = menus.order_id
) AS distinct_keys
JOIN customers ON distinct_keys.customer_id = customers.id
JOIN reservations ON distinct_keys.reservation_id = reservations.id
JOIN orders ON distinct_keys.order_id = orders.id
JOIN menus ON distinct_keys.menu_id = menus.id;

メリット

• 中間結果のサイズが小さくなるため、DISTINCTの処理が高速化されます。
• 処理する際にメモリに乗るデータセットが小さくなるため、Temp落ちの可能性が減少します。
• DISTINCTする対象のキーが増えないため、パフォーマンスが向上します。

DISTINCT ONを使う場合

PostgreSQLには、DISTINCT ONという機能があります。これは指定したカラムに基づいて重複を排除するものです。
一番の特徴はDISTINCTと異なり、特定のテーブルのカラムのみを対象して重複排除ができるため、JOINしたテーブルが多いほどDISTINCTと比較して、パフォーマンスが向上します。

SELECT DISTINCT ON (customers.id, menus.id) customers.*, menus.*
FROM customers
JOIN reservations ON customers.id = reservations.customer_id
JOIN orders ON reservations.id = orders.reservation_id
JOIN menus ON orders.id = menus.order_id
ORDER BY customers.id, reservations.id, orders.id, menus.id;

メリット

• 特定のテーブルの主キーだけを指定して重複排除できるので、処理が軽くなります。
• 比較対象のカラムが明示的に指定されるため、PostgreSQLのオプティマイザが効率的な実行計画を立てやすくなります。

DISTINCTするカラムを指定する

DISTINCT関数で特定カラムを指定して重複を排除したのち、全カラムを取得することは理論的には可能です。
このアプローチでは、重複排除したいカラムだけをDISTINCTで指定し、そのキーを使って再度元のテーブルを結合します。

以下のようなアプローチが考えられますが、DISTINCT ONと比較してパフォーマンスが悪くなる傾向があり、クエリも複雑になるためあまりおすすめはできません。
興味のある人のみ以下の例を参考にしてください。

サブクエリとJOINを用いる方法

SELECT customers.* menus.*
FROM (
    SELECT DISTINCT customers.id AS customer_id, orders.id AS order_id
    FROM customers
    JOIN reservations ON customers.id = reservations.customer_id
    JOIN orders ON reservations.id = orders.reservation_id
    JOIN menus ON orders.id = menus.order_id
) AS distinct_keys
JOIN customers ON distinct_keys.customer_id = customers.id
JOIN reservations ON customers.id = reservations.customer_id
JOIN orders ON reservations.id = orders.reservation_id AND orders.id = distinct_keys.order_id
JOIN menus ON orders.id = menus.order_id;

DISTINCTとDISTINCT ONの違い

DISTINCTはカラムを指定しない限りは、選択されたすべてのカラムの組み合わせに対して重複を排除します。一方、DISTINCT ONは指定したカラムのみに基づいて重複を排除し、その他のカラムは最初に見つかった行の値が使用されます。
ただ先ほどの具体例でも紹介しましたが、純粋なDISTINCT関数を用いて重複排除を行い全てのカラムを返すには工夫が必要です。

おわりに

この記事では、PostgreSQLにおけるDISTINCTの使用に関するパフォーマンス問題とその解決策について解説しました。

• JOINした結果に対する安易なDISTINCT使用は危険
  • 中間結果の肥大化によりレスポンスが著しく遅延することがある
  • メモリ不足「Temp落ち」でさらにパフォーマンス悪化の可能性
• PostgreSQLで重複排除を行う場合はDISTINCT ONを積極的に活用すべき
  • クエリを簡潔に保ちながらパフォーマンスを大幅向上

参考文献

https://www.postgresql.jp/docs/9.6/queries-select-lists.html