🥇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-слоўнік

Тэстуем прадукцыйнасць

Дадаць каментар Адмяніць адказ