PostgreSQL 10 - 查询优化 1

查询优化器

假设，我们有三张表，一个视图，每张表都有索引：

CREATE TABLE a (aid int, ...); -- 一亿行
CREATE TABLE b (bid int, ...); -- 二亿行
CREATE TABLE c (cid int, ...); -- 三亿行

CREATE INDEX idx_a ON a (aid);
CREATE INDEX idx_b ON b (bid);
CREATE INDEX idx_c ON c (cid);

CREATE VIEW v AS SELECT * FROM a, b WHERE aid = bid;

假设用户想运行下面的查询。优化器会做什么？它的选择是什么？

SELECT *
FROM v, c
WHERE v.aid = c.cid AND cid = 4;

在看真是的优化器如何处理之前，先看看优化器有什么选项。

评估join选项

假设优化器从头开始，计算视图的输出。连接一亿行和两亿行有什么好办法？

Nested loops

一个办法是使用嵌套循环。原理很简单，下面是伪代码：

for x in table1:
    for y in table2:
        if x.field == y.field
            issue row
        else
            keep doing

嵌套循环常用于一方很小，数据不多的时候。而我们的例子，要处理那么多数据，显然不会这样处理。

Hash joins

可以使用下面的策略，解决我们的问题：

← Hash join
    ← Sequentialscan table 1
    ← Sequentialscan table 2

双方都被hash，然后比较hash key，返回join结果。问题是所有的值都需要做hash，并暂存在某处。

Merge joins

最后是归并连接-使用排序列表连接返回值。如果要连接的双方都已经排好序了，系统能从顶部取一些行，如果匹配就返回。所以，最重要的是数据要先排序。计划是这样的：

← Merge join
    ← Sort table 1
        ← Sequential scan table 1
    ← Sort table 2
        ← Sequential scan table 2

连接的一方或者双方可以使用来自计划的底层的排序数据。如果直接访问表，索引是一个很好的选择，但是前提是返回的数据比整个表小很多。否则，我们的开销会增加很多-先读整个索引，再读整个表。如果返回集是表的很大一部分，顺序扫描更高效-尤其是以主键顺序访问的时候。
使用归并连接，可以处理很多数据。但是，数据必须先排序，或者从索引读数据。
排序成本是_O(n * log(n))_。因此，为3亿行数据排序也是没什么吸引力。
这几种连接都支持并行版本。

转换

既然几种连接都不可行。可以考虑使用逻辑转换加速查询。要执行下列几步：

内联视图

SELECT * FROM
    (SELECT * FROM a, b WHERE aid = bid) AS v, c
WHERE v.aid = c.cid AND cid = 4;

该视图是内联的，转换成了子查询。这样，为进一步的优化打开了大门。

消除子查询

SELECT * FROM a, b, c WHERE a.aid = c.cid AND aid = bid AND cid = 4;

消除子查询以后，变成了这样的。现在是一个普通连接。

等式约束

下来增加等式约束（equality constraints）-检测其他约束，连接选项和过滤等。让我们仔细观察一下这个查询：如果aid = cid而且aid = bid，我们知道bid = cid。如果cid = 4，则其他几个也等于4：

SELECT *
FROM a, b, c
WHERE a.aid = c.cid
    AND aid = bid
    AND cid = 4
    AND bid = cid
    AND aid = 4
    AND bid = 4

这几列都可以使用索引，就不使用可怕的视图了。PostgreSQL选择从索引检索几行以后，使用什么连接都可以了。

详尽的搜索

现在，所有的形式转换都结束了，PostgreSQL会执行详尽的搜索。它会试各种可能的计划，使用最廉价的方案。PostgreSQL还要决定最好的连接顺序。原始查询中，连接顺序是A → B和A → C。等式约束以后，可能先连接B → C，然后再连接A。优化器对所有的选项都持开放的态度。

试一试

PostgreSQL可能选择这样的执行计划：

postgres=# explain SELECT * FROM v, c WHERE v.aid = c.cid AND cid = 4;
QUERY PLAN
----------------------------------------------------------------
Nested Loop (cost=1.71..17.78 rows=1 width=12)
    -> Nested Loop (cost=1.14..9.18 rows=1 width=8)
        -> Index Only Scan using idx_a on a
            (cost=0.57..4.58 rows=1 width=4)
            Index Cond: (aid = 4)
        -> Index Only Scan using idx_b on b
            (cost=0.57..4.59 rows=1 width=4)
            Index Cond: (bid = 4)
    -> Index Only Scan using idx_c on c
        (cost=0.57..8.59 rows=1 width=4)
        Index Cond: (cid = 4)
(8 rows)

PostgreSQL会使用三个索引，使用嵌套循环连接数据。

使过程失败

你看到了，PostgreSQL可以为查询提速。PostgreSQL够聪明，但是也需要聪明的用户。用户做了愚蠢的事情，也会影响优化效果。我们先删掉视图：

DRO