🥇PostgreSQL Antipatterns: בואו נגיע ל-JOIN הכבד עם המילון

אנו ממשיכים את סדרת המאמרים המוקדשים למחקר של דרכים לא ידועות לשיפור הביצועים של שאילתות PostgreSQL "פשוטות לכאורה":

אל תחשוב שאני לא כל כך אוהב JOIN... :)

אבל לעתים קרובות בלעדיו, הבקשה מתבררת כפרודוקטיבית משמעותית מאשר איתה. אז היום ננסה להיפטר מ-JOIN עתיר משאבים - שימוש במילון.

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

הרבה "עובדות" באוצר מילים מצומצם

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

25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.

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

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

מחולל סקריפטים

-- сотрудники
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

בואו נראה את 100 המשימות האחרונות עבור מבצע ספציפי:

SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

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

מתברר כי 1/3 זמן כולל ו-3/4 קריאות דפי נתונים נעשו רק כדי לחפש את המחבר 100 פעמים - עבור כל משימת פלט. אבל אנחנו יודעים שבין מאות אלה רק 20 שונים - האם ניתן להשתמש בידע הזה?

hstore-מילון

בואו נשתמש סוג hstore כדי ליצור ערך מפתח "מילון":

CREATE EXTENSION hstore

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

-- формируем целевую выборку
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- формируем словарь для уникальных значений
, dict AS (
  SELECT
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  FROM
    person
  WHERE
    id = ANY(ARRAY(
      SELECT DISTINCT
        author_id
      FROM
        T
    ))
)
-- получаем связанные значения словаря
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

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

הושקעה בקבלת מידע על אנשים פי 2 פחות זמן ופי 7 פחות קריאת נתונים! בנוסף ל"אוצר המילים", מה שגם עזר לנו להשיג את התוצאות הללו היה אחזור רשומות בכמות גדולה מהשולחן במעבר יחיד באמצעות = ANY(ARRAY(...)).

ערכי טבלה: סדרה והדרה

אבל מה אם נצטרך לשמור לא רק שדה טקסט אחד, אלא ערך שלם במילון? במקרה זה, היכולת של PostgreSQL תעזור לנו להתייחס לערך בטבלה כערך יחיד:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- магия #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- магия #2
FROM
  T;

בואו נסתכל מה קרה כאן:

לקחנו p ככינוי לערך הטבלה של האדם המלא והרכיבו מערך מהם.
זה מערך ההקלטות היה מחדש למערך של מחרוזות טקסט (אדם[]::טקסט[]) כדי למקם אותו במילון hstore כמערך של ערכים.
כאשר אנו מקבלים רשומה קשורה, אנו נשלף מהמילון במפתח כמחרוזת טקסט.
אנחנו צריכים טקסט להפוך לערך סוג טבלה אדם (עבור כל טבלה נוצר אוטומטית סוג באותו שם).
"הרחב" את הרשומה המוקלדת לעמודות באמצעות (...).*.

מילון json

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

במקרה הזה הם יעזרו לנו פונקציות לעבודה עם json:

...
, p AS ( -- это уже CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- теперь это уже json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- и внутри json для каждой строки
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- извлекли из словаря как json
      ) AS j(name text, birth_date date) -- заполнили нужную нам структуру
  ) j;

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

אנחנו עדיין ניגשים למילון פעם אחת, אבל עלויות ה-json-[de]serialization גבוהות למדילכן, סביר להשתמש בשיטה זו רק במקרים מסוימים כאשר סריקת ה-CTE ה"כנה" מראה את עצמה גרועה יותר.

בדיקת ביצועים

אז יש לנו שתי דרכים לסידור נתונים למילון - hstore/json_object. בנוסף, ניתן ליצור את מערכי המפתחות והערכים עצמם גם בשתי דרכים, עם המרה פנימית או חיצונית לטקסט: array_agg(i::text) / array_agg(i)::text[].

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

WITH dict AS (
  SELECT
    hstore(
      array_agg(i::text)
    , array_agg(i::text)
    )
  FROM
    generate_series(1, ...) i
)
TABLE dict;

תסריט הערכה: סדרה

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

ב-PostgreSQL 11, עד גודל מילון בערך של 2^12 מפתחות הסידרה ל-json לוקחת פחות זמן. במקרה זה, היעיל ביותר הוא השילוב של json_object והמרה מסוג "פנימי". array_agg(i::text).

עכשיו בואו ננסה לקרוא את הערך של כל מפתח 8 פעמים - אחרי הכל, אם לא ניגשים למילון, אז למה זה נחוץ?

תסריט הערכה: קריאה מתוך מילון

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

ו... כבר בערך עם מקשים 2^6, הקריאה ממילון json מתחילה להפסיד מספר פעמים קריאה מ-hstore, עבור jsonb אותו דבר קורה ב-2^9.

מסקנות סופיות:

אם אתה צריך לעשות את זה הצטרף עם מספר רשומות שחוזרות על עצמן - עדיף להשתמש ב"מילון" של הטבלה

אם המילון שלך צפוי קטן ולא תקראו ממנו הרבה - אתה יכול להשתמש ב-json[b]

בכל שאר המקרים hstore + array_agg(i::text) יהיה יעיל יותר

מקור: www.habr.com

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

הרבה "עובדות" באוצר מילים מצומצם

hstore-מילון

ערכי טבלה: סדרה והדרה

מילון json

בדיקת ביצועים

הוספת תגובה ביטול תגובה