PostgreSQL 反模式：“必须只有一个！”

在 SQL 中，您描述的是想要实现的“什么”，而不是“如何”执行它。 因此，以“听说如其所写”的方式开发 SQL 查询的问题占据了重要地位，同时 SQL计算条件的特点.

今天，使用极其简单的示例，让我们看看这在使用环境中会产生什么结果 GROUP/DISTINCT и LIMIT 跟他们。

现在，如果您在请求中写道 “首先连接这些符号，然后扔掉所有重复的， 应该只剩下一个了 每个键的副本” - 这正是它的工作方式，即使根本不需要连接。

有时你很幸运，它“正常工作”，有时它会对性能产生令人不快的影响，有时它会产生从开发人员的角度来看完全意想不到的效果。

嗯，也许不是那么壮观，但是......

“甜蜜情侣”：JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

他们想要什么就很清楚了 选择 Y 中存在与满足条件相关的记录的记录 X。 通过写了一个请求 JOIN — 多次获得一些 pk 值（Y 中到底出现了多少个合适的条目）。 如何去除？ 当然 DISTINCT!

当每条 X 记录都有数百条相关的 Y 记录，然后重复项被英勇地删除时，这尤其“令人欣慰”......

怎么修？ 首先，意识到问题可以修改为 “选择记录 X，其中 Y 中至少有一个与满足条件相关联” - 毕竟，我们不需要 Y 记录本身的任何内容。

嵌套存在

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

某些版本的 PostgreSQL 知道在 EXISTS 中找到第一个出现的条目就足够了，而旧版本则不然。 因此我更喜欢始终指出 LIMIT 1 内 EXISTS.

横向连接

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

如有必要，相同的选项允许立即从找到的关联 Y 记录中返回一些数据。 文章中讨论了类似的选项 “PostgreSQL 反模式：罕见的记录将到达 JOIN 的中间”.

“为什么要支付更多”：DISTINCT [ON] + LIMIT 1

此类查询转换的另一个好处是，如果只需要一条或几条记录，则能够轻松限制对记录的搜索，如下例所示：

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

现在我们阅读该请求并尝试了解 DBMS 的用途：

• 连接标志
• X.pk 独有
• 从剩余条目中选择一项

那么你得到了什么？ “只有一个条目” 从独特的 - 如果我们把这个作为非独特的，结果会以某种方式改变吗？......“如果没有区别，为什么要花更多钱呢？”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

和完全相同的主题 GROUP BY + LIMIT 1.

“我只需要问”：隐含的 GROUP + LIMIT

类似的事情发生在不同的地方 非空检查 随着请求的进展，标志​​或 CTE：

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

聚合函数（count/min/max/sum/...）即使没有明确的指令，也能在整个集合上成功执行 GROUP BY。 只有 LIMIT 他们不是很友好。

开发者可以思考一下 “如果那里有记录，那么我只需要 LIMIT”。 但不要这样做！ 因为对于基础来说是：

• 数数他们想要什么 根据所有记录
• 按照他们的要求给出尽可能多的行

根据目标条件，可以进行以下替换之一：

• (count + LIMIT 1) = 0 上 NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 上 EXISTS(LIMIT 1)
• count >= N 上 (SELECT count(*) FROM (... LIMIT N))

“挂多少克”：DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

天真的开发人员可能真诚地相信请求将停止执行。 一旦我们找到第一个遇到的不同值中的 $1.

在未来的某个时候，由于新的节点，这可能并且将会起作用 索引跳跃扫描，目前正在制定实施方案，但尚未完成。

现在先 所有记录将被检索，是唯一的，并且只有从它们中才能返回所请求的金额。 如果我们想要类似的东西，那就特别悲伤了 $ 1 = 4，表里有几十万条记录……

为了不白白悲伤，我们使用递归查询 PostgreSQL Wiki 中的“DISTINCT 适合穷人”:

来源： habr.com