🥇PostgreSQL Antipatterns: ngatirovei inorema JOIN neduramazwi

Isu tinoenderera mberi nenhevedzano yezvinyorwa zvakapihwa mukudzidza kwezvishoma-zvinozivikanwa nzira dzekuvandudza mashandiro e "zvinoita sezviri nyore" PostgreSQL mibvunzo:

Usafunge kuti ini handifarire JOIN zvakanyanya ... :)

Asi kazhinji pasina iyo, chikumbiro chinoshanduka kuva chinoshanda zvakanyanya kupfuura nacho. Saka nhasi tichaedza bvisa resource-intensive JOIN - kushandisa duramazwi.

Kutanga nePostgreSQL 12, mamwe emamiriro anotsanangurwa pazasi anogona kuburitswa zvishoma zvakasiyana nekuda kwe default non-materialization CTE. Maitiro aya anogona kudzoserwa nekudoma kiyi MATERIALIZED.

Zvakawanda zve "chokwadi" mumashoko mashoma

Ngatitorei basa chairo rekushandisa - tinoda kuratidza runyorwa mameseji anouya kana mabasa anoshanda nevatumiri:

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

Munyika isinganzwisisike, vanyori vebasa vanofanirwa kugoverwa zvakaenzana pakati pevashandi vese vesangano redu, asi muchokwadi mabasa anouya, sekutonga, kubva kune vanhu vashoma vashoma - "kubva kumaneja" kumusoro kwehierarchy kana "kubva kune subcontractors" kubva kumadhipatimendi akavakidzana (vaongorori, vagadziri, kushambadzira, ...).

Ngatibvumei kuti musangano redu revanhu 1000, vanyori makumi maviri chete (kazhinji kunyange vashoma) vanoisa mabasa kune mumwe nemumwe mutambi uye. Ngatishandisei ruzivo rwechidzidzo ichikukurumidza kubvunza "chinyakare".

Script jenareta

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

Ngatiratidzei mabasa zana ekupedzisira emuitisi chaiwo:

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;

[tarisa ku explain.tensor.ru]

Icho chinopera icho 1/3 nguva yakazara uye 3/4 kuverenga mapeji e data akaitwa chete kutsvaga munyori ka100 - kune rimwe nerimwe basa rekubuda. Asi tinoziva kuti pakati pemazana aya 20 chete akasiyana - Zvinoita here kushandisa ruzivo urwu?

hstore-dictionary

Ngatitore mukana hstore type kugadzira "duramazwi" kiyi-kukosha:

CREATE EXTENSION hstore

Isu tinongoda kuisa ID yemunyori uye zita rake muduramazwi kuti isu tigozotora tichishandisa kiyi iyi:

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

[tarisa ku explain.tensor.ru]

Kushandiswa pakutsvaga ruzivo rwevanhu 2 nguva shoma nguva uye 7 nguva shoma data kuverenga! Pamusoro pe "vocabulary", chii chakatibatsirawo kuwana mhedzisiro iyi yaive kudzoreredza rekodhi yakawanda kubva patafura mune imwe pass uchishandisa = ANY(ARRAY(...)).

Tafura Entries: Serialization uye Deserialization

Asi ko kana tichida kuchengetedza kwete chikamu chimwe chete chemavara, asi chinyorwa chose muduramazwi? Muchiitiko ichi, kugona kwePostgreSQL kuchatibatsira tora chinyorwa chetafura sechinhu chimwe chete:

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

Ngatitarisei zvaiitika apa:

Takatora p sechirevo kune yakazara munhu tafura yekupinda akaunganidza marudzi mazhinji azvo.
ichi ndandanda yezvakarekodhwa yakakandwazve kumutsara wetambo dzemavara (munhu[]::zvinyorwa[]) kuti zviise muduramazwi rehstore senhevedzano yeukoshi.
Patinogamuchira rekodhi inoenderana, isu kudhonzwa kubva muduramazwi nekiyi sechinyorwa tambo.
Tinoda zvinyorwa shandura kuve mutengo wemhando yetafura munhu (patafura yega yega mhando yezita rimwechete inogadzirwa otomatiki).
"Wedzera" rekodhi yataipa kuita makoramu uchishandisa (...).*.

json duramazwi

Asi hunyengeri hwakadaro sezvatakashandisa pamusoro haushande kana pasina inoenderana tafura mhando yekuita "kukanda". Chaizvo mamiriro akafanana achamuka, uye kana tikaedza kushandisa CTE mutsara, kwete "chaiyo" tafura.

Pakadai vachatibatsira mabasa ekushanda nejson:

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

Zvinofanira kucherechedzwa kuti kana tichitsanangura chimiro chakatarwa, hatigone kunyora ese minda yesosi tambo, asi iyo chete yatinoda chaizvo. Kana tine tafura "yekuzvarwa", saka zviri nani kushandisa basa racho json_populate_record.

Tichiri kuwana duramazwi kamwe chete, asi json-[de]serialization mitengo yakakwira zvakanyanya, saka, zvinonzwisisika kushandisa nzira iyi chete mune dzimwe nguva apo "kutendeseka" CTE Scan inozviratidza kuipa.

Kuedza kuita

Saka, isu tine nzira mbiri dzekuisa data muduramazwi - hstore/json_object. Pamusoro pezvo, mitsara yemakiyi uye makoshero pachawo anogona kugadzirwa nenzira mbiri, nekushandurwa kwemukati kana kwekunze kuita zvinyorwa: array_agg(i::zvinyorwa) / array_agg(i)::zvinyorwa[].

Ngatitarisei kushanda kwemhando dzakasiyana dzeserialization tichishandisa muenzaniso chaiwo wekugadzira - kurongedza nhamba dzakasiyana dzekiyi:

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

Evaluation script: 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;

PaPostgreSQL 11, inosvika pachiyero cheduramazwi che2^12 makiyi serialization kujson inotora nguva shoma. Muchiitiko ichi, inonyanya kushanda ndeyekusanganiswa kwejson_object uye "mukati" rudzi rwekushandura array_agg(i::text).

Zvino ngatiedzei kuverenga kukosha kwekiyi imwe neimwe ka8 - mushure mezvose, kana iwe usingakwanise kuwana duramazwi, saka nei ichidikanwa?

Evaluation script: kuverenga kubva muduramazwi

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;

Uye ... nechekare nemakiyi 2^6, kuverenga kubva muduramazwi rejson kunotanga kurasikirwa kakawanda kuverenga kubva kuhstore, nekuti jsonb zvakafanana zvinoitika pa2^9.

Mhedziso dzekupedzisira:

kana uchifanira kuzviita JOIN neakawanda anodzokorora marekodhi - zviri nani kushandisa "duramazwi" retafura

kana duramazwi rako richitarisirwa diki uye haungaverenge zvakawanda kubva pazviri - unogona kushandisa json[b]

mune dzimwe nguva dzese hstore + array_agg(i::zvinyorwa) zvichava zvinobudirira

Source: www.habr.com

PostgreSQL Antipatterns: Duramazwi Hit Heavy JOIN

Zvakawanda zve "chokwadi" mumashoko mashoma

hstore-dictionary

Tafura Entries: Serialization uye Deserialization

json duramazwi

Kuedza kuita

Voeg kukanzura mhinduro