[解決済み] has-many-through関係でSQLの結果をフィルタリングする方法

質問

以下のテーブルがあるとします。 student , club そして student_club :

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

サッカー部(30人)と野球部(50人)の両方の生徒を全員見つける方法を知りたいです。

このクエリは動作しませんが、今のところ最も近いものです。

SELECT student.*
FROM   student
INNER  JOIN student_club sc ON student.id = sc.student_id
LEFT   JOIN club c ON c.id = sc.club_id
WHERE  c.id = 30 AND c.id = 50

どのように解決するのですか？

私は好奇心が強かったのです。そして周知のように、好奇心は猫を殺すという評判がある。

では、猫の皮を剥ぐのに一番手っ取り早い方法はどれでしょう？

このテストのための猫の皮を剥ぐ環境です。

• PostgreSQL 9.0 まともな RAM と設定の Debian Squeeze 上で。
• 6.000 人の学生、24.000 人のクラブ会員 (実際のデータを含む同様のデータベースからコピーしたデータ。)
• 質問のネーミングスキーマからの若干の転用。 student.id は student.stud_id であり club.id は club.club_id ここに
• このスレッドで、クエリに作者の名前を付けました。
• すべてのクエリを数回実行してキャッシュを生成し、5つのうち最も良いものを EXPLAIN ANALYZE .
• 関連するインデックス (最適であるべきです - どのクラブがクエリされるかの事前知識がない限り)。

ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club       ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);

club_pkey はここでのほとんどのクエリでは必要ありません。

主キーはPostgreSQLでは自動的にユニークインデックスを実装します。

最後のインデックスは、この既知の欠点を補うために マルチカラムインデックス をPostgreSQL上で使用することができます。

<ブロッククオート

複数列のB-treeインデックスは、インデックスの任意の列のサブセットを含む問い合わせ条件と共に使用することができます。 しかし、このインデックスが最も効率的なのは、先頭（左端）の列に制約がある場合です。 しかし、先頭（左端）の列に制約がある場合、インデックスは最も効率的です。

結果

以下の実行時間の合計 EXPLAIN ANALYZE .

1) マーティン2: 44.594 ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

2）エルヴィン1号：33.217ms

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

3）マーチン1：31.735ms

SELECT s.stud_id, s.name
FROM   student s
WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30

   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50
   );

4）デレク：2.287ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

5）エルヴィン2号：2.181ms

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

6）ショーン：2.043ミリ秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

最後の3つはほとんど同じように実行されます。4)と5)は同じクエリプランになります。

遅れての追加

派手なSQLだが、パフォーマンスが追いつかない。

7) ypercube 1: 148.649 ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c 
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

8) ypercube 2: 147.497 ms

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id  
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

予想通り、これら2つのパフォーマンスはほとんど同じです。クエリプランの結果はテーブルスキャンで、プランナはここでインデックスを使用する方法を見つけられませんでした。

9) ワイルドプラッサー 1: 49.849 ms

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

派手なSQLで、CTEとしてはまずまずのパフォーマンス。非常にエキゾチックなクエリプランです。

10) ワイルドプラッサー 2: 36.986 ms

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

クエリ2)のCTEバリアントです。意外なことに、全く同じデータで少し異なるクエリプランになることがあります。私は、シーケンシャルスキャンで student でのシーケンシャルスキャンを見つけました。

11) ypercube 3: 101.482 ms

またまた遅ればせながらypercubeを追加しました。何通りもあるのが本当にすごいです。

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   );

12）アーウィン3：2.377ミリ秒

ypercubeの11)は、実はこの単純なバリエーションと逆のアプローチであり、これもまだ見つかっていません。トップの猫とほぼ同じ速度で実行されます。

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   );

13）アーウィン4：2.375ミリ秒

信じがたいことですが、ここにもうひとつの、本当に新しいバリエーションがあります。2 つ以上のメンバーシップを持つ可能性がありますが、2 つだけのメンバーシップでも上位にランクインしています。

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   );

クラブメンバーシップの動的な数

言い換えれば、フィルターの数を変えることです。この質問では、まさに 2 のクラブ会員になります。しかし、多くのユースケースは、数が変化しても用意しなければなりません。ご覧ください。

• WHERE句で同じカラムを複数回使用する場合