🥇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

У словник нам достатньо помістити ID автора та його ім'я, щоб потім мати можливість витягти за цим ключем:

-- формируем целевую выборку
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 як аліас до повного запису таблиці person і зібрали їх масив.
цей масив записів перекастували у масив текстових рядків (person[]::text[]), щоб помістити його в hstore-словник як масив значень.
При отриманні пов'язаного запису ми його витягли зі словника по ключу як текстовий рядок.
Текст нам потрібен перетворити на значення типу таблиці person (для кожної таблиці автоматично створюється однойменний тип).
«Розгорнули» типізований запис у стовпці за допомогою (...).*.

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-[де]серіалізацію досить великі, тому в такий спосіб розумно користуватися лише у деяких випадках, коли «чесний» 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.

Підсумкові висновки:

якщо треба зробити JOIN із багаторазово повторюваними записами - краще використовувати «ословарювання» таблиці

якщо ваш словник очікувано маленький і читати ви з нього трохи - Можна використовувати json[b]

у всіх інших випадках hstore + array_agg(i::text) буде ефективніше

Джерело: habr.com

PostgreSQL Antipatterns: вдаримо словником по тяжкому JOIN

Багато «фактів» за обмеженим словником

hstore-словник

Записи таблиці: серіалізація та десеріалізація

json-словник

Тестуємо продуктивність

Додати коментар або відгук Скасувати відповідь