PostgreSQL Antipatterns: condition evaluation in SQL

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

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

During the optimization of the query execution plan in PostgreSQL it may arbitrarily 'rearrange' equivalent conditions, not computing some of them for individual records, assigning them to the condition of the applicable index... In short, it's simplest to assume that you cannot control the order in which they will (and whether they will) be evaluated equivalent conditions.

Therefore, if you do want to control priority, you need to structurally make these conditions unequal using conditional expressions. and operators.

Data and working with it are the foundation of our SBIS complex,, so it is very important to us that operations on them are performed not only correctly but also efficiently. Let's look at specific examples where errors in expression evaluation may occur and where it is worth improving their efficiency.

#0: RTFM

Starting example from the documentation:

When the order of evaluation is important, it can be fixed using the CASEconstruction. For example, this way of avoiding division by zero in a statement WHERE 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 this 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 will not operate if the first condition is false. Let's correct this with nested IF:

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

Now let's take a closer look — the entire body of the trigger function turned out to be 'wrapped' in IF. This means that nothing prevents us from bringing this condition out of the procedure using WHEN-condition:

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

This approach guarantees saving server resources when the condition is false.

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

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

In an unfortunate case, it can happen that both EXISTS will be 'true', but both will be executed..

But if we know for sure that one of them is 'true' much more often (or 'false' — for AND-chain) — can't we somehow 'raise its priority' so that the second does not execute unnecessarily?

It turns out, you can — the algorithmic approach is relevant to the topic of the article PostgreSQL Antipatterns: a rare record will reach the middle of a JOIN.

Let's just 'put both these conditions under CASE':

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

In this case, we did not define ELSE-value, meaning if both conditions are false CASE will return NULL, which is interpreted as FALSE downward API support (simultaneously with this in WHERE-condition.

This example can be combined differently as well — according to taste:

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

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

We spent two days analyzing the reasons for the 'strange' triggering of this trigger — let's see why.

Source code:

IF( NEW."Document_" is null or NEW."Document_" = (select '"Set"'::regclass::oid) or NEW."Document_" = (select to_regclass('"SalaryDocument"')::oid)
     AND (   OLD."OurOrganizationDocument" <> NEW."OurOrganizationDocument"
          OR OLD."Deleted" <> NEW."Deleted"
          OR OLD."Date" <> NEW."Date"
          OR OLD."Time" <> NEW."Time"
          OR OLD."Creator" <> NEW."Creator" ) ) THEN ...

Problem #1: inequality does not account for NULL

Let's assume that all OLD-fields had a value NULL. What will happen?

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

And from the perspective of processing the condition NULL is equivalent FALSE, as mentioned above.

Solution: use the operator IS DISTINCT FROM from ROW- operator, comparing entire records at once:

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

Problem #2: different implementations of the same functionality

Let's compare:

NEW."Document_" = (select '"Set"'::regclass::oid)
NEW."Document_" = (select to_regclass('"SalaryDocument"')::oid)

Why the unnecessary nested here SELECT? А функция to_regclass? А по-разному-то почему?..

Let's fix it:

NEW."Document_" = '"Set"'::regclass::oid
NEW."Document_" = '"SalaryDocument"'::regclass::oid

Problem #3: precedence of bool operations

Let's format the source:

{... IS NULL} OR
{... Set} OR
{... SalaryDocument} AND
( {... inequalities} )

Oops… In fact, it turned out that if any of the first two conditions are true, the entire condition turns into TRUE, without accounting for the inequalities. And that is not at all what we wanted.

Let's fix it:

(
  {... IS NULL} OR
  {... Set} OR
  {... SalaryDocument}
) AND
( {... inequalities} )

Problem #4 (small): complex OR condition for a single field

Actually, we encountered the problems in #3 precisely because there were three conditions. But instead, we can manage with one, using the mechanism coalesce ... IN:

coalesce(NEW."Document_"::text, '') IN ('', '"Set"', '"SalaryDocument"')

This way we will both NULL ‘capture’, and won’t have to deal with complex OR brackets.

Total

Let's summarize what we have achieved:

IF (
  coalesce(NEW."Document_"::text, '') IN ('', '"Set"', '"PayrollDocument"') AND
  (
    OLD."OurOrganizationDocument"
  , OLD."Deleted"
  , OLD."Date"
  , OLD."Time"
  , OLD."CreatedBy"
  ) IS DISTINCT FROM (
    NEW."OurOrganizationDocument"
  , NEW."Deleted"
  , NEW."Date"
  , NEW."Time"
  , NEW."CreatedBy"
  )
) THEN ...

And considering that this trigger function can only be applied in UPDATE-trigger due to the presence of OLD/NEW in the top-level condition, this condition can be entirely moved to a WHEN-condition, as shown in #1…

Source: habr.com