🥇PostgreSQL Antipatterns: SQL'de Durum Değerlendirmesi

SQL, C++ veya JavaScript değildir. Bu nedenle, mantıksal ifadelerin değerlendirilmesi farklıdır ve bu hiç de aynı şey değildir:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Bir PostgreSQL sorgusunun yürütme planını optimize ederken eşdeğer koşulları keyfi olarak "yeniden düzenleyebilir", hiçbirini bireysel kayıtlar için hesaplamayın, uygulanan indeksin durumuna bakın ... Kısacası, en kolay yol, idare edemiyorum olacakları sıra (ve hesaplanıp hesaplanmayacakları) adil koşulları.

Bu nedenle, yine de önceliği yönetmek istiyorsanız, yapısal olarak bu koşulları eşitsiz kılmak koşullu ifade и operatörler.

Veriler ve onlarla çalışmak temeldir VLSI kompleksimizin, bu yüzden üzerlerindeki işlemlerin sadece doğru değil, aynı zamanda verimli bir şekilde yapılması bizim için çok önemlidir. İfade değerlendirmesinde hataların yapılabileceği ve bunların verimliliğini artırmaya değer olduğu somut örneklere bakalım.

#0: RTFM

Başlangıç belgelerden örnek:

Değerlendirme sırası önemli olduğunda, yapı ile sabitlenebilir CASE. Örneğin, bir cümlede sıfıra bölmekten kaçınmak için bu yol WHERE güvenilmez:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Güvenli seçenek:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Kullanılan yapı CASE ifadeyi optimizasyondan korur, bu nedenle yalnızca gerektiğinde kullanılmalıdır.

#1: tetikleme koşulu

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

Her şey iyi görünüyor, ama... Kimse yatırımın iyi olacağına dair söz vermiyor. SELECT ilk koşul yanlışsa yürütülmez. şununla düzelt iç içe IF:

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

Şimdi dikkatlice bakalım - tetikleme işlevinin tüm gövdesi "sarılmış" çıktı IF. Ve bu, hiçbir şeyin bizi bu koşulu kullanarak prosedürden çıkarmamızı engellediği anlamına gelir. WHEN-koşullar:

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

Bu yaklaşım, koşul yanlışsa sunucu kaynaklarını bir garantiyle kaydetmenize olanak tanır.

#2: VEYA/VE zinciri

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

Aksi takdirde, her ikisinin de elde edilebileceği EXISTS doğru olacak ama ikisi de idam edilecek.

Ancak bunlardan birinin çok daha sık "doğru" (veya "yanlış") olduğundan emin olursak - çünkü AND-zincirler) - ikincisinin bir kez daha uygulanmaması için bir şekilde "önceliğini artırmak" mümkün mü?

Bunun mümkün olduğu ortaya çıktı - algoritmik yaklaşım makalenin konusuna yakın PostgreSQL Antipatterns: Nadir giriş bir JOIN'in ortasına ulaşır.

Bu koşulların her ikisini de "CASE altına itelim":

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

Bu durumda, tanımlamadık ELSE-değer, yani her iki koşul da yanlışsa CASE dönecek NULLolarak yorumlanır FALSE в WHERE- koşullar.

Bu örnek başka bir şekilde birleştirilebilir - tatmak ve renklendirmek için:

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

#3: koşullar nasıl [yazılmaz]

Bu tetikleyicinin "garip" tetiklemesinin nedenlerini analiz etmek için iki gün harcadık - nedenini görelim.

Kaynak:

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 ...

Sorun 1: Eşitsizlik NULL'u açıklamıyor

Diyelim ki her şey OLD-alanlar önemliydi NULL. Ne olacak?

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

Ve koşulların çalışılması açısından NULL eş değer FALSE, Yukarıda da belirtildiği gibi.

karar: operatörü kullan IS DISTINCT FROM itibaren ROW-operatör, tüm kayıtları aynı anda karşılaştırarak:

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

Sorun 2: aynı işlevin farklı uygulanması

Karşılaştıralım:

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

Neden ekstra yatırımlar var? SELECT? Bir işlev to_regclass? Neden farklı...

Düzeltelim:

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

Problem 3: bool önceliği

Kaynağı biçimlendirelim:

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

Hata ... Aslında, ilk iki koşuldan herhangi birinin doğru olması durumunda, tüm koşulun dönüştüğü ortaya çıktı. TRUE, eşitsizlikleri göz ardı ederek. Ve bu hiç de istediğimiz şey değildi.

Düzeltelim:

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

Problem #4 (küçük): bir alan için karmaşık VEYA koşulu

Aslında 3 numarada tam da XNUMX şart olduğu için sorun yaşadık. Ama onların yerine, mekanizmayı kullanarak biriyle idare edebilirsiniz. coalesce ... IN:

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

biz de öyle NULL "yakala" ve karmaşık OR Parantezlerle uğraşmanıza gerek yok.

Toplam

Elimizdekileri düzeltelim:

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

Ve bu tetikleme işlevinin yalnızca UPDATEvarlığı nedeniyle tetik OLD/NEW üst düzey koşulda, o zaman bu koşul genellikle şu şekilde çıkarılabilir: WHEN-durum #1'de gösterildiği gibi...

Kaynak: habr.com

PostgreSQL Antipatterns: SQL'de Durum Değerlendirmesi