Come filtrare i risultati SQL in una relazione has-many-through

Supponendo che io abbia i tavoli student , club e student_club :

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

Voglio sapere come trovare tutti gli studenti nel club di calcio (30) e di baseball (50).
Anche se questa query non funziona, è la cosa più vicina che ho finora:

 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 

Ero curioso. E come tutti sappiamo, la curiosità ha la reputazione di uccidere i gatti.

Quindi, qual è il modo più veloce per dare la pelle a un gatto?

Il preciso ambiente di skin cat per questo test:

  • PostgreSQL 9.0 su Debian Squeeze con RAM e impostazioni decenti.
  • 6.000 studenti, 24.000 membri del club (dati copiati da un database simile con dati reali).
  • Leggero svantaggio dallo schema di denominazione nella domanda: student.id è student.stud_id e club.id è club.club_id qui.
  • Ho chiamato le query dopo il loro autore in questa discussione, con un indice dove ce ne sono due.
  • Ho eseguito tutte le query un paio di volte per popolare la cache, quindi ho scelto il meglio di 5 con EXPLAIN ANALYZE.
  • Indici rilevanti (dovrebbe essere l’optimum – a patto che manchino le conoscenze preliminari su quali club saranno interrogati):

     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 non è richiesto dalla maggior parte delle query qui.
    Le chiavi primarie implementano automaticamente indici univoci In PostgreSQL.
    L’ultimo indice è quello di recuperare questa nota mancanza di indici multi-colonna su PostgreSQL:

Un indice B-tree multicolonna può essere utilizzato con condizioni di query che coinvolgono qualsiasi sottoinsieme delle colonne dell’indice, ma l’indice è più efficiente quando vi sono vincoli sulle colonne iniziali (più a sinistra).

risultati:

Totale runtime da SPIEGARE ANALIZZA.

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; 

Gli ultimi tre si comportano praticamente allo stesso modo. 4) e 5) producono lo stesso piano di query.

Late Additions:

Fancy SQL, ma le prestazioni non possono tenere il passo.

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

Come previsto, questi due si comportano quasi allo stesso modo. I risultati del piano di query nelle scansioni di tabelle, il pianificatore non trova un modo per utilizzare gli indici qui.


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; 

Fancy SQL, prestazioni decenti per un CTE. Piano di query molto esotico.
Di nuovo, sarebbe interessante il modo in cui 9.1 gestisce questo. Ho intenzione di aggiornare presto il cluster db usato qui a 9.1. Forse riprenderò l’intera shebang …


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

Variante CTE della query 2). Sorprendentemente, può risultare in un piano di query leggermente diverso con gli stessi identici dati. Ho trovato una scansione sequenziale sullo student , in cui la variante subquery ha utilizzato l’indice.


11) ypercube 3: 101.482 ms

Un’altra aggiunta in ritardo @ypercube. È davvero sorprendente, quanti modi ci sono.

 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 di ypercube) è in realtà solo l’approccio inverso di torsione mentale di questa variante più semplice, che era anche mancante. Esegue quasi la velocità dei migliori gatti.

 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

Difficile da credere, ma ecco un’altra variante davvero nuova. Vedo il potenziale per più di due membri, ma si colloca anche tra i migliori gatti con solo due.

 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 ) 

Numero dinamico di iscrizioni al club

In altre parole: numero variabile di filtri. Questa domanda ha richiesto esattamente due affiliazioni ai club. Ma molti casi d’uso devono prepararsi per un numero variabile.

Discussione dettagliata in questa risposta successiva correlata:

  • Utilizzando la stessa colonna più volte nella clausola WHERE
 SELECT s.* FROM student s INNER JOIN student_club sc_soccer ON s.id = sc_soccer.student_id INNER JOIN student_club sc_baseball ON s.id = sc_baseball.student_id WHERE sc_baseball.club_id = 50 AND sc_soccer.club_id = 30 
 select * from student where id in (select student_id from student_club where club_id = 30) and id in (select student_id from student_club where club_id = 50) 

Se vuoi solo student_id allora:

  Select student_id from student_club where club_id in ( 30, 50 ) group by student_id having count( student_id ) = 2 

Se hai bisogno anche del nome dello studente, allora:

 Select student_id, name from student s where exists( select * from student_club sc where s.student_id = sc.student_id and club_id in ( 30, 50 ) group by sc.student_id having count( sc.student_id ) = 2 ) 

Se hai più di due fiori in una tabella club_selezione, allora:

 Select student_id, name from student s where exists( select * from student_club sc where s.student_id = sc.student_id and exists( select * from club_selection cs where sc.club_id = cs.club_id ) group by sc.student_id having count( sc.student_id ) = ( select count( * ) from club_selection ) ) 
 SELECT * FROM student WHERE id IN (SELECT student_id FROM student_club WHERE club_id = 30 INTERSECT SELECT student_id FROM student_club WHERE club_id = 50) 

O una soluzione più generale più facile da estendere ai club e che evita INTERSECT (non disponibile in MySQL) e IN (come le prestazioni di questo fa schifo in MySQL )

 SELECT s.id, s.name FROM student s join student_club sc ON s.id = sc.student_id WHERE sc.club_id IN ( 30, 50 ) GROUP BY s.id, s.name HAVING COUNT(DISTINCT sc.club_id) = 2 

Un altro CTE. Sembra pulito, ma probabilmente genererà lo stesso piano di un groupby in una subquery normale.

 WITH two AS ( SELECT student_id FROM tmp.student_club WHERE club_id IN (30,50) GROUP BY student_id HAVING COUNT(*) > 1 ) SELECT st.* FROM tmp.student st JOIN two ON (two.student_id=st.id) ; 

Per coloro che vogliono testare, una copia del mio genera testdata thingy:

 DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp; CREATE TABLE tmp.student ( id INTEGER NOT NULL PRIMARY KEY , sname VARCHAR ); CREATE TABLE tmp.club ( id INTEGER NOT NULL PRIMARY KEY , cname VARCHAR ); CREATE TABLE tmp.student_club ( student_id INTEGER NOT NULL REFERENCES tmp.student(id) , club_id INTEGER NOT NULL REFERENCES tmp.club(id) ); INSERT INTO tmp.student(id) SELECT generate_series(1,1000) ; INSERT INTO tmp.club(id) SELECT generate_series(1,100) ; INSERT INTO tmp.student_club(student_id,club_id) SELECT st.id , cl.id FROM tmp.student st, tmp.club cl ; DELETE FROM tmp.student_club WHERE random() < 0.8 ; UPDATE tmp.student SET sname = 'Student#' || id::text ; UPDATE tmp.club SET cname = 'Soccer' WHERE id = 30; UPDATE tmp.club SET cname = 'Baseball' WHERE id = 50; ALTER TABLE tmp.student_club ADD PRIMARY KEY (student_id,club_id) ; 

Quindi c’è più di un modo per pelle un gatto .
Ne aggiungerò altri due per renderlo, beh, più completo.

1) GRUPPO prima, UNISCITI più tardi

Supponendo che un modello di dati sano dove (student_id, club_id) è unico in student_club . La seconda versione di Martin Smith è un po ‘simile, ma si unisce prima, dopo i gruppi. Questo dovrebbe essere più veloce:

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

2) ESISTE

E, naturalmente, c’è il classico EXISTS . Simile alla variante di Derek con IN . Semplice e veloce. (In MySQL, questo dovrebbe essere un po ‘più veloce della variante con IN ):

 SELECT s.id, s.name FROM student s WHERE EXISTS (SELECT 1 FROM student_club WHERE student_id = s.student_id AND club_id = 30) AND EXISTS (SELECT 1 FROM student_club WHERE student_id = s.student_id AND club_id = 50); 

Dal momento che nessuno ha aggiunto questa (classica) versione:

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

o simili:

 SELECT s.* 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.student_id = s.id AND sc.club_id = c.club_id ) ) 

Ancora una prova con un approccio leggermente diverso. Ispirato da un articolo in Explain Extended: Attributi multipli in una tabella EAV: GROUP BY vs. NOT EXISTS :

 SELECT s.* FROM student_club AS sc JOIN student AS s ON s.student_id = sc.student_id WHERE sc.club_id = 50 --- one option here AND NOT EXISTS ( SELECT * FROM ( SELECT 30 AS club_id --- all the rest in here --- as in previous query ) AS c WHERE NOT EXISTS ( SELECT * FROM student_club AS scc WHERE scc.student_id = sc.id AND scc.club_id = c.club_id ) ) 

Un altro approccio:

 SELECT s.stud_id FROM student s EXCEPT SELECT stud_id FROM ( SELECT s.stud_id, c.club_id FROM student s CROSS JOIN (VALUES (30),(50)) c (club_id) EXCEPT SELECT stud_id, club_id FROM student_club WHERE club_id IN (30, 50) -- optional. Not needed but may affect performance ) x ; 
 WITH RECURSIVE two AS ( SELECT 1::integer AS level , student_id FROM tmp.student_club sc0 WHERE sc0.club_id = 30 UNION SELECT 1+two.level AS level , sc1.student_id FROM tmp.student_club sc1 JOIN two ON (two.student_id = sc1.student_id) WHERE sc1.club_id = 50 AND two.level=1 ) SELECT st.* FROM tmp.student st JOIN two ON (two.student_id=st.id) WHERE two.level> 1 ; 

Ciò sembra funzionare ragionevolmente bene, dal momento che la scansione CTE evita la necessità di due sottoquery separate.

C’è sempre un motivo per abusare delle query ricorsive!

(A proposito: mysql non sembra avere query ricorsive)

Piani di query diversi nella query 2) e 10)

Ho provato in una vita reale db, quindi i nomi differiscono dalla lista dei gatti. È una copia di backup, quindi nulla è cambiato durante tutte le esecuzioni di test (tranne le modifiche minori ai cataloghi).

Domanda 2)

 SELECT a.* FROM ef.adr a JOIN ( SELECT adr_id FROM ef.adratt WHERE att_id IN (10,14) GROUP BY adr_id HAVING COUNT(*) > 1) t using (adr_id); Merge Join (cost=630.10..1248.78 rows=627 width=295) (actual time=13.025..34.726 rows=67 loops=1) Merge Cond: (a.adr_id = adratt.adr_id) -> Index Scan using adr_pkey on adr a (cost=0.00..523.39 rows=5767 width=295) (actual time=0.023..11.308 rows=5356 loops=1) -> Sort (cost=630.10..636.37 rows=627 width=4) (actual time=12.891..13.004 rows=67 loops=1) Sort Key: adratt.adr_id Sort Method: quicksort Memory: 28kB -> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=12.386..12.710 rows=67 loops=1) Filter: (count(*) > 1) -> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.245..5.958 rows=2811 loops=1) Recheck Cond: (att_id = ANY ('{10,14}'::integer[])) -> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.217..0.217 rows=2811 loops=1) Index Cond: (att_id = ANY ('{10,14}'::integer[])) Total runtime: 34.928 ms 

Domanda 10)

 WITH two AS ( SELECT adr_id FROM ef.adratt WHERE att_id IN (10,14) GROUP BY adr_id HAVING COUNT(*) > 1 ) SELECT a.* FROM ef.adr a JOIN two using (adr_id); Hash Join (cost=1161.52..1261.84 rows=627 width=295) (actual time=36.188..37.269 rows=67 loops=1) Hash Cond: (two.adr_id = a.adr_id) CTE two -> HashAggregate (cost=450.87..488.49 rows=627 width=4) (actual time=13.059..13.447 rows=67 loops=1) Filter: (count(*) > 1) -> Bitmap Heap Scan on adratt (cost=97.66..394.81 rows=2803 width=4) (actual time=0.252..6.252 rows=2811 loops=1) Recheck Cond: (att_id = ANY ('{10,14}'::integer[])) -> Bitmap Index Scan on adratt_att_id_idx (cost=0.00..94.86 rows=2803 width=0) (actual time=0.226..0.226 rows=2811 loops=1) Index Cond: (att_id = ANY ('{10,14}'::integer[])) -> CTE Scan on two (cost=0.00..50.16 rows=627 width=4) (actual time=13.065..13.677 rows=67 loops=1) -> Hash (cost=384.68..384.68 rows=5767 width=295) (actual time=23.097..23.097 rows=5767 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1153kB -> Seq Scan on adr a (cost=0.00..384.68 rows=5767 width=295) (actual time=0.005..10.955 rows=5767 loops=1) Total runtime: 37.482 ms 

@ erwin-brandstetter Per favore, confronta questo:

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

È come il numero 6) di @sean, solo più pulito, immagino.

 -- EXPLAIN ANALYZE WITH two AS ( SELECT c0.student_id FROM tmp.student_club c0 , tmp.student_club c1 WHERE c0.student_id = c1.student_id AND c0.club_id = 30 AND c1.club_id = 50 ) SELECT st.* FROM tmp.student st JOIN two ON (two.student_id=st.id) ; 

Il piano di query:

  Hash Join (cost=1904.76..1919.09 rows=337 width=15) (actual time=6.937..8.771 rows=324 loops=1) Hash Cond: (two.student_id = st.id) CTE two -> Hash Join (cost=849.97..1645.76 rows=337 width=4) (actual time=4.932..6.488 rows=324 loops=1) Hash Cond: (c1.student_id = c0.student_id) -> Bitmap Heap Scan on student_club c1 (cost=32.76..796.94 rows=1614 width=4) (actual time=0.667..1.835 rows=1646 loops=1) Recheck Cond: (club_id = 50) -> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.36 rows=1614 width=0) (actual time=0.473..0.473 rows=1646 loops=1) Index Cond: (club_id = 50) -> Hash (cost=797.00..797.00 rows=1617 width=4) (actual time=4.203..4.203 rows=1620 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 57kB -> Bitmap Heap Scan on student_club c0 (cost=32.79..797.00 rows=1617 width=4) (actual time=0.663..3.596 rows=1620 loops=1) Recheck Cond: (club_id = 30) -> Bitmap Index Scan on sc_club_id_idx (cost=0.00..32.38 rows=1617 width=0) (actual time=0.469..0.469 rows=1620 loops=1) Index Cond: (club_id = 30) -> CTE Scan on two (cost=0.00..6.74 rows=337 width=4) (actual time=4.935..6.591 rows=324 loops=1) -> Hash (cost=159.00..159.00 rows=8000 width=15) (actual time=1.979..1.979 rows=8000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 374kB -> Seq Scan on student st (cost=0.00..159.00 rows=8000 width=15) (actual time=0.093..0.759 rows=8000 loops=1) Total runtime: 8.989 ms (20 rows) 

Quindi sembra ancora voler la scansione seq su studente.

 SELECT s.stud_id, s.name FROM student s, ( select x.stud_id from student_club x JOIN student_club y ON x.stud_id = y.stud_id WHERE x.club_id = 30 AND y.club_id = 50 ) tmp_tbl where tmp_tbl.stud_id = s.stud_id ; 

Uso della variante più veloce (Mr. Sean nel grafico Mr. Brandstetter). Può essere una variante con un solo join solo alla matrice student_club ha il diritto di vivere. Quindi, la query più lunga avrà solo due colonne da calcolare, l’idea è di rendere la query sottile.