ProHoster > Blog > Administrácia > PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Pokračujeme v sérii článkov venovaných štúdiu málo známych spôsobov, ako zlepšiť výkon „zdanlivo jednoduchých“ dopytov PostgreSQL:

Nemyslite si, že sa mi JOIN až tak nepáči... :)

Ale často bez neho sa žiadosť ukáže byť podstatne produktívnejšia ako s ňou. Tak dnes skúsime zbaviť sa JOIN náročného na zdroje - pomocou slovníka.

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Počnúc PostgreSQL 12 môžu byť niektoré situácie opísané nižšie reprodukované mierne odlišne predvolená nezhmotnená CTE. Toto správanie je možné vrátiť zadaním kľúča MATERIALIZED.

Veľa „faktov“ v obmedzenej slovnej zásobe

Zoberme si veľmi reálnu aplikačnú úlohu – musíme zobraziť zoznam prichádzajúce správy alebo aktívne úlohy s odosielateľmi:

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

V abstraktnom svete by mali byť autori úloh rovnomerne rozdelení medzi všetkých zamestnancov našej organizácie, ale v skutočnosti úlohy spravidla prichádzajú od pomerne obmedzeného počtu ľudí - „od manažmentu“ po hierarchii alebo „od subdodávateľov“ zo susedných oddelení (analytici, dizajnéri, marketing, ...).

Pripusťme, že v našej organizácii 1000 ľudí len 20 autorov (zvyčajne ešte menej) stanovuje úlohy pre každého konkrétneho interpreta a Využime tieto poznatky z predmetuna urýchlenie „tradičného“ dotazu.

Generátor skriptov

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

Ukážme si posledných 100 úloh pre konkrétneho vykonávateľa:

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;

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN
[pozrite sa na explain.tensor.ru]

Ukazuje sa, že 1/3 celkového času a 3/4 meraní strany údajov boli urobené len na vyhľadanie autora 100-krát - pre každú výstupnú úlohu. Ale vieme, že medzi týmito stovkami len 20 rôznych - Je možné tieto znalosti využiť?

hstore-slovník

Využime to typ hstore na vygenerovanie páru kľúč – hodnota „slovník“:

CREATE EXTENSION hstore

Potrebujeme len vložiť ID autora a jeho meno do slovníka, aby sme potom mohli extrahovať pomocou tohto kľúča:

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

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN
[pozrite sa na explain.tensor.ru]

Vynaložené na získavanie informácií o osobách 2-krát menej času a 7-krát menej prečítaných dát! Okrem „slovnej zásoby“ nám k týmto výsledkom pomohlo aj to hromadné vyhľadávanie záznamov zo stola jedným prechodom pomocou = ANY(ARRAY(...)).

Položky tabuľky: Serializácia a Deserializácia

Čo ak však potrebujeme uložiť nielen jedno textové pole, ale celý záznam v slovníku? V tomto prípade nám pomôže schopnosť PostgreSQL zaobchádzať so záznamom tabuľky ako s jednou hodnotou:

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

Pozrime sa, čo sa tu dialo:

  1. Zobrali sme p ako alias pre úplný záznam v tabuľke osôb a zostavil z nich rad.
  2. Tento pole nahrávok bolo prepracované na pole textových reťazcov (person[]::text[]), aby ste ho umiestnili do slovníka hstore ako pole hodnôt.
  3. Keď dostaneme súvisiaci záznam, my vytiahnuté zo slovníka kľúčom ako textový reťazec.
  4. Potrebujeme text zmeniť na hodnotu typu tabuľky osoba (pre každú tabuľku sa automaticky vytvorí typ s rovnakým názvom).
  5. „Rozbaľte“ napísaný záznam do stĺpcov pomocou (...).*.

json slovník

Ale taký trik, aký sme použili vyššie, nebude fungovať, ak neexistuje zodpovedajúci typ tabuľky na vykonanie „castingu“. Presne rovnaká situácia nastane, a ak sa pokúsime použiť riadok CTE, nie „skutočnú“ tabuľku.

V tomto prípade nám pomôžu funkcie pre prácu s 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;

Treba si uvedomiť, že pri popise cieľovej štruktúry nemôžeme vypísať všetky polia zdrojového reťazca, ale len tie, ktoré skutočne potrebujeme. Ak máme „natívnu“ tabuľku, potom je lepšie použiť funkciu json_populate_record.

Do slovníka ešte raz pristupujeme, ale json-[de]serializačné náklady sú pomerne vysoké, preto je rozumné použiť túto metódu len v niektorých prípadoch, keď sa „poctivý“ CTE Scan prejaví horšie.

Testovanie výkonu

Máme teda dva spôsoby serializácie údajov do slovníka − hstore/json_object. Samotné polia kľúčov a hodnôt možno navyše generovať dvoma spôsobmi, s internou alebo externou konverziou na text: array_agg(i::text) / array_agg(i)::text[].

Poďme skontrolovať účinnosť rôznych typov serializácie pomocou čisto syntetického príkladu - serializovať rôzne čísla kľúčov:

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

Hodnotiaci skript: serializácia

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 Antipatterns: Dictionary Hit Heavy JOIN

Na PostgreSQL 11 až približne do veľkosti slovníka 2^12 kľúčov serializácia do json trvá menej času. V tomto prípade je najefektívnejšia kombinácia konverzie typu json_object a „interného“. array_agg(i::text).

Teraz sa pokúsme prečítať hodnotu každého kľúča 8-krát - koniec koncov, ak nemáte prístup do slovníka, prečo je to potrebné?

Hodnotiaci scenár: čítanie zo slovníka

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;

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

A... už približne s 2^6 kľúčmi sa čítanie zo slovníka json viackrát stráca čítanie z hstore, pre jsonb sa to isté stane na 2^9.

Konečné závery:

  • ak to potrebujete urobiť PRIPOJTE sa k viacerým opakujúcim sa záznamom — je lepšie použiť „slovník“ tabuľky
  • ak sa očakáva váš slovník malá a veľa sa z nej nedočítate - môžete použiť json[b]
  • vo všetkých ostatných prípadoch hstore + array_agg(i::text) bude efektívnejšia

Zdroj: hab.com

Pridať komentár