🥇 Antipatterns PostgreSQL: بیایید با فرهنگ لغت به 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;

[به توضیح.tensor.ru نگاه کنید]

معلوم است که 1/3 کل زمان و 3/4 خواندن страниц данных были сделаны только для того, чтобы 100 раз поискать автора — для каждой выводимой задачи. Но мы же знаем, что среди этой сотни всего 20 разных - آیا می توان از این دانش استفاده کرد؟

hstore-dictionary

بهره ببریم типом 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;

[به توضیح.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 به عنوان نام مستعار ورودی جدول شخص کامل و آرایه ای از آنها را جمع آوری کرد.
این массив записей перекастовали به آرایه ای از رشته های متنی (person[]::text[]) تا آن را به عنوان آرایه ای از مقادیر در فرهنگ لغت 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 Scan показывает себя хуже.

تست عملکرد

بنابراین، ما دو راه برای سریال کردن داده ها در یک فرهنگ لغت داریم - 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-dictionary

ورودی های جدول: سریال سازی و سریال زدایی

دیکشنری json

تست عملکرد

اضافه کردن نظر انصراف از پاسخ