PostgreSQL 教程: 优化子查询

七月 16, 2024

摘要:在本教程中,您将学习如何优化 PostgreSQL 中的子查询。

目录

介绍

SQL 允许您在可能出现表或列名称的几乎任何地方使用子查询。您所要做的就是用括号将查询括起来,例如(SELECT ...),然后您可以在任意表达式中使用它。这使得 SQL 成为一种强大的语言,但是可能难以阅读。但我不想讨论 SQL 的美或丑。在本教程中,我们来看看如何编写出表现良好的子查询。让我们先从简单开始,稍后再来了解更令人惊讶和复杂的话题。

相关和不相关的子查询

在子查询中,您可以使用外部的表列,例如

SELECT a.col1,
       (SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;

对“a”中的每一行,子查询会不同。这样的子查询通常称为相关子查询。不相关的子查询是指不引用任何外部内容的子查询。

不相关的子查询很简单。如果 PostgreSQL 优化器没有“拉起它”(将其集成到主查询树中),则执行器将在单独的步骤中计算它。您可以在EXPLAIN的输出中看到InitPlan(初始计划)。**不相关的子查询几乎从来都不是性能问题。**在本文的其余部分,将会主要讨论相关的子查询。

标量和表格子查询

如果在 SQL 语句中的某个位置编写一个子查询,而该位置本来需要写入单个值,则该子查询是标量子查询。标量子查询的一个示例是上一节中的示例。一个不同的例子是

SELECT a.col1
FROM a
WHERE 1 = (SELECT count(*)
           FROM b
           WHERE b.x = a.x);

如果标量子查询不返回任何结果,则结果值为 NULL。如果查询返回多于一行,您会收到运行时错误:

ERROR:  more than one row returned by a subquery used as an expression

表格子查询出现在可以返回多个值的上下文中:

  • FROM列表条目:FROM (SELECT ...) AS alias
  • 公共表表达式(CTE):WITH q AS (SELECT ...) SELECT ...
  • INNOT IN表达式:WHERE a.x IN (SELECT ...)
  • EXISTSNOT EXISTS表达式:WHERE NOT EXISTS (SELECT ...)

标量子查询通常有性能问题

我的经验法则是:应当尽量避免相关的标量子查询。原因是 PostgreSQL 只能以嵌套循环方式来执行标量子查询。例如,PostgreSQL 会对表 “a” 中的每一行,执行一次前面提到的子查询。如果“a”是一个小表,这可能很好(请记住,这里的建议只是一个经验法则)。但是,如果表 “a” 很大,即使是快速的子查询,也会使查询执行速度慢得令人难受。

重写 SELECT 列表或 WHERE 子句中的标量子查询

如果相关的标量子查询对性能不利,我们如何避免它们?没有单一的、直接的答案,您可能无法重写查询,以避免在所有情况下都出现此类子查询。但通常的解决方案是,将子查询转换为连接。对于我们的第一个查询,它将如下所示:

SELECT a.col1,
       b.col2
FROM a
   LEFT JOIN b ON b.x = a.x;

查询在语义上是等效的,不同之处在于如果“a”中的行与“b”中的多行匹配,则不会收到运行时错误。我们需要一个外部连接,来说明子查询不返回任何结果的情况。

对于我们的第二个示例,重写后的查询将如下所示:

SELECT a.col1
FROM a
   JOIN b ON b.x = a.x
GROUP BY a.pkey, a.col1
HAVING count(*) = 1;

这里,a.pkey是“a”的主键。根据a.col1分组是不够的,因为表 “a” 中的两个不同行可能具有相同的col1值。

像上面这样重写查询的优点是,PostgreSQL 可以选择最佳连接策略,并且不限于嵌套循环。如果表 “a” 只有几行,这可能没有区别,因为无论如何,嵌套循环连接可能是最有效的连接策略。但是,在这种情况下,查询也不会因重写而表现变差。如果 “a” 很大,则使用哈希或合并连接的速度会快得多。

表格子查询和性能

虽然相关的标量子查询通常很糟糕,但表格子查询的情况也没那么简单。让我们分别考虑不同的情况。

FROM 中的 CTE 和子查询

这些情况几乎相同,因为您始终可以将 CTE 重写为FROM中的子查询,除非它是递归的、MATERIALIZED的或数据修改的 CTE。CTE 不会是相关的,因此它们永远不会有问题。但是,FROM子句条目可以在横向连接中关联:

SELECT a.col1, sub.col2
FROM a
   CROSS JOIN LATERAL
      (SELECT b.col2
       FROM b
       WHERE b.x = a.x
       ORDER BY b.sort
       LIMIT 1) AS sub;

同样,PostgreSQL 将在嵌套循环中执行这样的子查询,这对于大型表 “a” 可能会表现不佳。因此,重写查询以避免相关的子查询,通常是一个好主意:

SELECT DISTINCT ON (a.pkey)
       a.col1, b.col2
FROM a
   JOIN b ON b.x = a.x
ORDER BY a.pkey, b.sort;

如果 “a” 有很多行,则重写后的查询性能会更好,但如果 “a” 很小而 “b” 很大可能会更差,除非在(x, sort)上面有索引。

EXISTS 和 NOT EXISTS 中的子查询

这是一个特例。到目前为止,我一直建议避免相关子查询。但是使用EXISTSNOT EXISTS,PostgreSQL 优化器能够将子句分别转换为半连接和反连接。这使得 PostgreSQL 可以使用所有连接策略,而不仅仅是嵌套循环。

因此,PostgreSQL 可以高效地处理EXISTSNOT EXISTS的相关子查询

IN 和 NOT IN 的棘手情况

您可能会期望这两种情况的行为相似,但事实并非如此。在一个查询中,使用IN的子查询始终可以使用EXISTS重写。例如,下面的语句:

SELECT a.col1
FROM a
WHERE a.foo IN (SELECT b.col2
                 FROM b
                 WHERE a.x = b.x);

等价于

SELECT a.col1
FROM a
WHERE EXISTS (SELECT NULL
              FROM b
              WHERE a.x = b.x
                AND a.foo = b.col2);

PostgreSQL 优化器可以做到这一点,并会像处理EXISTS中的子查询一样,高效地处理IN中的子查询。

然而,NOT IN的情况却大不相同。您可以像上面一样,将NOT IN重写为NOT EXISTS,但这不是 PostgreSQL 可以自动完成的转换,因为重写的语句在语义上不同:如果子查询返回至少一个 NULL 值,则NOT IN永远不会为 TRUE。而NOT EXISTS子句没有表现出这种令人惊讶的行为。

现在人们通常不关心NOT IN的这个特点(事实上,很少有人知道它)。无论如何,大多数人都更喜欢NOT EXISTS的这种行为。但是您必须自己重写 SQL 语句,并且不能指望 PostgreSQL 会自动执行此操作。因此,我的建议是,您永远不要使用带有子查询的NOT IN子句,而始终应改为使用NOT EXISTS

使用相关子查询强制嵌套循环连接

到目前为止,我已经告诉您如何重写 SQL 语句,以避免强制优化器使用嵌套循环。然而,有时你会需要完全相反的结果:你希望优化器使用嵌套循环连接,因为你恰好知道这是最好的连接策略。然后,您可以有意识地将常规连接重写为横向交叉连接,以强制嵌套循环。例如,下面的查询

SELECT a.col1, b.col2
FROM a
   JOIN b ON a.x = b.x;

在语义上等价于

SELECT a.col1, sub.col2
FROM a
   CROSS JOIN LATERAL
      (SELECT b.col2
       FROM b
       WHERE a.x = b.x) AS sub;

结论

如果您希望获得良好的子查询性能,通常最好遵循以下准则:

  • 尽可能地使用不相关的子查询,只要它们不会让语句难以理解
  • 在所有地方避免使用相关子查询,除非在EXISTSNOT EXISTSIN子句中
  • 总是将NOT IN重写为NOT EXISTS

也不要把这些规则当作铁律。有时,相关子查询实际上可以表现得更好,有时您可以使用相关子查询,来强制优化器使用嵌套循环,只要您确定这是正确的策略。

了解更多

PostgreSQL 优化