PostgreSQL Antipatterns: הערכת מצב ב-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: שרשרת OR/AND

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

אחרת, אתה יכול לגמור עם שניהם EXISTS יהיה "נכון", אבל שניהם יתגשמו.

אבל אם אנחנו יודעים בוודאות שאחד מהם הוא "נכון" לעתים קרובות יותר (או "שקר" - עבור AND-שרשרות) - האם ניתן איכשהו "להגדיל את העדיפות שלו" כך שהשני לא יבוצע שוב?

מסתבר שזה אפשרי - הגישה האלגוריתמית קרובה לנושא המאמר PostgreSQL Antipatterns: שיא נדיר יגיע לאמצע JOIN.

בואו פשוט "נדחוף" את שני התנאים הללו תחת CASE:

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 (קטנה): תנאי OR מורכב עבור שדה אחד

למעשה, היו לנו בעיות במס' 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