🥇PostgreSQL アンチパターン: SQL での条件評価

SQL は C++ ではなく、JavaScript でもありません。したがって、論理式の評価は異なりますが、これはまったく同じものではありません。

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL クエリの実行計画を最適化するとき等価な条件を任意に「再配置」できる、個々のレコードについては計算せず、適用されたインデックスの条件を参照してください...つまり、最も簡単な方法は、次のように仮定することです。管理できないそれらが計算される順序 (およびそもそも計算されるかどうか) 同等条件。

したがって、それでも優先順位を管理したい場合は、構造的に次のことを行う必要があります。 これらの条件を不平等にする 条件付き表現 и 演算子.

データとその活用が基礎となる当社の VLSI コンプレックスのしたがって、それらに対する操作が正しく実行されるだけでなく、効率的に実行されることが非常に重要です。式の評価でエラーが発生する可能性がある具体的な例と、効率を改善する価値のある例を見てみましょう。

#0: RTFM

起動ドキュメントからの例:

評価の順序が重要な場合は、次の構成で修正できます。 CASE。たとえば、文中でのゼロ除算を避けるには次の方法があります。 WHERE 信頼性が低い:
SELECT ... WHERE x > 0 AND y/x > 1.5;
安全なオプション:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
使用した構造 CASE 式を最適化から保護するため、必要な場合にのみ使用してください。

#1: トリガー条件

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

すべてがうまくいっているように見えますが... 投資した成果が確実に得られるとは誰も約束しません。 SELECT 最初の条件が false の場合、実行されません。で修正してください 入れ子になった IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

それでは、注意深く見てみましょう - トリガー関数の本体全体が、 IF。これは、次の方法を使用してこの条件を手順から削除することを妨げるものがないことを意味します。 WHEN-条件:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

このアプローチにより、条件が false の場合に確実にサーバーリソースを節約できます。

#2: OR/AND チェーン

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

それ以外の場合は、両方とも得られます。 EXISTS それは本当だろうが、 どちらも実行されます.

しかし、それらのうちの XNUMX つが「真」であることが確実にわかっている場合 (または「偽」である場合) がはるかに多くなります。 AND-chains) - XNUMX 番目のものが再度実行されないように、何らかの方法で「優先順位を上げる」ことは可能ですか?

それは可能であることがわかりました - アルゴリズム的なアプローチは記事の主題に近いです PostgreSQL のアンチパターン: まれなエントリが JOIN の途中に到達する.

これらの両方の条件を「CASE の下に押し込んで」みましょう。

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

この場合、定義しませんでした ELSE-value、つまり両方の条件が false の場合 CASE 戻ります NULL、次のように解釈されます FALSE в WHERE- 条件。

この例は、味と色を変えるために別の方法で組み合わせることができます。

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: 条件を記述する方法[しない]方法

私たちは XNUMX 日間をかけて、このトリガーの「奇妙な」トリガーの理由を分析しました。その理由を見てみましょう。

ソース：

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

問題 #1: 不等式は NULL を考慮に入れません

すべてだと仮定しましょう OLD-フィールドは重要だった NULL。何が起こるか？

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

そして、条件を整えるという観点から、 NULL 同等 FALSE、上記のように。

ソリューション: 演算子を使用します IS DISTINCT FROM から ROW-operator、レコード全体を一度に比較:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

問題その 2: 同じ機能の異なる実装

比較しなさい：

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

なぜ追加投資が発生するのか SELECT? 機能 to_regclass? なんで違うんだろう…

修正しましょう:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

問題 #3: ブール値の優先順位

ソースをフォーマットしましょう:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

おっと...実際、最初の XNUMX つの条件のいずれかが真実の場合、条件全体が次のようになります。 TRUE、不平等を無視します。そして、これは私たちが望んでいたものではありません。

修正しましょう:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

問題 #4 (小): XNUMX つのフィールドに対する複雑な OR 条件

実は、3 つの条件があったからこそ、No.XNUMX で問題が発生しました。しかし、それらの代わりに、次のメカニズムを使用すれば、XNUMX つで済みます。 coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

私たちもそうだ NULL 「キャッチ」、そして複雑 OR 括弧を気にする必要はありません。

合計で

得られたものを修正しましょう:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

そして、このトリガー関数は次の場合にのみ使用できることを考慮すると、 UPDATE存在によるトリガー OLD/NEW 上位レベルの条件では、この条件は通常、次のように取り出すことができます。 WHEN-#1に示すような状態...

出所： habr.com

PostgreSQL アンチパターン: SQL での条件評価