【译】PostgreSQL 查询优化：左连接vs联合所有

原文链接：https://www.crunchydata.com/blog/postgres-query-optimization-left-join-vs-union-all
作者：David Christensen

介绍
PostgreSQL 优化器是一个了不起的东西，每个版本都会变得更加惊人。它能够获取有关您的数据定义、数据分布、约束和特定查询的信息，并提出通常最有效的方法来返回该查询的结果。

由于 SQL 是一种声明性语言，我们明确放弃定义数据库如何确定结果并相信它能够以它认为最有效的任何方法获得正确的结果。有时我们可以以某种方式构造查询以获得比直接方法更好的结果。

问题
我正在与托管在 Crunchy Bridge 上的一位客户一起工作，查看 pg_stat_statements 中按平均花费的时间排名最高的查询。最上面的异常值是以下形式的查询：

SELECT
    "table_a".*,
    COALESCE("table_b"."count", 0) AS table_count
FROM
    table_a
LEFT JOIN
    table_b ON table_a.id = table_b.a_id
WHERE
    NOT bool_1 AND NOT bool_2 AND NOT bool_3
ORDER BY
    table_count DESC
LIMIT 100

pg_stat_statements 报告的此查询的平均时间在 2-3 秒范围内，对于此客户端来说太长了。这里涉及的表大小适中，每张表有数百万行。

这里涉及的特定查询使用了一个存储附加表的汇总计数的表；此查询用于支持为相应表中的每一行优化 COUNT()。对于这个特定的表，数据分布有点不平衡，所以对于一个常见的情况，底层的 COUNT() 会很快；但是，表中有足够多的异常值，这些异常值具有大量计数，通用计划无法正常工作。出于这个原因，我们使用一个未记录的表来存储查找的结果。 （由于这个版本的 PostgreSQL 不支持 UNLOGGED MATERIALIZED VIEWS，我们选择了这种方法，因为这个表的生成产生了相当多的 WAL。）

以下是未修改查询的计划：

Limit  (cost=978034.80..978036.03 rows=10 width=2934)
       ->  Gather Merge  (cost=978034.80..1430258.92 rows=3680894 width=2934)
             Workers Planned: 7
             ->  Sort  (cost=977034.68..978349.28 rows=525842 width=2934)
                   Sort Key: (COALESCE(table_b.count, '0'::bigint)) DESC
                   ->  Parallel Hash Left Join  (cost=87599.95..965671.42 rows=525842 width=2934)
                         Hash Cond: (table_a.id = table_b.a_id)
                         ->  Parallel Seq Scan on table_a  (cost=0.00..874743.68 rows=525842 width=2926)
                               Filter: ((NOT bool_1) AND (NOT bool_2) AND (NOT bool_3))
                         ->  Parallel Hash  (cost=62782.20..62782.20 rows=1985420 width=12)
                               ->  Parallel Seq Scan on table_b  (cost=0.00..62782.20 rows=1985420 width=12)
    (11 rows)

由于 ORDER BY 子句使用表达式，我们将无法在此处直接使用索引，并且由于此 COALESCE 的全部目的是为连接表中不存在的行提供默认值，因此我们不能使用一些一种表达索引。因此，我们不能直接使用索引来返回我们感兴趣的值，我们需要求助于外部排序。

那么我们如何看待改进呢？这里要注意的一件事是，我们在 LEFT JOIN 案例中替换为一个常量值；基本上我们假设如果有一个缺失的行，这个计数现在是 0。我们可以使用这个事实如下：

表 A 和 B 的左连接是 A 和 B 的交集加上 A 中未出现在 B 中的行。（这是 NULL 匹配 id 的情况。）我们不能直接索引左连接，但是交集的情况可以被很好地索引，我们也许可以为空连接情况做一些比当前情况更好的事情。因此，与其直接使用 LEFT JOIN，不如创建一个查询，显式计算结果集的每个部分并附加它们，看看我们如何提高性能。

对于交叉点，我们有以下内容：

SELECT
    "table_a".*,
    "table_b"."count" AS table_count
FROM
    table_a
JOIN
    table_b ON table_a.id = table_b.a_id
WHERE
    NOT bool_1 AND NOT bool_2 AND NOT bool_3
ORDER BY
    table_count DESC

对于 A 中不在 B 中的行，我们有：

SELECT
    "table_a".*,
    0 AS table_count
FROM
    table_a
LEFT JOIN
    table_b ON table_a.id = table_b.a_id
WHERE
    NOT bool_1 AND NOT bool_2 AND NOT bool_3
    AND table_b.id IS NULL

将这两者结合起来可以得到以下查询：

SELECT * FROM (
    SELECT
        "table_a".*,
        "table_b"."count" AS table_count
    FROM
        table_a
    JOIN
        table_b ON table_a.id = table_b.a_id
    WHERE
        NOT bool_1 AND NOT bool_2 AND NOT bool_3
    ORDER BY
        table_count DESC
) intersect
UNION ALL
    SELECT * FROM (
    SELECT
        "table_a".*,
        0 AS table_count
    FROM
        table_a
    LEFT JOIN
        table_b ON table_a.id = table_b.a_id
WHERE
    NOT bool_1 AND NOT bool_2 AND NOT bool_3
    AND table_b.id IS NULL
) antijoin
LIMIT 100

由于在我们的原始查询中没有二级排序，我们不关心反连接行的顺序是什么；它们始终是相同的常量排序值，因此数据库返回的自然顺序就足够了。此外，由于我们的相交集是一个索引列，我们的 ORDER BY count DESC 可以通过使用反向 btree 索引扫描来完成，从而为我们节省了一个排序节点。

这给了我们以下计划，我们现在可以看到它的性能要好得多：

Limit  (cost=0.87..19.30 rows=10 width=6116) (actual time=0.049..0.132 rows=10 loops=1)
       ->  Append  (cost=0.87..6784732.67 rows=3680878 width=6116) (actual time=0.048..0.130 rows=10 loops=1)
             ->  Nested Loop  (cost=0.87..5172687.85 rows=1956032 width=2934) (actual time=0.048..0.119 rows=10 loops=1)
                   ->  Index Scan Backward using table_b_count on table_b  (cost=0.43..191262.84 rows=7941680 width=12) (actual time=0.016..0.033 rows=12 loops=1)
                   ->  Index Scan using table_a_pkey on table_a  (cost=0.43..0.63 rows=1 width=2926) (actual time=0.006..0.006 rows=1 loops=12)
                         Index Cond: (id = table_b.a_id)
                         Filter: ((NOT bool_1) AND (NOT bool_2) AND (NOT bool_3))
                         Rows Removed by Filter: 0
             ->  Subquery Scan on "*SELECT* 2"  (cost=0.87..1554519.81 rows=1724847 width=2934) (never executed)
                   ->  Merge Anti Join  (cost=0.87..1532959.22 rows=1724847 width=2930) (never executed)
                         Merge Cond: (table_a_1.id = table_b_1.a_id)
                         ->  Index Scan using table_a_pkey on table_a table_a_1  (cost=0.43..1332577.10 rows=3680879 width=2926) (never executed)
                               Filter: ((NOT bool_1) AND (NOT bool_2) AND (NOT bool_3))
                         ->  Index Only Scan using table_b_a_id on table_b table_b_1  (cost=0.43..152158.13 rows=7941680 width=4) (never executed)
                               Heap Fetches: 0
     Planning Time: 1.107 ms
     Execution Time: 0.244 ms
    (17 rows)

对于完全相同的结果集，我们从 2-3 秒的执行时间缩短到 1 毫秒以下。任何时候只要有一个 LEFT JOIN 可以用索引来回答部分结果，这种相同的技术就可以派上用场，因此您可以单独优化每个部分并组合起来，而不是对所有总结果使用更重的 ORDER BY。

此外，由于我们对结果集应用了 LIMIT，您可以看到在这种情况下甚至没有计算反连接子选择，因为我们请求的行数已经被相交集满足了。不用说，无论多快，跳过计算总是比运行计算快。

我希望这种技术在您自己的查询优化之旅中派上用场。