PostgreSQL Antipatterns: "חייב להישאר רק אחד!"

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

היום, בעזרת דוגמאות פשוטות ביותר, בואו נראה למה זה יכול להוביל בהקשר של שימוש GROUP/DISTINCT и LIMIT איתם.

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

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

טוב, אולי לא כל כך מרהיב, אבל...

"זוג מתוק": JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

יהיה ברור מה הם רוצים בחר רשומות X שעבורן יש רשומות ב-Y הקשורות לתנאי שהתמלא. כתב בקשה דרך JOIN - קיבל כמה ערכי pk מספר פעמים (בדיוק כמה ערכים מתאימים הופיעו ב-Y). איך להסיר? בְּהֶחלֵט DISTINCT!

זה "משמח" במיוחד כאשר לכל רשומה X יש כמה מאות רשומות Y קשורות, ואז הכפילויות מוסרות בגבורה...

איך לתקן? מלכתחילה, הבינו שניתן לשנות את הבעיה "בחר רשומות X שעבורן ב-Y יש לפחות אחת המשויכת לתנאי שהתמלא" - אחרי הכל, אנחנו לא צריכים שום דבר מתקליט ה-Y עצמו.

מקונן קיים

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

חלק מהגרסאות של PostgreSQL מבינות שב-EXISTS מספיק למצוא את הערך הראשון שעולה, ישנים יותר לא. לכן אני מעדיף לציין תמיד LIMIT 1 בתוך EXISTS.

הצטרפות לרוחב

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

אותה אפשרות מאפשרת, במידת הצורך, להחזיר מיידית חלק מהנתונים מתוך רשומת ה-Y המשויכת שנמצאה. אפשרות דומה נידונה במאמר "PostgreSQL Antipatterns: שיא נדיר יגיע לאמצע JOIN".

"למה לשלם יותר": DISTINCT [ON] + LIMIT 1

יתרון נוסף של טרנספורמציות שאילתות כאלה הוא היכולת להגביל בקלות את החיפוש אחר רשומות אם יש צורך רק באחת או כמה מהן, כמו במקרה הבא:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

כעת אנו קוראים את הבקשה ומנסים להבין מה ה-DBMS מוצע לעשות:

• מחברים את השלטים
• ייחודי על ידי X.pk
• מבין הערכים הנותרים, בחר אחד

אז מה קיבלת? "רק כניסה אחת" מהייחודיים - ואם ניקח את זה מהלא הייחודיים, האם התוצאה תשתנה איכשהו?.. "ואם אין הבדל, למה לשלם יותר?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

ובדיוק אותו נושא עם GROUP BY + LIMIT 1.

"אני רק צריך לשאול": GROUP + LIMIT מרומז

דברים דומים מתרחשים באחרים בדיקות אי-ריקנות סימנים או CTE עם התקדמות הבקשה:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

פונקציות צבירה (count/min/max/sum/...) מבוצעות בהצלחה על כל הסט, אפילו ללא הוראות מפורשות GROUP BY. רק עם LIMIT הם לא מאוד ידידותיים.

היזם יכול לחשוב "אם יש שם רשומות, אז אני לא צריך יותר מ-LIMIT". אבל אל תעשה את זה! כי לבסיס זה:

• לספור מה שהם רוצים לפי כל הרישומים
• לתת כמה שורות שהם מבקשים

בהתאם לתנאי היעד, ראוי לבצע אחת מההחלפות הבאות:

• (count + LIMIT 1) = 0 על NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 על EXISTS(LIMIT 1)
• count >= N על (SELECT count(*) FROM (... LIMIT N))

"כמה לשקול בגרמים": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

מפתח נאיבי עשוי להאמין באמת ובתמים שהבקשה תפסיק לבצע. ברגע שאנו מוצאים $1 מהערכים השונים הראשונים שנתקלים בהם.

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

לעת עתה קודם כל כל הרשומות יאוחזרו, ייחודיים, ורק מהם יוחזר הסכום המבוקש. זה עצוב במיוחד אם רצינו משהו כזה $ 1 = 4, ויש מאות אלפי רשומות בטבלה...

כדי לא להיות עצובים לשווא, בואו נשתמש בשאילתה רקורסיבית "DISTINCT הוא לעניים" מתוך PostgreSQL Wiki:

מקור: www.habr.com