SQLのWITH句で処理速度を最適化！使いまわしによるパフォーマンス低下を防ぐ方法

【SQL】WITHの使いまわしに 気を付けよう

目次

1. WITHって何？
2. 問題点（WITHの使いまわし）
3. 問題点の改善策
4. 改善前と改善後のコスト比較
5. WITHを使用する際のベストプラクティス
6. まとめ

---

1. WITHって何？

• 一時的な名前付きのサブクエリ。
• 複数のサブクエリをシンプルにまとめることができる。
• 見やすくなる。
• 同じ結果を繰り返し参照できる。

---

2. 記載方法の比較

• サブクエリ:

  SELECT p.first_name, p.last_name, a.street, a.city
  FROM People p
  INNER JOIN (
    SELECT person_id, street, city 
    FROM Addresses
    WHERE city = 'New York'
  ) a
  ON p.person_id = a.person_id;
  
  
  

• WITH句:

  WITH Address_CTE AS (
      SELECT person_id, street, city
      FROM Addresses
      WHERE city = 'New York'
  )
  SELECT p.first_name, p.last_name, a.street, a.city
  FROM People p
  INNER JOIN Address_CTE a
  ON p.person_id = a.person_id;
  
  
  

3. 問題点（WITHの使いまわし）

• WITHの結果が一時的にディスクに保存される（マテリアライズ）

  • ディスクアクセスを行うと処理が遅くなる場合がある。

• 再利用が非効率

  • WITHの結果は一時的に固定化される。
  • WITHで使用されたINDEXは、メインクエリで異なる条件でフィルタリングした際に適用されない場合があり、遅延する。

• 例（異なる条件でWITHを使いまわす）:

  WITH ADR AS (SELECT person_id, birth_date FROM Addresses)
  SELECT pep.first_name, adr1.birth_date, adr2.birth_date
  FROM People pep
  INNER JOIN ADR adr1 ON pep.person_id = adr1.person_id
  INNER JOIN ADR adr2 ON pep.birth_date = adr2.birth_date
  WHERE pep.person_id = 135233;
  
  
  

4. 問題点の改善策

1. WITHを使用せずサブクエリとして記載

   • 最適な手順書（実行計画）を選択し、効率よくSQLが実行される。
   • 結果が固定されるWITHと比較し、サブクエリごとに実行計画が選択されるため、遅延が発生しにくい。

2. 一時テーブルを作成し、そこにWITHのデータをINSERT

   • 1と同じ理由で、サブクエリや一時テーブルを使用することで、同じデータを複数回使用してもディスクアクセスを減らすことができる。
   • 一時テーブルにデータを保存し、再利用することでパフォーマンスを向上させる。

改善策 1: サブクエリを使用

• サブクエリ:

  SELECT pep.first_name, adr1.address_id, adr2.birth_date
  FROM People pep
  INNER JOIN (SELECT person_id, address_id FROM Addresses WHERE person_id = 135233) adr1
  ON pep.person_id = adr1.person_id
  INNER JOIN (SELECT person_id, birth_date FROM Addresses) adr2
  ON pep.birth_date = adr2.birth_date
  WHERE pep.person_id = 135233;
  
  
  

改善策 2: 一時テーブルを作成

• 一時テーブル:

  CREATE TEMP TABLE temp_addresses AS
  SELECT    person_id,    address_id,    birth_date
  FROM     Addresses
  WHERE    person_id = 135233; 
  
  SELECT    pep.first_name,
  FROM     People pep
  INNER JOIN temp_addresses adr1 ON pep.person_id = adr1.person_id
  INNER JOIN temp_addresses adr2    ON pep.birth_date = adr2.birth_date;
  
  
  

5. 改善前と改善後のコスト比較

改善前のコスト（WITH句を使用した場合）

• コスト: 110,521
  • WITH句を使いまわすことで、マテリアライズされたデータにアクセスするためのディスクI/Oが増加し、クエリ全体のコストが高くなる。

改善後のコスト

改善策1: サブクエリを使用した場合

• コスト: 469
  • サブクエリをインライン化することで、クエリプランナーが最適なインデックスを選択し、結果としてパフォーマンスが向上。

改善策2: 一時テーブルを使用した場合

• コスト: 16,534
  • 一時テーブルにデータを保存して再利用することで、ディスクI/Oが減少し、全体のコストは下がったが、サブクエリには及ばない。

---

6. WITHを使用する際のベストプラクティス

• 異なる条件で使いまわす場合は注意:

  • WITH句を異なる条件で使いまわすと、最適なインデックスが使用されない可能性があるため、パフォーマンスが低下することがある。

• サブクエリや一時テーブルの検討:

  • 同じ結果を複数回参照する必要がある場合、サブクエリや一時テーブルを使用して、最適な実行計画が選択されるようにする。

• マテリアライズの制御:

  • PostgreSQL 12以降では、MATERIALIZEDやNOT MATERIALIZEDを使って、CTEがマテリアライズされるかどうかを制御できる。適切に制御することで、不要なディスクI/Oを避けることができる。

• インデックスの適切な使用:

  • WITH句内で使用されるテーブルやカラムに適切なインデックスを設定することで、パフォーマンスを向上させる。

---

7. まとめ

• WITH句は便利だが、使い方に注意:

  • WITH句を使うことでクエリの可読性が向上するが、使いまわしにはパフォーマンス上のリスクがある。

• パフォーマンスを意識した設計が重要:

  • サブクエリや一時テーブルの使用、マテリアライズの制御、インデックスの適用を検討し、クエリのパフォーマンスを最大化する。

• データの増加による影響を考慮:

  • 小規模なデータセットでは問題がなくても、データが増加することでパフォーマンスに影響が出ることがあるため、定期的な見直しが必要。