Ne kadar çok-bir ilişki SQL sonuçlarını süzmek için bir yer

Tablolar student, club student_club var varsayarsak:

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

Hem futbol (30) ve (50) beyzbol kulübü öğrencileri bulmak için bilmek istiyorum.
Bu sorgu işe yaramıyor olsa da, şimdiye kadar bana en yakın şey:

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

CEVAP

15 EKİM 2011, CUMARTESİ

Merak ettim. Ve hepimizin bildiği gibi, merak kedileri öldürmek için bir üne sahiptir.

Bir işi yapmanın en hızlı yolu nedir?

Kesin kedi-stiller bu test ortamı için:

PostgreSQL 9.0RAM ve ayarları düzgün ile Sıkmak Debian.
6.000 öğrenciler, 24.000 kulüp üyelikleri (veri gerçek yaşam verileri ile benzer bir veritabanı.)
Soru adlandırma şeması hafif saptırma: 21* *student.stud_id club.id club.club_id burada.
İki bulunduğu dizin ile bu konuyu kendi yazar sonra sorguları adını verdim.
Birkaç kez tüm sorgular önbelleği doldurmak için koştu, sonra da öğrenir ve ANALİZ ile 5 en iyisini seçtim.
(Optimum - kulüpleri sorgulanmasını hangi ön bilgi eksikliği olarak) ilgili dizinler:
```
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 sorguların çoğu burada gerekli değildir.
Birincil anahtarlar, benzersiz dizinleri PostgreSQL otomatik Olarak uygular.
Son dizin PostgreSQL multi-column indexes bu bilinen eksiklikleri telafi etmek için

B-tree indeksleme sorgu koşulları ile kullanılan bir sütunlu dizin sütunları herhangi bir alt kümesini içerir, ama bu dizin en. önde gelen kısıtlamaları olduğunda etkin (soldaki) sütunlar.

Sonuçlar:

AÇIKLAMAMA toplam çalışma zamanları, ANALİZ eder.

1) Martin 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) Erwin 1: 33.217 ms

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) Martin 1: 31.735 ms

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) Derek: 2.287 ms

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) Erwin 2: 2.181 ms

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) Sean: 2.043 ms

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;

Son üç hemen hemen aynı gerçekleştirin. 4) ve 5) aynı query plan neden.

Eklemeler:

Süslü SQL, ancak gösteri devam edemez.

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  
      )
   );

Beklendiği gibi, bu ikisi neredeyse aynı gerçekleştirin. Tablo sorgu planı sonuçları, planlayıcısı dizinler burada kullanmak için bir yol bulmak yok tarar.

9) wildplasser 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;

Süslü SQL, CTE için iyi bir performans. Çok egzotik bir sorgu planı.
Yine, 9.1 bu işler ne kadar ilginç olurdu. 9.1 için db küme burada kullanılan yakında yükseltmek için gidiyorum. Belki de hepsi birden tekrar test edeceğim ...

10) wildplasser 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);

Sorgu 2 CTE varyant). Şaşırtıcı bir şekilde, aynı veri ile biraz farklı sorgu planları neden olabilir. Sorgu değişken dizin kullanılan bir sıralı student, tarama buldum.

11) ypercube 3: 101.482 ms

Başka bir geç ayrıca @ypercube. Kaç tane var gerçekten şaşırtıcı.

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) erwin 3: 2.377 ms

@11 ypercube) aslında sadece bu basit varyant zihin-büküm ters bir yaklaşımdır, o da hala kayıp. Neredeyse hızlı top kedi. olarak gerçekleştirir

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) erwin 4: 2.375 ms

İnanması zor ama, bir, gerçekten yeni bir türevi. İkiden fazla Üyelikleri için potansiyel görüyorum, ama aynı zamanda sadece iki üst kediler arasında yer alıyor.

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
   )

Bunu Paylaş: