如何筛选具有多次通过关系的SQL结果

我很好奇 众所周知，好奇心以杀死猫而闻名。

那么，哪一种最快的方法是给猫皮呢？ 此测试的精确蒙皮环境：

Debian Squeeze上的PostgreSQL 9.0，具有不错的RAM和设置。 6.000名学生，24.000个俱乐部会员资格（数据从类似的数据库复制而来，带有真实生活的数据。） 从问题中的命名模式稍微转移：“ student.id是” student.stud_id和“ club.id在club.club_id这里”。 我在该线程中以查询的作者命名，在其中有两个索引。 我运行了所有查询几次以填充缓存，然后使用EXPLAIN ANALYZE选择了5个最好的查询。 相关指标（应该是最佳的-只要我们不具备要查询哪些俱乐部的知识）：

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树索引用于涉及该索引列的任何子集的查询条件，但是当前导（最左边）列受到约束时，该索引效率最高。

结果： EXPLAIN ANALYZE的总运行时间。

1）马丁2：44.594毫秒 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毫秒 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.735毫秒 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毫秒 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.181毫秒 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; 后三个的表现几乎相同。4）和5）得出相同的查询计划。

后期添加： 花式SQL，但性能跟不上。

7）超级立方体1：148.649毫秒 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毫秒 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
) ); 不出所料，这两个的表现几乎相同。查询计划会导致表扫描，而计划者在这里找不到使用索引的方法。

9）Wildplasser 1：49.849毫秒 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的性能不错。非常奇特的查询计划。 同样，有趣的是9.1如何处理这个问题。我将很快将此处使用的数据库集群升级到9.1。也许我会重新运行整个shebang ...

10）Wildplasser 2：36.986毫秒 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）超级立方体3：101.482毫秒 另一个后期添加@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毫秒 难以置信，但这是另一个全新的变体。我认为有超过两个成员的潜力，但它也仅以两个而跻身顶级猫之列。

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 )来源：stack overflow