10.3. 用明确的 JOIN (连接)控制规划器

PostgreSQL 7.1 开始, 我们可以在一定程度上用明确的JOIN语法控制查询规划器. 要明白为什么有这茬事,我们首先需要一些背景知识.

在简单的连接查询里,比如

SELECT * FROM a,b,c WHERE a.id = b.id AND b.ref = c.id;

规划器可以按照任何顺序自由地连接给出的表. 比如,它可以生成一个查询规划先用WHERE子句 a.id = b.id把 A 连接到 B,然后用另外一个WHERE子句 把 C 连接到这个表上来,或者它也可以先连接 B 和 C 然后再连接 A, 也得到这个结果.或者它也可以连接 A 到 C 然后把结果与 B 连接 --- 不过这么做效率比较差,因为必须生成完整的 A 和 C 的迪卡尔积, 而在查询里没有可用的WHERE子句可以优化该连接. (PostgreSQL 执行器里的所有连接 都发生在两个输入表之间,所以在这种情况下它必须先得出一个结果.) 重要的一点是这些连接方式给出语义上相同的结果, 但在执行开销上却可能有巨大的差别. 因此,规划器会对它们进行检查并找出最高效的查询规划.

如果查询只涉及两或三个表,那么在查询里不会有太多需要考虑的连接. 但是潜在的连接顺序的数目随着表数目的增加程指数增加的趋势. 当超过十个左右的表以后,实际上根本不可能对所有可能做一次穷举搜索, 甚至对六七个表都需要相当长的时间进行规划. 如果有太多输入的表,PostgreSQL 规划器 将从穷举搜索切换为基因概率搜索, 以减少可能性数目(样本空间). (切换的阈值是用运行时参数GEQO_THRESHOLD设置的, 它在 PostgreSQL 7.3.3 管理员手册 里描述.) 基因搜索花的时间少,但是并不一定能找到最好的规划.

当查询涉及外部连接时,规划器就不象对付普通(内部)连接那么自由了. 比如,看看下面这个查询

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

尽管这个查询的约束和前面一个非常相似,但它们的语义却不同, 因为如果 A 里有任何一行不能匹配 B 和 C 的连接里的行, 那么该行都必须输出.因此这里规划器对连接顺序没有什么选择: 它必须先连接 B 到 C,然后把 A 连接到该结果上.因此, 这个查询比前面一个花在规划上的时间少.

PostgreSQL 的 规划器把所有明确的JOIN语法都当做连接顺序的约束, 即使它们对内部连接而言逻辑上不是必须的也如此. 因此,尽管下面所有的查询给出相同的结果:

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

第二个和第三个还是要比第一个花在规划上的时间少. 这个作用对于只有三个表的连接而言是微不足道的, 但对于数目众多的表,可能就是救命稻草了.

你完全不必为了缩短搜索时间来约束连接顺序, 因为在一个简单的FROM列表里使用JOIN操作符就很好了. 比如,

SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;

强迫规划器先把 A 连接到 B,然后再连接到其它的表上, 但并不约束其它的选择.在本例中,可能的连接顺序的数目减少了 5 倍.

如果你在一个复杂的查询里混合有内部和外部连接, 你可能不想规划器在外部连接里寻找一个好的内部连接顺序. 你不能直接在JOIN语法里实现这个目的, 但你可以使用子查询图绕过这个语法限制.比如,

SELECT * FROM d LEFT JOIN
        (SELECT * FROM a, b, c WHERE ...) AS ss
        ON (...);

在这里,连接 D 肯定是查询规划的最后一步, 但是规划器可以自由考虑 A,B,C 的连接顺序.

按照上面的想法考虑规划器的搜索问题是一个很有用的 技巧,不管是对减少规划时间还是对引导规划器生成好的规划都很有帮助. 如果缺省时规划器选择了一个糟糕的连接顺序, 你可以用JOIN语法强迫它选择一个更好的 --- 也就是说,你得先知道一个更好的顺序.所以我们建议多试验.