PostgreSQL Antipatterns: нека ударим тежкия JOIN с речник

Продължаваме поредицата от статии, посветени на изучаването на малко известни начини за подобряване на производителността на „привидно прости“ заявки на PostgreSQL:

• рядък запис ще достигне средата на JOIN
• Сизифови JOIN масиви
• вредни JOIN и OR
• CTE ПРИСЪЕДИНЕТЕ СЕ К CTE

Не си мислете, че не харесвам 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;

[вижте expand.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;

[вижте expand.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;

Нека да видим какво се случва тук:

1. Взехме p като псевдоним на записа в пълната таблица с лица и събра масив от тях.
2. това масивът от записи беше преработен към масив от текстови низове (person[]::text[]), за да го поставите в речника на hstore като масив от стойности.
3. Когато получим свързан запис, ние изтеглен от речника по ключ като текстов низ.
4. Имаме нужда от текст превърне в стойност от тип таблица човек (за всяка таблица автоматично се създава тип със същото име).
5. „Разширете“ въведения запис в колони с помощта на (...).*.

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]разходите за сериализация са доста високи, следователно е разумно да се използва този метод само в някои случаи, когато „честното“ 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::текст) ще бъде по-ефективен

Източник: www.habr.com