我很好奇 众所周知，好奇心以杀死猫而闻名。
那么，哪一种最快的方法是给猫皮呢？
此测试的精确蒙皮环境：
- *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.nameFROM student sJOIN student_club sc USING (stud_id)WHERe sc.club_id IN (30, 50)GROUP BY 1,2HAVINg COUNT(*) > 1;
2）Erwin 1：33.217毫秒
SELECt s.stud_id, s.nameFROM student sJOIN ( 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.nameFROM student sWHERe 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.nameFROM student sWHERe 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.nameFROM student sJOIN student_club x ON s.stud_id = x.stud_idJOIN student_club y ON s.stud_id = y.stud_idWHERe x.club_id = 30AND y.club_id = 50;
后三个的表现几乎相同。4）和5）得出相同的查询计划。
后期添加：
花式SQL，但性能跟不上。
7）超级立方体1：148.649毫秒
SELECt s.stud_id, s.nameFROM student AS sWHERe 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.nameFROM student AS sWHERe 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.studentFROM student sJOIN 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 sJOIN sc USING (stud_id);
查询2的CTE变体。出乎意料的是，它可能会导致使用完全相同的数据的查询计划略有不同。我在上找到了顺序扫描
student，其中子查询变量使用了索引。
11）超级立方体3：101.482毫秒
另一个后期添加@ypercube。有多少种方法真令人惊讶。
SELECt s.stud_id, s.studentFROM student sJOIN 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 sJOIN 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 sWHERe 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 )
俱乐部会员动态数量
换句话说：数量不同的过滤器。这个问题要求有 两个 俱乐部会员资格。但是许多用例必须为数量众多做准备。
在此相关的稍后答案中进行详细讨论：
- 在WHERe子句中多次使用同一列
