Clever Geek Handbook

PostgreSQLアンチパターン：「残っているのは1つだけです！」

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レコードがあり、重複が英雄的に削除されると、特に「幸せ」になります...







それを修正するにはどうすればよいですか？まず、タスクを「Yが実行条件に少なくとも1つ関連付けられているレコード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



このようなクエリ変換の追加の利点は、次の場合のように、レコードの1つまたは複数のみが必要な場合に、レコードに対する反復を簡単に制限できることです。 



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


次に、リクエストを読み、DBMSが何を提案しているのかを理解しようとします。



  • プレートを接続します
  • X.pkで一意化可能
  • 残りのレコードから1つを選択します


つまり、あなたは何を得ましたか？ユニークなものの「1つのレコード」 -そして、これをユニークでないものの1つにすると、結果はどういうわけか変わりますか？..「そして、違いがないのなら、なぜもっと支払うのですか？」 



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


そして、とまったく同じトピックGROUP BY + LIMIT 1。



「私はただ尋ねる」：暗黙のグループ+制限



リクエストの実行中にプレートまたはCTEが空でないかどうかの さまざまなチェック中に、同様のことが発生します。 



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


集約関数（count/min/max/sum/...）は、明示的な指示がなくても、セット全体で正常に実行されGROUP BYます。LIMIT彼らだけが彼らとあまり友好的ではありません。



開発者は、「そこにレコードがあれば、これ以上LIMITは必要ない」と考えるかもしれません。しかし、しないでください！ベースの場合は次のとおりです。



  • すべてのレコードで必要なものを数えます
  • あなたが尋ねるだけの行を与える


ターゲット条件に応じて、ここで置換の1つを行うことが適切です。



  • (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のようなものが必要で、テーブルに数十万のレコードがある場合は特に悲しいです...



無駄に悲しいことをしないために、PostgreSQLWikiからの再帰クエリ「DISTINCTforthepoor」を使用します。





More articles:


All Articles