PostgreSQL Antipatterns: JOINs ו-ORs מזיקים

היזהרו מפעולות שמביאות חוצצים...
בעזרת שאילתה קטנה כדוגמה, בואו נסתכל על כמה גישות אוניברסליות לאופטימיזציה של שאילתות ב-PostgreSQL. אם אתה משתמש בהם או לא זה תלוי בך, אבל כדאי לדעת עליהם.

בחלק מהגרסאות הבאות של PG המצב עשוי להשתנות ככל שהמתזמן נעשה חכם יותר, אבל עבור 9.4/9.6 זה נראה בערך אותו הדבר, כמו בדוגמאות כאן.

בואו ניקח בקשה אמיתית מאוד:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

לגבי שמות טבלאות ושדותניתן להתייחס אחרת לשמות ה"רוסים" של שדות וטבלאות, אבל זה עניין של טעם. בגלל ה כאן בטנסור אין מפתחים זרים, ו-PostgreSQL מאפשר לנו לתת שמות אפילו בהירוגליפים, אם הם מוקף במרכאות, אז אנחנו מעדיפים לתת שם לאובייקטים בצורה חד משמעית וברורה כדי שלא יהיו אי התאמות.
בואו נסתכל על התוכנית שהתקבלה:

[הסתכל ב-explain.tensor.ru]

מאגרים של 144ms וכמעט 53K - כלומר, יותר מ-400MB של נתונים! ויהיה לנו מזל אם כולם יהיו במטמון עד לבקשתנו, אחרת זה ייקח הרבה יותר זמן בקריאה מהדיסק.

האלגוריתם הכי חשוב!

כדי לייעל איכשהו כל בקשה, תחילה עליך להבין מה היא צריכה לעשות.
נשאיר את הפיתוח של מבנה מסד הנתונים עצמו מחוץ לתחום המאמר הזה לעת עתה, ונסכים שנוכל יחסית "בזול" לשכתב את הבקשה ו/או לגלגל על ​​הבסיס כמה מהדברים שאנחנו צריכים - אינדקסים.

אז הבקשה:
- בודק את קיומו של מסמך כלשהו לפחות
- במצב שאנו צריכים ומסוג מסוים
- כאשר המחבר או המבצע הוא העובד שאנו צריכים

JOIN + LIMIT 1

לעתים קרובות יותר קל למפתח לכתוב שאילתה שבה מצטרפים תחילה מספר גדול של טבלאות, ואז נשארת רק רשומה אחת מכל הסט הזה. אבל קל יותר עבור המפתח לא אומר יעיל יותר עבור מסד הנתונים.
במקרה שלנו היו רק 3 שולחנות - ומה ההשפעה...

בוא נפטר קודם כל מהחיבור עם טבלת "סוג מסמך", ובמקביל נספר למסד הנתונים רשומת הסוג שלנו היא ייחודית (אנחנו יודעים את זה, אבל למתזמן אין מושג עדיין):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

כן, אם הטבלה/CTE מורכבת משדה בודד של רשומה בודדת, אז ב-PG אפשר אפילו לכתוב כך, במקום

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

הערכה עצלנית בשאילתות PostgreSQL

BitmapOr נגד UNION

במקרים מסוימים, Bitmap Heap Scan יעלה לנו הרבה – למשל במצב שלנו, כאשר לא מעט רשומות עומדות בתנאי הנדרש. קיבלנו את זה בגלל מצב OR הפך ל- BitmapOr- תפעול בתוכנית.
נחזור לבעיה המקורית - עלינו למצוא רשומה מתאימה לכל אחד מהתנאים - כלומר, אין צורך לחפש את כל רשומות 59K בשני התנאים. יש דרך לפתור תנאי אחד, ו עבור אל השני רק כאשר דבר לא נמצא בראשון. העיצוב הבא יעזור לנו:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"חיצוני" LIMIT 1 מבטיחה שהחיפוש יסתיים כאשר הרשומה הראשונה נמצאה. ואם הוא כבר נמצא בבלוק הראשון, הבלוק השני לא יתבצע (מעולם לא הוצא להורג לגבי).

"הסתרת תנאים קשים תחת CASE"

יש רגע מאוד לא נוח בשאילתה המקורית - בדיקת המצב מול הטבלה הקשורה "DocumentExtension". ללא קשר לאמיתותם של תנאים אחרים בביטוי (לדוגמה, ד. "נמחק" אינו נכון), חיבור זה מבוצע תמיד ו"עולה משאבים". פחות או יותר מהם יושקעו - תלוי בגודל השולחן הזה.
אבל אתה יכול לשנות את השאילתה כך שהחיפוש אחר רשומה קשורה יתרחש רק כאשר זה באמת נחוץ:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

פעם אחת מהטבלה המקושרת אלינו אין צורך באף אחד מהשדות לתוצאה, אז יש לנו הזדמנות להפוך את JOIN לתנאי בשאילתת משנה.
הבה נשאיר את השדות שצורפו לאינדקס "מחוץ לסוגריים של CASE", נוסיף תנאים פשוטים מהרשומה לבלוק WHEN - ועכשיו השאילתה ה"כבדה" מבוצעת רק בעת מעבר ל-THEN.

שם המשפחה שלי הוא "סה"כ"

אנו אוספים את השאילתה המתקבלת עם כל המכניקה שתוארה לעיל:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

התאמת [ל] אינדקסים

עין מאומנת הבחינה שתנאי האינדקס בתתי בלוקים של UNION שונים במקצת - זה בגלל שכבר יש לנו אינדקסים מתאימים על השולחן. ואם הם לא היו קיימים, כדאי ליצור: Document(Person3, DocumentType) и מסמך (DocumentType, Employee).
על סדר השדות בתנאי ROWמנקודת מבטו של המתכנן אפשר כמובן לכתוב (A, B) = (constA, constB)ו - (B, A) = (constB, constA). אבל בזמן ההקלטה לפי סדר השדות באינדקס, בקשה כזו פשוט נוחה יותר לניפוי באגים מאוחר יותר.
מה בתוכנית?

[הסתכל ב-explain.tensor.ru]

לרוע המזל, התמזל מזלנו ולא נמצא דבר בבלוק UNION הראשון, כך שהשני עדיין הוצא להורג. אבל גם כך - בלבד 0.037ms ו-11 מאגרים!
זירזנו את הבקשה וצמצמנו את שאיבת הנתונים בזיכרון כמה אלפי פעמים, באמצעות טכניקות פשוטות למדי - תוצאה טובה עם מעט העתק-הדבק. 🙂

מקור: www.habr.com