ProHoster > Blog > administrasie > PostgreSQL Antipatrone: Toestandevaluering in SQL

PostgreSQL Antipatrone: Toestandevaluering in SQL

SQL is nie C++ nie, en nie JavaScript nie. Daarom vind die berekening van logiese uitdrukkings anders plaas, en dit is glad nie dieselfde nie:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

In die proses om die PostgreSQL-navraaguitvoerplan te optimaliseer ekwivalente toestande arbitrêr kan “herrangskik”., moenie sommige daarvan vir individuele rekords bereken nie, bring dit in verband met die voorwaardes van die toegepaste indeks... Kortom, die maklikste manier is om te aanvaar dat jy kan beheer nie in watter volgorde hulle sal (en of hulle enigsins bereken sal word) gelyk voorwaardes.

As jy dus steeds prioriteit wil bestuur, moet jy dit struktureer maak hierdie toestande ongelyk gebruik van voorwaardes uitdrukkings и operateurs.

PostgreSQL Antipatrone: Toestandevaluering in SQL
Data en die werk daarmee is die basis ons VLSI-kompleks, daarom is dit vir ons baie belangrik dat bewerkings daarop nie net korrek uitgevoer word nie, maar ook doeltreffend. Kom ons kyk na spesifieke voorbeelde waar foute in die berekening van uitdrukkings gemaak kan word, en waar dit die moeite werd is om hul doeltreffendheid te verbeter.

#0: RTFM

Begin voorbeeld uit dokumentasie:

Wanneer die volgorde van evaluering belangrik is, kan dit met behulp van die konstruk vasgelê word CASE. Dit is byvoorbeeld 'n manier om deling deur nul in 'n sin te vermy WHERE onbetroubaar:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Veilige opsie:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Die ontwerp wat op hierdie manier gebruik word CASE beskerm die uitdrukking teen optimalisering, dus moet dit slegs gebruik word wanneer dit nodig is.

#1: sneller toestand

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

Alles lyk goed, maar ... Niemand belowe dat die belegging SELECT sal nie uitgevoer word as die eerste voorwaarde vals is nie. Kom ons maak dit reg met geneste IF:

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

Kom ons kyk nou mooi - die hele liggaam van die snellerfunksie is "toegedraai". IF. Dit beteken dat niks ons verhoed om hierdie toestand uit die prosedure te verwyder wat gebruik word nie WHEN-toestande:

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

Hierdie benadering is gewaarborg om bedienerhulpbronne te bespaar wanneer die toestand vals is.

#2: OF/EN ketting

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

Andersins kan jy met albei eindig EXISTS sal "waar" wees, maar beide sal vervul word.

Maar as ons seker weet dat een van hulle baie meer dikwels "waar" is (of "vals" - vir AND-kettings) - is dit moontlik om op een of ander manier "sy prioriteit te verhoog" sodat die tweede een nie weer uitgevoer word nie?

Dit blyk dat dit moontlik is - die algoritmiese benadering is naby aan die onderwerp van die artikel PostgreSQL-teenpatrone: 'n seldsame rekord sal die middel van 'n JOIN bereik.

Kom ons "stoot" net albei hierdie toestande onder CASE:

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

In hierdie geval het ons nie gedefinieer nie ELSE-waarde, dit wil sê as beide voorwaardes vals is CASE sal terugkeer NULL, wat geïnterpreteer word as FALSE в WHERE-toestande.

Hierdie voorbeeld kan op ander maniere gekombineer word - afhangende van smaak en kleur:

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

#3: hoe [nie] om voorwaardes te skryf nie

Ons het twee dae spandeer om die redes vir die "vreemde" werking van hierdie sneller te ontleed - kom ons kyk hoekom.

Bron:

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

Probleem #1: ongelykheid respekteer nie NULL nie

Kom ons verbeel ons dat alles OLD-velde het betekenis gehad NULL. Wat sal gebeur?

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

En uit die oogpunt van die uitwerk van die voorwaardes NULL ekwivalent FALSE, soos bogenoem.

besluit: gebruik operateur IS DISTINCT FROM van ROW-operateur, vergelyk hele rekords gelyktydig:

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

Probleem #2: verskillende implementerings van dieselfde funksionaliteit

Kom ons vergelyk:

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

Hoekom is daar ekstra belegging hier? SELECT? 'n Funksie to_regclass? Hoekom is dit anders?..

Kom ons maak reg:

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

Probleem #3: prioriteit van bool-operasies

Kom ons formateer die bron:

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

Oeps... Trouens, dit het geblyk dat as enige van die eerste twee voorwaardes waar is, die hele toestand verander in TRUE, sonder om ongelykhede in ag te neem. En dit is glad nie wat ons wou hê nie.

Kom ons maak reg:

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

Probleem #4 (klein): komplekse OF toestand vir een veld

Eintlik het ons probleme in nr 3 gehad juis omdat daar drie voorwaardes was. Maar in plaas daarvan kan jy met een klaarkom deur die meganisme te gebruik coalesce ... IN:

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

So ons NULL "ons sal vang", en moeilik OR Dit is nie nodig om met hakies omhein te word nie.

In totaal

Kom ons teken op wat ons gekry het:

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

En as u in ag neem dat hierdie snellerfunksie slegs in gebruik kan word UPDATE-sneller as gevolg van beskikbaarheid OLD/NEW in die boonste vlak toestand, dan kan hierdie toestand oor die algemeen in geplaas word WHEN-toestand, soos getoon in #1...

Bron: will.com

Voeg 'n opmerking