🥇PostgreSQL Antipatterns: udarimo v težko JOIN s slovarjem

Nadaljujemo s serijo člankov, posvečenih preučevanju malo znanih načinov za izboljšanje učinkovitosti "na videz preprostih" poizvedb PostgreSQL:

Ne mislite, da mi JOIN ni tako všeč ... :)

Toda pogosto se brez njega zahteva izkaže za bistveno bolj produktivno kot z njim. Danes bomo torej poskusili znebite se JOIN-a, ki zahteva veliko virov - z uporabo slovarja.

Začenši s PostgreSQL 12 se lahko nekatere situacije, opisane spodaj, reproducirajo nekoliko drugače zaradi privzeti CTE brez materializacije. To vedenje je mogoče razveljaviti z navedbo ključa MATERIALIZED.

Veliko "dejstev" v omejenem besednjaku

Vzemimo zelo resnično nalogo aplikacije – prikazati moramo seznam dohodna sporočila ali aktivne naloge s pošiljatelji:

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

V abstraktnem svetu bi morali biti avtorji nalog enakomerno porazdeljeni med vse zaposlene v naši organizaciji, v resnici pa naloge praviloma prihajajo od dokaj omejenega števila ljudi - »od vodstva« navzgor po hierarhiji ali »od podizvajalcev« iz sosednjih oddelkov (analitiki, projektanti, marketing, ...).

Sprejmimo, da v naši 1000-članski organizaciji samo 20 avtorjev (običajno še manj) postavlja naloge za vsakega posameznega izvajalca in Uporabimo to znanje predmetaza pospešitev "tradicionalne" poizvedbe.

Generator 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);

Pokažimo zadnjih 100 opravil za določenega izvajalca:

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;

[ogled na expand.tensor.ru]

Izkazalo se je, da 1/3 skupnega časa in 3/4 branja strani s podatki so bile narejene le za 100-kratno iskanje avtorja - za vsako izhodno nalogo. Vemo pa, da med temi stotimi samo 20 različnih - Ali je to znanje mogoče uporabiti?

hstore-dictionary

Izkoristimo vrsta hstore za ustvarjanje ključa-vrednosti »slovarja«:

CREATE EXTENSION hstore

Samo ID avtorja in njegovo ime moramo vnesti v slovar, da lahko nato ekstrahiramo s tem ključem:

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

[ogled na expand.tensor.ru]

Porabili za pridobivanje podatkov o osebah 2-krat manj časa in 7-krat manj prebranih podatkov! Poleg »besednega zaklada« nam je do teh rezultatov pomagalo tudi množično iskanje zapisov iz tabele v enem prehodu z uporabo = ANY(ARRAY(...)).

Vnosi v tabelo: serializacija in deserializacija

Kaj pa, če moramo shraniti ne samo eno besedilno polje, ampak celoten vnos v slovarju? V tem primeru nam bo pomagala sposobnost PostgreSQL obravnavati vnos v tabeli kot eno samo vrednost:

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

Poglejmo, kaj se je tukaj dogajalo:

Smo vzeli p kot vzdevek celotnega vnosa v tabeli oseb in jih sestavil niz.
To nabor posnetkov je bil prenovljen v matriko besedilnih nizov (oseba[]::besedilo[]), da jo postavite v slovar hstore kot matriko vrednosti.
Ko prejmemo povezan zapis, mi potegnil iz slovarja po ključu kot besedilni niz.
Potrebujemo besedilo pretvori v vrednost vrste tabele oseba (za vsako tabelo se samodejno ustvari tip z istim imenom).
Vneseni zapis »razširite« v stolpce z uporabo (...).*.

json slovar

Toda takšen trik, kot smo ga uporabili zgoraj, ne bo deloval, če ni ustreznega tipa tabele, ki bi opravila »casting«. Popolnoma enaka situacija se bo pojavila, in če bomo poskušali uporabiti vrstica CTE, ne "prava" tabela.

V tem primeru nam bodo pomagali funkcije za delo z 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;

Upoštevati je treba, da pri opisu ciljne strukture ne moremo navesti vseh polj izvornega niza, ampak le tista, ki jih resnično potrebujemo. Če imamo "domačo" tabelo, potem je bolje uporabiti funkcijo json_populate_record.

Enkrat še dostopamo do slovarja, ampak stroški serializacije json-[de]so precej visoki, zato je to metodo smiselno uporabiti le v nekaterih primerih, ko se "pošten" CTE Scan pokaže slabše.

Testiranje uspešnosti

Imamo torej dva načina za serializacijo podatkov v slovar − hstore/json_object. Poleg tega je mogoče same nize ključev in vrednosti ustvariti na dva načina, z notranjo ali zunanjo pretvorbo v besedilo: array_agg(i::text) / array_agg(i)::text[].

Preverimo učinkovitost različnih vrst serializacije s povsem sintetičnim primerom - serializirajte različna števila ključev:

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

Ocenjevalni skript: serializacija

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;

V PostgreSQL 11 do približno velikosti slovarja 2^12 ključev serializacija v json traja manj časa. V tem primeru je najbolj učinkovita kombinacija json_object in “notranje” pretvorbe tipa array_agg(i::text).

Zdaj pa poskusimo 8-krat prebrati vrednost vsakega ključa - navsezadnje, če nimate dostopa do slovarja, zakaj je potem potreben?

Evalvacijski skript: branje iz slovarja

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;

In ... že približno z 2^6 ključi se branje iz slovarja json začne večkrat izgubljati branje iz hstore, za jsonb se isto zgodi pri 2^9.

Končne ugotovitve:

če morate to storiti PRIDRUŽITE SE z več ponavljajočimi se zapisi — bolje je uporabiti "slovar" tabele

če se pričakuje vaš slovar majhen in iz njega ne boste veliko prebrali - lahko uporabite json[b]

v vseh drugih primerih hstore + array_agg(i::text) bo bolj učinkovito

Vir: www.habr.com

PostgreSQL Antipatterns: udarimo po težkem JOIN s slovarjem

Veliko "dejstev" v omejenem besednjaku

hstore-dictionary

Vnosi v tabelo: serializacija in deserializacija

json slovar

Testiranje uspešnosti

Dodaj komentar Prekliči odgovor