PostgreSQL アンチパターン: 「存在するのは XNUMX つだけです!」

SQL では、「どのように」取得するかではなく、「何を」取得したいかを記述します。 したがって、「聞いたとおりに書かれている」スタイルで SQL クエリを開発するという問題が優先され、 SQL における条件評価の特殊性.

今日は、非常に単純な例を使用して、これが次の使用のコンテキストでどのような結果をもたらすかを見てみましょう。 GROUP/DISTINCT и LIMIT 彼らと一緒に。

それはリクエストに書いた場合です 「まずこれらのタブレットを接続し、それから重複したものをすべて破棄します。 XNUMX つだけあるはずです 各キーのインスタンス」 - 接続がまったく必要なかった場合でも、これはまさにこのように動作します。

そして、運が良くて「うまくいく」場合もあれば、パフォーマンスに不快な影響を与える場合もあれば、開発者の観点からは全く予想外の効果が生じる場合もあります。

まあ、それほど壮観ではないかもしれませんが…

「素敵なカップル」: 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 レコードがあり、重複が英雄的に削除される場合は特に「楽しい」です...

直し方？ まず、タスクを次のように変更できることを理解してください。 "条件が満たされている Y に少なくとも XNUMX つが関連付けられているレコード X を選択します" - 結局のところ、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

このようなクエリ変換の追加の利点は、次の場合のように、必要なレコードが XNUMX つまたは少数である場合に、レコードの列挙を簡単に制限できることです。

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

ここでリクエストを読み、DBMS が何をすべきかを理解しようとします。

• プレートを接続します
• X.pk によるユニークな
• 残りのレコードの XNUMX つを選択します

それで、何を手に入れましたか？ 「ある一枚のレコード」 ユニークなものから - そして、これをユニークでないものから選んだ場合、結果は何らかの形で変わりますか? .. 「違いがないなら、なぜもっと支払う必要があるのでしょうか?」

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 for the Poor」:

出所： habr.com