🥇PostgreSQL 反模式：有害的 JOIN 和 OR

谨防带来缓冲区的操作...
以一个小查询为例，让我们看看一些在 PostgreSQL 中优化查询的通用方法。是否使用它们取决于您自己，但了解它们是值得的。

在 PG 的某些后续版本中，随着调度程序变得更加智能，情况可能会发生变化，但对于 9.4/9.6，情况看起来大致相同，如此处的示例所示。

让我们提出一个非常实际的请求：

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

关于表名和字段名字段和表的“俄语”名称可以不同地对待，但这是一个品味问题。因为在张量这里没有外国开发者，并且 PostgreSQL 允许我们甚至用象形文字来命名，如果他们用引号引起来，那么我们更愿意明确、清晰地命名对象，这样就不会出现差异。
让我们看看最终的计划：

[看看 explain.tensor.ru]

144ms 和近 53K 缓冲区 ——也就是超过400MB的数据！如果在我们请求时所有这些都在缓存中，我们将很幸运，否则从磁盘读取时将花费数倍的时间。

算法最重要！

为了以某种方式优化任何请求，您必须首先了解它应该做什么。
我们暂时将数据库结构本身的开发放在本文的讨论范围之外，并同意我们可以相对“便宜” 重写请求 和/或将一些我们需要的东西滚到底座上指数.

所以请求：
— 检查至少一些文档的存在
- 在我们需要的条件下并且是某种类型
- 作者或表演者是我们需要的员工

加入+限制1

通常，开发人员更容易编写一个查询，其中首先连接大量表，然后整个集合中仅保留一条记录。但对于开发人员来说更容易并不意味着数据库更高效。
在我们的例子中，只有 3 张桌子 - 效果如何......

我们先去掉与“文档类型”表的连接，同时告诉数据库：我们的类型记录是独一无二的（我们知道这一点，但调度程序还不知道）：

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

是的，如果表/CTE 由单个记录的单个字段组成，那么在 PG 中你甚至可以这样写，而不是

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL 查询中的惰性求值

BitmapOr 与 UNION

在某些情况下，位图堆扫描会让我们付出很大的代价——例如，在我们的情况下，当相当多的记录满足所需条件时。我们得到它是因为 OR 条件变成 BitmapOr- 按计划运行。
让我们回到最初的问题——我们需要找到一条对应的记录任何从条件来看——即两种条件下都不需要搜索所有59K记录。有一种方法可以解决一个条件，并且仅当第一个中没有找到任何内容时才转到第二个。下面的设计将帮助我们：

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

“外部”LIMIT 1 确保在找到第一个记录时结束搜索。如果已经在第一个块中找到，则不会执行第二个块（从未执行过 方面）。

“CASE下隐藏困难条件”

原始查询中有一个非常不方便的时刻 - 根据相关表“DocumentExtension”检查状态。无论表达式中其他条件的真实性如何（例如， d.“已删除”不属实），这个连接总是被执行并且“消耗资源”。或多或少将被花费——取决于这张桌子的大小。
但是您可以修改查询，以便仅在确实有必要时才搜索相关记录：

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

一旦从链接表给我们结果不需要任何字段，那么我们就有机会将 JOIN 转换为子查询的条件。
让我们将索引字段保留在“CASE 括号之外”，将记录中的简单条件添加到 WHEN 块 - 现在，仅在传递给 THEN 时才会执行“重”查询。

我的姓氏是“总”

我们使用上述所有机制收集结果查询：

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

调整索引

训练有素的眼睛注意到 UNION 子块中的索引条件略有不同 - 这是因为我们在表上已经有了合适的索引。如果它们不存在，那么就值得创建： 文档（Person3，文档类型） и 文档（文档类型，员工）.
关于 ROW 条件中字段的顺序从策划者的角度来看，当然可以这样写 (A, B) = (常量A, 常量B)和 (B, A) = (常量B, 常量A)。但录音时按照索引中字段的顺序，这样的请求只是为了以后调试更方便。
计划中有什么？

[看看 explain.tensor.ru]

不幸的是，我们运气不好，第一个 UNION 块中什么也没找到，所以第二个 UNION 块仍然被执行。但即便如此——也只是 0.037ms 和 11 个缓冲区!
我们加快了请求速度并减少了内存中的数据泵送几千次，使用相当简单的技术 - 只需少量复制粘贴即可获得良好的结果。 🙂

来源： habr.com

PostgreSQL 反模式：有害的 JOIN 和 OR