连接
我们先建表,准备些数据:
postgres=# CREATE TABLE a (aid int);
CREATE TABLE
postgres=# CREATE TABLE b (bid int);
CREATE TABLE
postgres=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
postgres=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3
我们看一个简单的外连接:
postgres=# SELECT * FROM a LEFT JOIN b ON (aid = bid);
aid | bid
-----+-----
1 |
2 | 2
3 | 3
(3 行记录)
下一个例子对很多人来说,可能是个惊喜:
postgres=# SELECT * FROM a LEFT JOIN b ON (aid = bid AND bid = 2);
aid | bid
-----+-----
1 |
2 | 2
3 |
(3 行记录)
结果集没有减少。很多人以为只返回一行,会导致一些隐藏的问题。
postgres=# SELECT avg(aid), avg(bid) FROM a LEFT JOIN b
ON (aid = bid AND bid = 2);
avg | avg
--------------------+--------------------
2.0000000000000000 | 2.0000000000000000
(1 行记录)
平均值并不是在单行的基础上计算的。人们写外连接,却不知道在让PostgreSQL做什么。所以,在质疑外连接的性能之前,先要问一下它的语义是什么。
处理外连接
一般来说,重新排列内连接的顺序,能显著加快查询的执行速度。但是,对于外连接,只允许少数重排序操作。
(A leftjoin B on (Pab)) innerjoin C on (Pac) = (A innerjoin C on (Pac)) leftjoin B on (Pab)
Pac是引用A和C的谓词,同样的,Pab是引用A和B的谓词……
(A leftjoin B on (Pab)) leftjoin C on (Pac) = (A leftjoin C on (Pac)) leftjoin B on (Pab)
(A leftjoin B on (Pab)) leftjoin C on (Pbc) = (A leftjoin (B leftjoin C on (Pbc)) on (Pab)
当对于所有的B的空行,谓词Pbc一定失败,则最后一条规则成立-就是说,B至少有一列。如果Pbc不严格(strict),第一种形式可能产生一些C列非空的行,而第二种形式会使整行为空。
虽然一些连接可以重排,但是,查询一般不会受益:
SELECT ...
FROM a LEFT JOIN b ON (aid = bid)
LEFT JOIN c ON (bid = cid)
LEFT JOIN d ON (cid = did)
...
解决这个问题的办法是,检查一下是否真的需要这些外连接。
join_collapse_limit
在查询计划处理期间,PostgreSQL会检查所有可能的连接顺序。很多时候,这很昂贵,让计划处理速度变慢。
join_collapse_limit变量可以让开发者解决问题的工具-以更直接的方式,查询应该如何处理。
先看一下这几条语句:
SELECT * FROM tab1, tab2, tab3
WHERE tab1.id = tab2.id
AND tab2.ref = tab3.id;
SELECT * FROM tab1 CROSS JOIN tab2
CROSS JOIN tab3
WHERE tab1.id = tab2.id
AND tab2.ref = tab3.id;
SELECT * FROM tab1 JOIN (tab2 JOIN tab3
ON (tab2.ref = tab3.id))
ON (tab1.id = tab2.id);
这三条语句是相同的,优化器会以同样的办法处理。第一条语句是隐式连接,最后一个由显式连接组成。在内部,优化器会检查这些请求,对连接重新排序以获得最佳的运行时间。问题是:有多少显式连接会修改成隐式的?可以修改join_collapse_limit来告诉优化器。对于普通查询,默认值就挺好的。如果你的查询有很多连接,修改它可以减少计划执行时间。
想看join_collapse_limit如何改变执行计划,请看下面的查询:
EXPLAIN WITH x AS
(
SELECT * FROM generate_series(1, 1000) AS id
)
SELECT *
FROM x AS a
JOIN x AS b ON (a.id = b.id)
JOIN x AS c ON (b.id = c.id)
JOIN x AS d ON (c.id