🥇 Αντιπρότυπα PostgreSQL: Αξιολόγηση συνθηκών σε SQL

Η SQL δεν είναι C++, ούτε JavaScript. Επομένως, η αξιολόγηση των λογικών εκφράσεων είναι διαφορετική, και αυτό δεν είναι καθόλου το ίδιο πράγμα:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Κατά τη βελτιστοποίηση του σχεδίου εκτέλεσης ενός ερωτήματος PostgreSQL μπορεί αυθαίρετα να «αναδιατάξει» τις ισοδύναμες συνθήκες, μην υπολογίσετε κανένα από αυτά για μεμονωμένες εγγραφές, ανατρέξτε στην συνθήκη του εφαρμοζόμενου δείκτη ... Εν ολίγοις, ο ευκολότερος τρόπος είναι να υποθέσετε ότι δεν μπορεί να διαχειριστεί τη σειρά με την οποία θα είναι (και αν θα υπολογιστούν καθόλου) ίσος συνθήκες.

Επομένως, εάν εξακολουθείτε να θέλετε να διαχειριστείτε την προτεραιότητα, πρέπει να το κάνετε δομικά καθιστούν αυτές τις συνθήκες άνισες με υπό όρους εκφράσεις и χειριστές.

Τα δεδομένα και η συνεργασία με αυτά είναι η βάση του συγκροτήματος VLSI μας, επομένως είναι πολύ σημαντικό για εμάς οι λειτουργίες σε αυτά να εκτελούνται όχι μόνο σωστά, αλλά και αποτελεσματικά. Ας δούμε συγκεκριμένα παραδείγματα όπου μπορούν να γίνουν λάθη στην αξιολόγηση της έκφρασης και όπου αξίζει να βελτιωθεί η αποτελεσματικότητά τους.

#0: RTFM

Εκκίνηση παράδειγμα από τεκμηρίωση:

Όταν η σειρά αξιολόγησης είναι σημαντική, μπορεί να διορθωθεί με την κατασκευή CASE. Για παράδειγμα, με αυτόν τον τρόπο για να αποφύγετε τη διαίρεση με το μηδέν σε μια πρόταση WHERE αναξιόπιστος:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Ασφαλής επιλογή:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Η κατασκευή που χρησιμοποιήθηκε CASE προστατεύει την έκφραση από βελτιστοποίηση, επομένως θα πρέπει να χρησιμοποιείται μόνο όταν είναι απαραίτητο.

#1: κατάσταση ενεργοποίησης

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

Όλα δείχνουν να φαίνονται καλά, αλλά... Κανείς δεν υπόσχεται ότι η επένδυση SELECT δεν θα εκτελεστεί εάν η πρώτη συνθήκη είναι ψευδής. Διορθώστε το με φωλιασμένος IF:

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

Τώρα ας δούμε προσεκτικά - ολόκληρο το σώμα της λειτουργίας σκανδάλης αποδείχθηκε ότι ήταν "τυλιγμένο". IF. Και αυτό σημαίνει ότι τίποτα δεν μας εμποδίζει να αφαιρέσουμε αυτήν την κατάσταση από τη διαδικασία που χρησιμοποιεί WHEN-συνθήκες:

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

Αυτή η προσέγγιση σάς επιτρέπει να εξοικονομήσετε πόρους διακομιστή με εγγύηση εάν η συνθήκη είναι ψευδής.

#2: Ή/ΚΑΙ αλυσίδα

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

Διαφορετικά, μπορεί να ληφθεί ότι και τα δύο EXISTS θα είναι αλήθεια, αλλά θα εκτελεστούν και τα δύο.

Αλλά αν γνωρίζουμε με βεβαιότητα ότι ένα από αυτά είναι "αληθινό" πολύ πιο συχνά (ή "ψεύτικο" - για AND-αλυσίδες) - είναι δυνατόν με κάποιο τρόπο να «αυξήσει την προτεραιότητά του» ώστε να μην εκτελεστεί για άλλη μια φορά το δεύτερο;

Αποδεικνύεται ότι είναι δυνατό - η αλγοριθμική προσέγγιση είναι κοντά στο θέμα του άρθρου Αντιπρότυπα PostgreSQL: Η σπάνια καταχώρηση φτάνει στη μέση ενός JOIN.

Ας βάλουμε απλώς «υπό ΠΕΡΙΠΤΩΣΗ» και τις δύο αυτές προϋποθέσεις:

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

Σε αυτή την περίπτωση δεν προσδιορίσαμε ELSE-τιμή, δηλαδή αν και οι δύο συνθήκες είναι ψευδείς CASE θα επιστρέψει NULL, το οποίο ερμηνεύεται ως FALSE в WHERE- συνθήκες.

Αυτό το παράδειγμα μπορεί να συνδυαστεί με άλλο τρόπο - για γεύση και χρώμα:

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

#3: πώς να [μη] γράψετε συνθήκες

Περάσαμε δύο ημέρες για να αναλύσουμε τους λόγους για την «περίεργη» ενεργοποίηση αυτής της σκανδάλης - ας δούμε γιατί.

Πηγή:

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

Πρόβλημα #1: Η ανισότητα δεν λαμβάνει υπόψη το NULL

Ας υποθέσουμε ότι τα πάντα OLD- τα χωράφια είχαν σημασία NULL. Τι θα συμβεί?

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

Και από την άποψη της επεξεργασίας των συνθηκών NULL ισοδύναμος FALSE, όπως αναφέρθηκε προηγουμένως.

Λύση: χρήση τελεστή IS DISTINCT FROM από ROW- χειριστή, συγκρίνοντας ταυτόχρονα ολόκληρες εγγραφές:

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

Πρόβλημα νούμερο 2: διαφορετική υλοποίηση της ίδιας λειτουργικότητας

Συγκρίνετε:

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

Γιατί υπάρχουν επιπλέον επενδύσεις SELECT? Μια λειτουργία to_regclass? Γιατί είναι διαφορετικό...

Ας διορθώσουμε:

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

Πρόβλημα #3: bool προτεραιότητα

Ας μορφοποιήσουμε την πηγή:

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

Ωχ... Στην πραγματικότητα, αποδείχθηκε ότι στην περίπτωση της αλήθειας οποιασδήποτε από τις δύο πρώτες συνθήκες, ολόκληρη η συνθήκη μετατρέπεται σε TRUE, αγνοώντας τις ανισότητες. Και αυτό δεν είναι καθόλου αυτό που θέλαμε.

Ας διορθώσουμε:

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

Πρόβλημα #4 (μικρό): σύνθετη συνθήκη Ή για ένα πεδίο

Ουσιαστικά είχαμε προβλήματα στο Νο3 ακριβώς επειδή υπήρχαν τρεις προϋποθέσεις. Αλλά αντί για αυτά, μπορείτε να τα βγάλετε πέρα με ένα, χρησιμοποιώντας τον μηχανισμό coalesce ... IN:

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

Το ίδιο και εμείς NULL «πιάσε», και σύνθετο OR Δεν χρειάζεται να μπερδεύεστε με παρενθέσεις.

Σε συνολικά

Ας διορθώσουμε αυτό που πήραμε:

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

Και δεδομένου ότι αυτή η λειτουργία ενεργοποίησης μπορεί να χρησιμοποιηθεί μόνο σε UPDATEέναυσμα λόγω της παρουσίας OLD/NEW στην κατάσταση ανώτερου επιπέδου, τότε αυτή η κατάσταση μπορεί γενικά να εξαλειφθεί WHEN-κατάσταση όπως φαίνεται στο #1...

Πηγή: www.habr.com

Αντιπρότυπα PostgreSQL: Αξιολόγηση συνθηκών σε SQL