תבניות נגד PostgreSQL: CTE x CTE

בשל קו העבודה שלי, אני נאלץ להתמודד עם מצבים שבהם מפתח כותב בקשה וחושב "הבסיס חכם, הוא יכול להתמודד עם הכל בעצמו!«

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

ראשית, אתן דוגמה לבקשה כזו:

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

כדי להעריך באופן מהותי את איכות הבקשה, בואו ניצור מערך נתונים שרירותי:

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

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

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

מפרקים אותו חלק אחר חלק

בואו נסתכל מקרוב על הבקשה ונתלבט:

1. למה WITH RECURSIVE נמצא כאן אם אין CTEs רקורסיבי?
2. למה לקבץ ערכי מינימום/מקסימום ב-CTE נפרד אם הם קשורים אז למדגם המקורי בכל מקרה?
   +25% זמן
3. למה להשתמש ב'SELECT * FROM' ללא תנאי בסוף כדי לחזור על ה-CTE הקודם?
   +14% זמן

במקרה הזה, היה לנו מזל גדול ש-Hash Join נבחר לחיבור, ולא Nested Loop, כי אז היינו מקבלים לא רק כרטיס CTE Scan אחד, אלא 10K!

קצת על CTE Scanכאן עלינו לזכור זאת CTE Scan דומה ל-Seq Scan - כלומר, ללא אינדקס, אלא רק חיפוש שלם, שידרוש 10K x 0.3ms = 3000ms עבור מחזורים לפי cte_max או 1K x 1.5ms = 1500ms בעת לולאה על ידי cte_bind!
בעצם, מה רצית לקבל כתוצאה מכך? כן, בדרך כלל זו השאלה שעולה אי שם בדקה החמישית של ניתוח שאילתות "שלוש קומות".

רצינו להוציא עבור כל זוג מפתחות ייחודי min/max מקבוצה לפי key_a.
אז בואו נשתמש בזה בשביל זה פונקציות החלונות:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

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

מכיוון שקריאת נתונים בשתי האפשרויות לוקחת אותו דבר בערך 4-5ms, אז כל הזמן שלנו מרוויח -32% - זה בצורתו הטהורה ביותר עומס הוסר מהמעבד הבסיסי, אם בקשה כזו מבוצעת לעתים קרובות מספיק.

באופן כללי, אתה לא צריך להכריח את הבסיס "לסחוב את העגול, לגלגל את המרובע".

מקור: www.habr.com