🥇PostgreSQL antipatterns: nosacījumu aprēķināšana SQL

SQL nav C++, nevis JavaScript. Tāpēc loģisko izteiksmju aprēķins notiek atšķirīgi, un tas nepavisam nav viens un tas pats:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL vaicājuma izpildes plāna optimizācijas procesā var patvaļīgi “pārkārtot” līdzvērtīgus nosacījumus, nerēķiniet dažus no tiem atsevišķiem ierakstiem, saistiet tos ar piemērotā indeksa nosacījumiem... Īsāk sakot, vienkāršākais veids ir pieņemt, ka jūs nevar kontrolēt kādā secībā tie tiks (un vai tie vispār tiks aprēķināti) vienāds nosacījumiem.

Tāpēc, ja joprojām vēlaties pārvaldīt prioritāti, jums tā ir jāstrukturē padarīt šos nosacījumus nevienlīdzīgus izmantojot nosacījumus izteiksmes и operatori.

Dati un darbs ar tiem ir pamats mūsu VLSI komplekss, tādēļ mums ir ļoti svarīgi, lai operācijas ar tām tiktu veiktas ne tikai pareizi, bet arī efektīvi. Apskatīsim konkrētus piemērus, kur var pieļaut kļūdas izteiksmju aprēķināšanā un kur ir vērts uzlabot to efektivitāti.

#0: RTFM

Sākas piemērs no dokumentācijas:

Ja novērtēšanas secība ir svarīga, to var uztvert, izmantojot konstrukciju CASE. Piemēram, tas ir veids, kā teikumā izvairīties no dalīšanas ar nulli WHERE neuzticams:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Drošs variants:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Šādā veidā izmantotais dizains CASE aizsargā izteiksmi no optimizācijas, tāpēc to vajadzētu izmantot tikai nepieciešamības gadījumā.

#1: sprūda nosacījums

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

Šķiet, ka viss izskatās labi, bet... Neviens nesola, ka ieguldījums SELECT netiks izpildīts, ja pirmais nosacījums ir nepatiess. Labosim ar ligzdotas IF:

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

Tagad paskatīsimies uzmanīgi - viss sprūda funkcijas korpuss ir “iesaiņots”. IF. Tas nozīmē, ka nekas neliedz mums noņemt šo nosacījumu no procedūras, izmantojot WHEN- nosacījumi:

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

Šī pieeja garantē servera resursu taupīšanu, ja nosacījums ir nepatiess.

#2: VAI/UN ķēde

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

Pretējā gadījumā jūs varat nonākt pie abiem EXISTS būs “patiesa”, bet abi piepildīsies.

Bet, ja mēs noteikti zinām, ka viens no tiem ir “patiess” daudz biežāk (vai “nepatiess” - par AND-ķēdes) - vai ir iespējams kaut kā “palielināt tās prioritāti”, lai otrā netiktu izpildīta vēlreiz?

Izrādās, ka tas ir iespējams – algoritmiskā pieeja ir tuva raksta tēmai PostgreSQL antipatterns: rets ieraksts sasniegs JOIN vidu.

Vienkārši “izbīdīsim” abus šos nosacījumus CASE:

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

Šajā gadījumā mēs nedefinējām ELSE-vērtība, tas ir, ja abi nosacījumi ir nepatiesi CASE atgriezīsies NULL, kas tiek interpretēts kā FALSE в WHERE- nosacījumi.

Šo piemēru var kombinēt citos veidos - atkarībā no garšas un krāsas:

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

#3: kā [ne] rakstīt nosacījumus

Mēs pavadījām divas dienas, analizējot šī sprūda “dīvainās” darbības iemeslus - redzēsim, kāpēc.

Avots:

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

Problēma #1: nevienlīdzība neievēro NULL

Iedomāsimies, ka viss OLD-laukiem bija nozīme NULL. Kas notiks?

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

Un no nosacījumu izstrādes viedokļa NULL ekvivalents FALSE, kā iepriekš minēts.

Šķīdums: izmantojiet operatoru IS DISTINCT FROM no ROW-operators, salīdzinot visus ierakstus vienlaikus:

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

2. problēma: vienas un tās pašas funkcionalitātes dažādas ieviešanas

Salīdzināt:

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

Kāpēc šeit ir papildu investīcijas? SELECT? Funkcija to_regclass? Kāpēc ir savādāk?...

Labosim:

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

3. problēma: būtības operāciju prioritāte

Formatēsim avotu:

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

Hmm... Patiesībā izrādījās, ka, ja kāds no pirmajiem diviem nosacījumiem ir patiess, viss nosacījums pārvēršas par TRUE, neņemot vērā nevienlīdzību. Un tas nepavisam nav tas, ko mēs gribējām.

Labosim:

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

4. problēma (maza): komplekss VAI nosacījums vienam laukam

Patiesībā mums bija problēmas Nr.3 tieši tāpēc, ka bija trīs nosacījumi. Bet to vietā jūs varat iztikt ar vienu, izmantojot mehānismu coalesce ... IN:

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

Tātad mēs NULL "mēs noķersim", un grūti OR Nav nepieciešams nožogot ar kronšteiniem.

Kopā

Reģistrēsim to, ko esam ieguvuši:

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

Un, ja uzskatāt, ka šo sprūda funkciju var izmantot tikai UPDATE-sprūda pieejamības dēļ OLD/NEW augstākā līmeņa stāvoklī, tad šo stāvokli parasti var ievietot WHEN-stāvoklis, kā parādīts #1...

Avots: www.habr.com

PostgreSQL antipatterns: stāvokļa novērtējums SQL