PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Við höldum áfram greinaröðinni sem helguð er rannsókn á lítt þekktum leiðum til að bæta árangur „að því er virðist einföld“ PostgreSQL fyrirspurnir:

• sjaldgæft met mun ná miðju JOIN
• Sisyphean JOIN fylki
• skaðleg JOIN og OR
• CTE GANGist í CTE

Ekki halda að mér líki ekki svo vel við JOIN... :)

En oft án þess reynist beiðnin vera verulega afkastameiri en með henni. Svo í dag reynum við losna við auðlindafrekt JOIN - með því að nota orðabók.

Frá og með PostgreSQL 12 geta sumar aðstæður sem lýst er hér að neðan verið endurskapaðar aðeins öðruvísi vegna sjálfgefið óverjandi CTE. Hægt er að snúa þessari hegðun til baka með því að tilgreina lykilinn MATERIALIZED.

Fullt af „staðreyndum“ í takmörkuðum orðaforða

Við skulum taka mjög raunverulegt umsóknarverkefni - við þurfum að birta lista skilaboð sem berast eða virk verkefni með sendendum:

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

Í óhlutbundnum heimi ættu verkefnahöfundar að vera jafnt dreift á alla starfsmenn stofnunarinnar okkar, en í raun og veru verkefni koma að jafnaði frá frekar takmörkuðum fjölda fólks - „frá stjórnendum“ upp stigveldið eða „frá undirverktökum“ frá nálægum deildum (sérfræðingar, hönnuðir, markaðssetning, ...).

Við skulum sætta okkur við að í okkar 1000 manna skipulagi, setja aðeins 20 höfundar (oftast jafnvel færri) verkefni fyrir hvern tiltekinn flytjanda og Notum þessa fagþekkingutil að flýta fyrir "hefðbundinni" fyrirspurn.

Script rafall

-- сотрудники
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);

Sýnum síðustu 100 verkefnin fyrir tiltekinn framkvæmdastjóra:

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;

[horfðu á explain.tensor.ru]

Það kemur í ljós að 1/3 heildartími og 3/4 lestur gagnasíður voru aðeins gerðar til að leita að höfundinum 100 sinnum - fyrir hvert úttaksverkefni. En við vitum að meðal þessara hundruða aðeins 20 mismunandi - Er hægt að nýta þessa þekkingu?

hstore-orðabók

Nýtum okkur hstore gerð til að búa til „orðabók“ lykilgildi:

CREATE EXTENSION hstore

Við þurfum bara að setja auðkenni höfundar og nafn hans í orðabókina svo að við getum síðan dregið út með þessum lykli:

-- формируем целевую выборку
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;

[horfðu á explain.tensor.ru]

Varið í að afla upplýsinga um einstaklinga 2 sinnum styttri tíma og 7 sinnum minni gögn lesin! Auk „orðaforða“ var það sem hjálpaði okkur að ná þessum árangri endurheimt magnskráa frá borðinu í einni umferð með því að nota = ANY(ARRAY(...)).

Töflufærslur: Serialization og deserialization

En hvað ef við þurfum að vista ekki bara einn textareit heldur heila færslu í orðabókinni? Í þessu tilviki mun hæfni PostgreSQL hjálpa okkur meðhöndla töflufærslu sem eitt gildi:

...
, 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;

Við skulum skoða hvað var í gangi hér:

1. Við tókum p sem samnefni við töflufærsluna fyrir fulla persónu og setti saman fjölda þeirra.
2. Þetta upptökurnar voru endursteyptar í fjölda textastrengja (persóna[]::texti[]) til að setja hann í hstore orðabókina sem fylki gilda.
3. Þegar við fáum tengda skrá, við dregið úr orðabókinni með lykli sem textastrengur.
4. Okkur vantar texta breytast í töflutegundargildi manneskja (fyrir hvert borð er sjálfkrafa búin til tegund með sama nafni).
5. „Stækkaðu“ vélritaða færsluna í dálka með því að nota (...).*.

json orðabók

En slík bragð eins og við notuðum hér að ofan mun ekki virka ef það er engin samsvarandi borðtegund til að gera „steypu“. Nákvæmlega sama staða mun koma upp, og ef við reynum að nota CTE röð, ekki "alvöru" borð.

Í þessu tilfelli munu þeir hjálpa okkur aðgerðir til að vinna með 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;

Það skal tekið fram að þegar markskipulaginu er lýst, getum við ekki skráð alla reiti upprunastrengsins, heldur aðeins þá sem við þurfum í raun og veru. Ef við erum með „innfædda“ töflu, þá er betra að nota aðgerðina json_populate_record.

Við komum samt einu sinni í orðabókina, en json-[de]serialization kostnaður er nokkuð hár, þess vegna er sanngjarnt að nota þessa aðferð aðeins í sumum tilfellum þegar „heiðarleg“ CTE Scan sýnir sig verri.

Prófa frammistöðu

Svo, við höfum tvær leiðir til að raða gögnum í orðabók - hstore/json_object. Að auki er einnig hægt að búa til fylki lykla og gilda sjálfra á tvo vegu, með innri eða ytri umbreytingu í texta: array_agg(i::text) / array_agg(i)::text[].

Við skulum athuga virkni mismunandi tegunda raðgreiningar með því að nota eingöngu tilbúið dæmi - setja mismunandi fjölda lykla í röð:

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

Matshandrit: serialization

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, allt að um það bil orðabókarstærð 2^12 lykla Serialization í json tekur styttri tíma. Í þessu tilfelli er áhrifaríkasta samsetningin af json_object og „innri“ gerð umbreytingar array_agg(i::text).

Nú skulum við reyna að lesa gildi hvers takka 8 sinnum - þegar allt kemur til alls, ef þú hefur ekki aðgang að orðabókinni, hvers vegna er það þá þörf?

Matshandrit: lesið úr orðabók

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;

Og... þegar um það bil með 2^6 lyklum byrjar lestur úr json orðabók að tapast mörgum sinnum að lesa úr hstore, fyrir jsonb gerist það sama við 2^9.

Lokaályktanir:

• ef þú þarft að gera það JOIN með mörgum endurteknum færslum - það er betra að nota "orðabók" af töflunni
• ef von er á orðabókinni þinni lítið og þú munt ekki lesa mikið úr því - þú getur notað json[b]
• í öllum öðrum tilvikum hstore + array_agg(i::texti) verður skilvirkari

Heimild: www.habr.com