Home PostgreSQL PostgreSQL Antipatterns:Calculating Conditions in SQL

PostgreSQL Antipatterns:Calculating Conditions in SQL

by admin

SQL is not C++ or JavaScript.So calculating logical expressions is different, and this is not the same thing at all:

WHEREfncondX() ANDfncondY()

= fncondX() fncondY()

In the process of optimizing the PostgreSQL query execution plan can arbitrarily "rearrange" the equivalent conditions , not calculate some of them for individual records, relate them to the condition of the applied index…In short, it is easiest to consider that you in advance can’t control the order in which (and whether at all) the equal conditions.
So if you still want to control the priority, you must structurally make these conditions unequal using the conditional expressions and operators
PostgreSQL Antipatterns:Calculating Conditions in SQL
Data and working with it – the basis of our VLSI suite. so it is very important to us that operations on them are performed not only correctly, but also efficiently. Let’s look at concrete examples, where errors in expression calculation can be made, and where it is worth improving their efficiency.

#0:RTFM

Starter example from the documentation :

When the order of calculation is important, it can be fixed with the construction CASE For example, this way to avoid division by zero in the sentence WHERE is unreliable :

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

Safe option :

SELECT... WHERE CASEWHENx > 0 THEN y/x > 1.5 ELSEfalse END;

Construction applied in this way CASE protects the expression from optimization, so you should use it only if necessary.

#1:condition in trigger

BEGINIFcond(NEW.fld) AND EXISTS(SELECT ...) THEN...ENDIF;RETURN NEW;END;

Everything looks good, but… No one promises that the enclosed SELECT will not be executed if the first condition is false. Let’s correct this with nested IF :

BEGINIF cond(NEW.fld) THENIF EXISTS(SELECT ...) THEN...ENDIF;END IF;RETURN NEW;END;

Now let’s take a closer look – the entire body of the trigger function appears to be "wrapped" in IF And this means that there’s nothing to stop us from taking this condition out of the procedure with WHEN -conditions :

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

This approach is guaranteed to save server resources when the condition is false.

#2: OR/AND-chain

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

In the unpleasant case you can get that both EXISTS will be "true, " but both and will execute
But if we know for a fact that one of them is "true" many times more often (or "false" for AND -chain) – is there any way to "increase its priority" so that the second one is not executed unnecessarily?
It turns out you can – algorithmically the approach is close to the topic of the article PostgreSQL Antipatterns: a rare entry will make it to the middle of a JOIN
Let’s just "put under CASE" both of these conditions :

SELECT ...WHERECASEWHEN EXISTS(... A) THEN TRUEWHEN EXISTS(... B) THEN TRUEEND

In this case we have not defined ELSE -value, that is, if both conditions are false CASE will return NULL which is interpreted as FALSE in WHERE -conditions.
This example can be combined in other ways – for taste and color:

SELECT ...WHERECASEWHEN NOT EXISTS(... A) THEN EXISTS(... B)ELSE TRUEEND

#3: how [not] to write conditions

We spent two days trying to figure out why this trigger worked so strangely, so let’s see why.
Source :

IF( NEW."Document" is null or NEW."Document" = (select '"Set"'::regclass::oid) or NEW."Document" = (select to_regclass('"Documentable"')::oid)AND ( OLD."DocumentOurOrganization" <> NEW."DocumentOurOrganization"OR OLD."Deleted" <> NEW."Deleted"OR OLD."Date" <> NEW."Date"OR OLD."Time" <> NEW."Time"OR OLD."FACE CREATED"<> NEW."FACE created")) THEN ...

Problem #1: Inequality does not account for NULL

Suppose that all OLD -fields had the value NULL What do you get?

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

And in terms of working out the condition NULL equivalent to FALSE , as mentioned above.
Solution : use the operator IS DISTINCT FROM from ROW -operator, comparing whole records at once :

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

Problem #2: Different implementation of the same functionality

Let’s compare :

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

Why are there unnecessary nested SELECT ? And the function to_regclass ? Why differently?
Fix it :

NEW."Document_" = '"Kit"::regclass::oidNEW."Document_"='"document_party"'::regclass::oid

Problem #3: Priority of bool operations

Let’s format the source :

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

Oops… In fact, it turns out that if any of the first two conditions is true, the whole condition turns into TRUE without taking the inequalities into account. And this is not what we wanted at all.
Fix it :

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

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

We actually had problems in #3 precisely because there were three conditions. But you can get by with one instead, using the mechanism coalesce ... IN :

coalesce(NEW."Document"::text, ") IN (", '"Set"', '"Documentable"')

So we and NULL "catch, " and complex OR with brackets.

Total

Let’s record what we got :

IF (coalesce(NEW."Document_"::text, '') IN (', '"Set"', '"DocumentPayroll"') AND(OLD."DOCUMENTAORGANIZATION", OLD."Deleted", OLD."Date", OLD."Time", OLD."PersonCreated") IS DISTINCT FROM (NEW."Document Organization", NEW."Deleted", NEW."Date", NEW."Time", NEW."PersonCreated")) THEN ...

And considering that this trigger function can only be applied in UPDATE -trigger because of the presence of OLD/NEW condition in the top-level condition, then this condition can be put in the WHEN -condition, as was shown in #1…

You may also like