PostgreSQL Antipatterns: üssük le a nehéz JOIN-t egy szótárral

Folytatjuk a „egyszerűnek tűnő” PostgreSQL-lekérdezések teljesítményének javításának kevéssé ismert módjait tanulmányozó cikksorozatot:

• ritka rekord eléri a JOIN közepét
• Sziszifuszi JOIN tömbök
• káros JOIN és VAGY
• CTE CSATLAKOZZ A CTE-hez

Ne gondold, hogy annyira nem szeretem a JOIN-t... :)

De gyakran enélkül a kérés lényegesen produktívabbnak bizonyul, mint vele. Szóval ma megpróbáljuk megszabadulni az erőforrás-igényes JOIN-tól - szótár használata.

A PostgreSQL 12-től kezdve az alább leírt helyzetek némelyike ​​kissé eltérő módon reprodukálható, mivel alapértelmezett non-materialization CTE. Ez a viselkedés a kulcs megadásával visszaállítható MATERIALIZED.

Sok „tény” korlátozott szókincsben

Vegyünk egy nagyon valós alkalmazási feladatot – meg kell jelenítenünk egy listát bejövő üzenetek vagy aktív feladatok feladókkal:

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

Az absztrakt világban a feladatok szerzőit egyenletesen kell elosztani szervezetünk összes alkalmazottja között, de a valóságban a feladatok általában meglehetősen korlátozott számú embertől származnak - „vezetéstől” feljebb a hierarchiában vagy „alvállalkozóktól” a szomszédos részlegektől (elemzők, tervezők, marketing stb.).

Fogadjuk el, hogy 1000 fős szervezetünkben csak 20 szerző (általában még kevesebb) jelöl ki feladatokat minden egyes előadónak, ill. Használjuk ezt a tantárgyi tudásthogy felgyorsítsa a "hagyományos" lekérdezést.

Script generátor

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

Mutassuk meg egy adott végrehajtó utolsó 100 feladatát:

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;

[megtekintés itt: magyarázat.tensor.ru]

Kiderül, hogy 1/3 teljes idő és 3/4 leolvasás oldalnyi adat csak a szerző keresésére készült 100 alkalommal - minden kimeneti feladathoz. De tudjuk, hogy ezek közül a százak közül csak 20 különböző - Felhasználható-e ez a tudás?

hstore-szótár

Használjuk ki hstore típusú „szótári” kulcsérték létrehozásához:

CREATE EXTENSION hstore

Csak be kell helyeznünk a szerző azonosítóját és nevét a szótárba, hogy ezt követően ki tudjuk bontani ezt a kulcsot:

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

[megtekintés itt: magyarázat.tensor.ru]

Személyekkel kapcsolatos információk megszerzésére fordítottuk 2-szer kevesebb idő és 7-szer kevesebb adatolvasás! A „szókincs” mellett az is segített, hogy elérjük ezeket az eredményeket tömeges rekord visszakeresés az asztalról egyetlen lépésben segítségével = ANY(ARRAY(...)).

Táblázatbejegyzések: szerializálás és deszerializálás

De mi van akkor, ha nem csak egy szövegmezőt kell mentenünk, hanem egy teljes bejegyzést a szótárban? Ebben az esetben a PostgreSQL képessége segít nekünk táblázatbejegyzést egyetlen értékként kezelni:

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

Nézzük, mi volt itt:

1. Vettünk p a teljes személy tábla bejegyzés álneveként és összeállított belőlük egy tömböt.
2. ezt a felvételek sorát átdolgozták szöveges karakterláncok tömbjére (person[]::text[]), hogy értéktömbként elhelyezze a hstore szótárban.
3. Amikor kapunk egy kapcsolódó rekordot, mi kulcsra húzva a szótárból szöveges karakterláncként.
4. Szövegre van szükségünk táblázat típusú értékké alakul személy (minden táblához automatikusan létrejön egy azonos nevű típus).
5. „Bővítse ki” a beírt rekordot oszlopokba a segítségével (...).*.

json szótár

De a fentebb alkalmazott trükk nem fog működni, ha nincs megfelelő táblázattípus a „casting” elvégzéséhez. Pontosan ugyanaz a helyzet fog előállni, és ha megpróbáljuk használni egy CTE sor, nem egy "igazi" táblázat.

Ebben az esetben ők segítenek nekünk funkciók a json használatához:

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

Megjegyzendő, hogy a célstruktúra leírásánál nem tudjuk felsorolni a forráskarakterlánc összes mezőjét, hanem csak azokat, amelyekre valóban szükségünk van. Ha van „natív” táblánk, akkor érdemesebb a függvényt használni json_populate_record.

Egyszer még mindig hozzáférünk a szótárhoz, de A json-[de]szerializációs költségek meglehetősen magasak, ezért ezt a módszert csak bizonyos esetekben célszerű alkalmazni, amikor a „becsületes” CTE Scan rosszabbul mutatja magát.

A teljesítmény tesztelése

Tehát két módszerünk van az adatok szótárba történő sorosítására − hstore/json_object. Ezenkívül maguk a kulcsok és értékek tömbjei kétféle módon is előállíthatók, belső vagy külső szöveggé konvertálással: array_agg(i::text) / array_agg(i)::text[].

Vizsgáljuk meg a különböző típusú szerializálás hatékonyságát egy tisztán szintetikus példával - különböző számú kulcsot sorba rendezni:

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

Értékelő szkript: sorozatosítás

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 rendszeren körülbelül 2^12 kulcsos szótárméretig a json-ra történő szerializálás kevesebb időt vesz igénybe. Ebben az esetben a leghatékonyabb a json_object és a „belső” típusú konverzió kombinációja array_agg(i::text).

Most próbáljuk meg 8-szor elolvasni az egyes kulcsok értékét – elvégre ha nem fér hozzá a szótárhoz, akkor miért van rá szükség?

Értékelő szkript: olvasás szótárból

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;

És... már kb 2^6 billentyűvel a json szótárból való olvasás többszörösen veszít hstore-ból olvas, a jsonb esetében ugyanez történik 2^9-nél.

Végső következtetések:

• ha meg kell tennie CSATLAKOZZ több ismétlődő rekordhoz - jobb a táblázat „szótárát” használni
• ha szótárát várják kicsi és nem fogsz sokat olvasni belőle - használhatod a json[b] fájlt
• minden más esetben hstore + array_agg(i::text) hatékonyabb lesz

Forrás: will.com