PostgreSQL Antipatterns: het berekenen van voorwaarden in SQL

SQL is neither C++ nor JavaScript. Therefore, the evaluation of logical expressions occurs differently, and this is definitely not the same thing:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

During the optimization of the execution plan in PostgreSQL it may arbitrarily 'rearrange' equivalent conditions, not calculate some of them for individual records, assign them to conditions of the applied index... In general, it's easiest to think that you cannot manage the order in which (and whether or not) the evaluations will occur on par with conditions.

Therefore, if you still want to control the priority, you need to structurally make these conditions unequal using conditional expressions. en operators.

Data and working with it are the foundation of our SBIS complex, which is why it is very important for us that operations on them are performed not only correctly but also efficiently. Let's look at specific examples where calculation errors may occur, and where we should improve their efficiency.

#0: RTFM

Starter voorbeeld uit de documentatie:

When the order of evaluation is important, it can be fixed using the CASEconstruction. For example, this way to avoid division by zero in a statement WAAR is unreliable:

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

Safe option:

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

Using such a construction CASE protects the expression from optimization, so it should only be used when necessary.

#1: условие в триггере

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

Everything seems fine, but... No one promises that the nested SELECT won't be executed if the first condition is false. Let's fix this with nested IF:

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

Now let's look closely — the entire body of the trigger function has been 'wrapped' in IF. And that means nothing stops us from extracting this condition from the procedure using WHEN-conditions:

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

This approach reliably saves server resources when the condition is false.

#2: OR/AND-цепочка

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

In an unfortunate case, we may find that both EXISTS will be 'true', but both will be executed.

But if we know for sure that one of them tends to be 'true' much more often (or 'false' — for the EN-chain) — is there any way to 'raise its priority' so that the second does not execute unnecessarily?

Het blijkt mogelijk te zijn — de algoritmische benadering is verwant aan het onderwerp van het artikel PostgreSQL Antipatterns: een zeldzame opname zal het midden van de JOIN bereiken.

Laten we gewoon beide voorwaarden "onder CASE" plaatsen:

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

In dit geval hebben we niet gedefinieerd ELSE-waarde, dat wil zeggen in het geval dat beide voorwaarden onwaar zijn CASE zal teruggeven NULL, wat wordt geïnterpreteerd als FALSE in WAAR-voorwaarde.

Dit voorbeeld kan op een andere manier gecombineerd worden — naar smaak:

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

#3: как [не] надо писать условия

We hebben twee dagen besteed aan het analyseren van de oorzaken van het "vreemde" functioneren van deze trigger — laten we kijken waarom.

Broncode:

IF( NEW."Document_" is null or NEW."Document_" = (select '"Complete"'::regclass::oid) or NEW."Document_" = (select to_regclass('"DocumentVoorLoon"')::oid)
     AND (   OLD."DocumentOnzeOrganisatie" <> NEW."DocumentOnzeOrganisatie"
          OR OLD."Verwijderd" <> NEW."Verwijderd"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Tijd" <> NEW."Tijd"
          OR OLD."PersoonGemaakt" <> NEW."PersoonGemaakt" ) ) THEN ...

Probleem #1: ongelijkheid houdt geen NULL in aanmerking

Stel je voor dat alles OLD-velden waarde hebben NULL. Wat gebeurt er?

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

En vanuit het perspectief van de voorwaarden NULL is gelijk aan FALSE, zoals eerder genoemd.

Oplossing: gebruik de operator IS DISTINCT FROM van ROW-operator, waarbij direct hele records worden vergeleken:

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

Probleem #2: verschillende implementatie van dezelfde functionaliteit

Laten we vergelijken:

NEW."Document_" = (select '"Complete"'::regclass::oid)
NEW."Document_" = (select to_regclass('"DocumentVoorLoon"')::oid)

Waarom zijn er hier extra geneste SELECT? А функция to_regclass? А по-разному-то почему?..

Laten we het corrigeren:

NEW."Document_" = '"Complete"'::regclass::oid
NEW."Document_" = '"DocumentVoorLoon"'::regclass::oid

Probleem #3: prioriteit van bool-operaties

Laten we de broncode formatteren:

{... IS NULL} OR
{... Complete} OR
{... DocumentVoorLoon} AND
( {... ongelijkheden} )

Oeps… In feite bleek dat als een van de eerste twee voorwaarden waar is, de hele voorwaarde volledig omkeert TRUE, zonder rekening te houden met ongelijkheden. En dat is helemaal niet wat we wilden.

Laten we het corrigeren:

(
  {... IS NULL} OR
  {... Complete} OR
  {... DocumentVoorLoon}
) AND
( {... ongelijkheden} )

Probleem #4 (klein): complexe OF-voorwaarde voor één veld

Eigenlijk zijn de problemen in #3 precies ontstaan omdat er drie voorwaarden waren. Maar in plaats daarvan kan het ook met één worden gedaan, met behulp van het mechanisme coalesce ... IN:

coalesce(NEW."Document_"::text, '') IN ('', '"Complete"', '"DocumentVoorLoon"')

Zo kunnen we zowel NULL "pakken", en ingewikkeld OR met haakjes hoeven we niet te rommelen.

Total

Laten we vastleggen wat we hebben bereikt:

IF (
  coalesce(NEW."Document_"::text, '') IN ('', '"Set"', '"DocumentOverSalaris"') AND
  (
    OLD."DocumentOnzeOrganisatie"
  , OLD."Verwijderd"
  , OLD."Datum"
  , OLD."Tijd"
  , OLD."GemaaktDoor"
  ) IS DISTINCT FROM (
    NEW."DocumentOnzeOrganisatie"
  , NEW."Verwijderd"
  , NEW."Datum"
  , NEW."Tijd"
  , NEW."GemaaktDoor"
  )
) THEN ...

En als we in aanmerking nemen dat deze triggerfunctie alleen kan worden toegepast in UPDATE-trigger vanwege de aanwezigheid van OLD/NEW in de bovenste voorwaarde, dan kan deze voorwaarde überhaupt worden verplaatst naar WHEN-voorwaarde, zoals getoond in #1…

Bron: habr.com