🥇PostgreSQL Antipatterns: loosst eis de schwéiere JOIN mam Wierderbuch schloen

Mir fuere weider d'Serie vun Artikelen, déi der Studie vu wéineg bekannte Weeër gewidmet sinn fir d'Performance vun "anscheinend einfache" PostgreSQL Ufroen ze verbesseren:

Denkt net datt ech JOIN net sou gär hunn ... :)

Awer dacks ouni et ass d'Ufro wesentlech méi produktiv wéi domat. Also haut probéieren mir lass vun Ressource-intensiv JOIN - mat engem Wierderbuch.

Ugefaange mat PostgreSQL 12, kënnen e puer vun de Situatiounen hei ënnendrënner liicht anescht reproduzéiert ginn wéinst Standard Net-materialization CTE. Dëst Verhalen kann zréckgesat ginn andeems Dir de Schlëssel spezifizéiert MATERIALIZED.

Vill "Fakten" an engem limitéierten Vocabulaire

Loosst eis eng ganz real Applikatioun Aufgab huelen - mir mussen eng Lëscht weisen erakommen Messagen oder aktiv Aufgabe mat Sender:

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

An der abstrakter Welt, sollen Aufgab Autoren gläichméisseg ënnert all Mataarbechter vun eiser Organisatioun verdeelt ginn, mä an der Realitéit Aufgaben kommen, als Regel, vun enger zimlech limitéierter Zuel vu Leit - "vum Gestioun" an d'Hierarchie erop oder "vun Ënnerkontrakter" vun den Nopeschdepartementer (Analyten, Designer, Marketing, ...).

Loosst eis akzeptéieren, datt an eiser Organisatioun vun 1000 Leit nëmmen 20 Auteuren (normalerweis nach manner) Aufgabe fir all spezifeschen Interpreten an Loosst eis dëst Thema Wëssen benotzenfir déi "traditionell" Ufro ze beschleunegen.

Skript Generator

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

Loosst eis déi lescht 100 Aufgabe fir e spezifeschen Exekutor weisen:

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;

[kuckt op explain.tensor.ru]

Et stellt sech eraus 1/3 Gesamtzäit an 3/4 Liesungen Säiten vun Daten goufen nëmme gemaach fir den Auteur 100 Mol ze sichen - fir all Ausgabtask. Mä mir wëssen, datt ënnert dësen honnerte nëmmen 20 verschidde - Ass et méiglech dëst Wëssen ze benotzen?

hstore-Wörterbuch

Loosst eis profitéieren hstore Typ fir e "Wörterbuch" Schlësselwäert ze generéieren:

CREATE EXTENSION hstore

Mir brauche just den Autor seng ID a säin Numm am Wierderbuch ze setzen fir datt mir dann mat dësem Schlëssel extrahéieren kënnen:

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

[kuckt op explain.tensor.ru]

Verbréngt fir Informatiounen iwwer Persounen ze kréien 2 Mol manner Zäit a 7 Mol manner Daten liesen! Nieft dem "Vokabulär", wat eis och gehollef huet dës Resultater z'erreechen war bulk Rekord Erhuelung vum Dësch an engem eenzege Pass benotzt = ANY(ARRAY(...)).

Dësch Entréen: Serialiséierung an Deserialiséierung

Awer wat wa mir net nëmmen een Textfeld musse späicheren, mee e ganzen Entrée am Wierderbuch? An dësem Fall hëlleft dem PostgreSQL seng Fäegkeet eis behandelen en Dëschentrée als eenzege Wäert:

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

Loosst eis kucken wat hei lass war:

Mir hunn geholl p als Alias fir déi voll Persoun Dësch Entrée an huet eng ganz Rëtsch vun hinnen zesummegesat.
dëst d'Gamme vun Opzeechnunge gouf nei gestalt op eng Rei vun Textsträicher (Persoun [] :: Text []) fir et am hstore Wierderbuch als eng Rei vu Wäerter ze placéieren.
Wann mir eng Zesummenhang Rekord kréien, mir mam Schlëssel aus dem Wierderbuch gezunn als Text String.
Mir brauchen Text ginn an en Dësch Typ Wäert Persoun (fir all Dësch gëtt automatesch eng Zort mam selwechten Numm erstallt).
"Erweidert" den getippten Rekord a Kolonnen benotzt (...).*.

json Wierderbuch

Awer sou en Trick wéi mir uewen ugewannt hunn, funktionnéiert net wann et keen entspriechende Dëschtyp ass fir de "Casting" ze maachen. Genau déi selwecht Situatioun wäert entstoen, a wa mir probéieren ze benotzen engem CTE Rei, net eng "richteg" Dësch.

An dësem Fall wäerten se eis hëllefen Funktiounen fir mat json ze schaffen:

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

Et sollt bemierkt datt wann Dir d'Zilstruktur beschreiwen, mir kënnen net all Felder vun der Quellstring oplëschten, awer nëmmen déi, déi mir wierklech brauchen. Wa mir en "native" Dësch hunn, ass et besser d'Funktioun ze benotzen json_populate_record.

Mir kréien nach eemol d'Wierderbuch Zougang, mä json-[de]Serialiséierungskäschte sinn zimlech héichDofir ass et raisonnabel dës Method nëmmen an e puer Fäll ze benotzen wann den "éierleche" CTE Scan sech méi schlecht weist.

Testen Leeschtung

Also hu mir zwee Weeër fir Daten an e Wierderbuch ze serialiséieren - hstore/json_object. Zousätzlech kënnen d'Arrays vu Schlësselen a Wäerter selwer och op zwou Weeër generéiert ginn, mat interner oder externer Konversioun op Text: array_agg(i::text) / array_agg(i)::text[].

Loosst eis d'Effektivitéit vu verschiddenen Zorte vu Serialiséierung iwwerpréiwen mat engem reng syntheteschen Beispill - serialiséiert verschidden Zuelen vun Schlësselen:

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

Evaluatioun Schrëft: 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;

Op PostgreSQL 11, bis zu ongeféier eng Wierderbuchgréisst vun 2^12 Schlësselen Serialiséierung op json brauch manner Zäit. An dësem Fall ass déi effektiv d'Kombinatioun vun json_object an "intern" Typ Konversioun array_agg(i::text).

Loosst eis elo probéieren de Wäert vun all Schlëssel 8 Mol ze liesen - schliisslech, wann Dir net op d'Wörterbuch kënnt, firwat ass et dann néideg?

Evaluatiounsskript: aus engem Wierderbuch liesen

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;

An ... schonn ongeféier mat 2 ^ 6 Schlësselen, liesen aus engem json Wierderbuch fänkt e puer Mol ze verléieren aus hstore liesen, fir jsonb geschitt datselwecht bei 2^9.

Finale Conclusiounen:

wann Dir musst et maachen JOIN mat multiple widderhuelende Rekorder - et ass besser "Wörterbuch" vum Dësch ze benotzen

wann Är Wierderbuch erwaart kleng an Dir wäert net vill aus et liesen - Dir kënnt json[b] benotzen

an allen anere Fäll hstore + array_agg(i::text) wäert méi effektiv sinn

Source: will.com

PostgreSQL Antipatterns: Wierderbuch Hit Heavy JOIN

Vill "Fakten" an engem limitéierten Vocabulaire

hstore-Wörterbuch

Dësch Entréen: Serialiséierung an Deserialiséierung

json Wierderbuch

Testen Leeschtung

Setzt e Commentaire annuléieren reply