PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Inkomplu s-serje ta’ artikoli ddedikati għall-istudju ta’ modi ftit li xejn magħrufa biex titjieb il-prestazzjoni ta’ mistoqsijiet PostgreSQL “li jidhru sempliċi”:

• rekord rari se jilħaq nofs ta 'JOIN
• Sisyphean JOIN arrays
• ħsara JOIN u JEW
• CTE JOIN CTE

Taħsibx li ma tantx inħobb JOIN... :)

Iżda ħafna drabi mingħajrha, it-talba tirriżulta li hija ferm aktar produttiva milli magħha. Allura llum nippruvaw jeħles minn JOIN li juża ħafna riżorsi - bl-użu ta' dizzjunarju.

Nibdew minn PostgreSQL 12, xi wħud mis-sitwazzjonijiet deskritti hawn taħt jistgħu jiġu riprodotti b'mod kemmxejn differenti minħabba default non-materjalizzazzjoni CTE. Din l-imġieba tista' titreġġa' lura billi tispeċifika ċ-ċavetta MATERIALIZED.

Ħafna "fatti" f'vokabularju limitat

Ejja nieħdu kompitu ta 'applikazzjoni reali ħafna - għandna bżonn nuru lista messaġġi deħlin jew kompiti attivi mal-mittenti:

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

Fid-dinja astratta, l-awturi tal-kompiti għandhom jitqassmu b'mod ugwali fost l-impjegati kollha tal-organizzazzjoni tagħna, iżda fir-realtà kompiti jiġu, bħala regola, minn numru pjuttost limitat ta 'nies - "mill-ġestjoni" 'l fuq fil-ġerarkija jew "minn sottokuntratturi" minn dipartimenti ġirien (analisti, disinjaturi, marketing, ...).

Ejja naċċettaw li fl-organizzazzjoni tagħna ta' 1000 ruħ, 20 awtur biss (ġeneralment saħansitra inqas) jistabbilixxu kompiti għal kull artist speċifiku u Ejja nużaw dan l-għarfien tas-suġġettbiex tħaffef il-mistoqsija "tradizzjonali".

Ġeneratur tal-iskript

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

Ejja nuru l-aħħar 100 biċċa xogħol għal eżekutur speċifiku:

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;

[ħares lejn explic.tensor.ru]

Jirriżulta li 1/3 ħin totali u 3/4 qari paġni ta 'data saru biss biex tfittex l-awtur 100 darba - għal kull kompitu output. Imma nafu li fost dawn il-mijiet 20 biss differenti - Huwa possibbli li tuża dan l-għarfien?

hstore-dizzjunarju

Ejja nieħdu vantaġġ tip ta' hstore biex tiġġenera valur-ċavetta "dizzjunarju":

CREATE EXTENSION hstore

Għandna bżonn biss li npoġġu l-ID tal-awtur u ismu fid-dizzjunarju biex imbagħad inkunu nistgħu niġbdu bl-użu ta’ din iċ-ċavetta:

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

[ħares lejn explic.tensor.ru]

Intefaq biex tinkiseb informazzjoni dwar persuni 2 darbiet inqas ħin u 7 darbiet inqas data qari! Minbarra “vokabularju”, dak li għen ukoll biex niksbu dawn ir-riżultati kien irkupru ta' rekords bl-ingrossa mit-tabella f'pass wieħed bl-użu = ANY(ARRAY(...)).

Entrati fil-Tabella: Serialization u Deserialization

Imma x'jiġri jekk ikollna bżonn insalvaw mhux biss qasam tat-test wieħed, iżda entrata sħiħa fid-dizzjunarju? F'dan il-każ, il-ħila ta 'PostgreSQL se tgħinna ittratta entrata ta' tabella bħala valur wieħed:

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

Ejja nħarsu lejn dak li kien għaddej hawn:

1. Ħadna p bħala alias għall-entrata tat-tabella tal-persuna sħiħa u immuntat firxa minnhom.
2. Dan il-firxa ta' reġistrazzjonijiet ġiet riformulata għal firxa ta’ kordi ta’ test (person[]::test[]) biex tpoġġiha fid-dizzjunarju hstore bħala firxa ta’ valuri.
3. Meta nirċievu rekord relatat, aħna miġbud mid-dizzjunarju biċ-ċavetta bħala string tat-test.
4. Għandna bżonn test tinbidel f'valur tat-tip ta' tabella persuna (għal kull tabella jinħoloq awtomatikament tip tal-istess isem).
5. "Espandu" ir-rekord ittajpjat f'kolonni bl-użu (...).*.

dizzjunarju json

Iżda tali trick kif applikajna hawn fuq ma jaħdimx jekk ma jkunx hemm tip ta 'tabella korrispondenti biex tagħmel il-"casting". Eżattament l-istess sitwazzjoni se tqum, u jekk nippruvaw nużaw ringiela CTE, mhux tabella "reali"..

F'dan il-każ se jgħinuna funzjonijiet biex taħdem ma '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;

Għandu jiġi nnutat li meta niddeskrivu l-istruttura fil-mira, ma nistgħux nilnutaw l-oqsma kollha tas-sekwenza tas-sors, iżda dawk biss li għandna bżonn verament. Jekk ikollna tabella "nattiva", allura huwa aħjar li tuża l-funzjoni json_populate_record.

Għadna aċċess għad-dizzjunarju darba, iżda json-[de]serialization spejjeż huma pjuttost għolja, għalhekk, huwa raġonevoli li tuża dan il-metodu biss f'xi każijiet meta l-"onest" CTE Scan juri lilu nnifsu agħar.

Prestazzjoni tal-ittestjar

Allura, aħna ltqajna żewġ modi biex nisserjelizzaw id-dejta f'dizzjunarju - hstore/json_object. Barra minn hekk, l-arrays ta 'ċwievet u valuri nfushom jistgħu wkoll jiġu ġġenerati b'żewġ modi, b'konverżjoni interna jew esterna għal test: array_agg(i::test) / array_agg(i)::test[].

Ejja niċċekkjaw l-effettività ta 'tipi differenti ta' serialization billi tuża eżempju purament sintetiku - serialize numri differenti ta 'ċwievet:

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

Skript ta' valutazzjoni: 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;

Fuq PostgreSQL 11, sa madwar daqs ta' dizzjunarju ta' 2^12-il ċavetta serialization għal json tieħu inqas ħin. F'dan il-każ, l-aktar effettiva hija l-kombinazzjoni ta 'json_object u konverżjoni tat-tip "intern". array_agg(i::text).

Issa ejja nippruvaw naqraw il-valur ta 'kull ċavetta 8 darbiet - wara kollox, jekk ma taċċessax id-dizzjunarju, allura għaliex hija meħtieġa?

Skript ta' evalwazzjoni: qari minn dizzjunarju

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;

U... diġà bejn wieħed u ieħor bi 2^6 ċwievet, il-qari minn dizzjunarju json jibda jitlef diversi drabi qari minn hstore, għal jsonb l-istess jiġri f'2^9.

Konklużjonijiet finali:

• jekk għandek bżonn tagħmel dan JINGĦADD b'reġistri ripetuti multipli — huwa aħjar li tuża "dizzjunarju" tat-tabella
• jekk id-dizzjunarju tiegħek hu mistenni żgħir u mhux se taqra ħafna minnu - tista' tuża json[b]
• fil-każijiet l-oħra kollha hstore + array_agg(i::test) se jkunu aktar effettivi

Sors: www.habr.com