🥇Mga Antipattern sa PostgreSQL: atong i-hit ang bug-at nga JOIN sa diksyonaryo

Gipadayon namon ang serye sa mga artikulo nga gipahinungod sa pagtuon sa wala kaayo nahibal-an nga mga paagi aron mapaayo ang paghimo sa "daw yano" nga mga pangutana sa PostgreSQL:

Ayaw hunahunaa nga dili ko ganahan ug JOIN... :)

Apan kasagaran kung wala kini, ang hangyo nahimo nga labi ka mabungahon kaysa uban niini. Busa karon atong sulayan kuhaa ang resource-intensive JOIN - gamit ang diksyonaryo.

Sugod sa PostgreSQL 12, ang pipila sa mga sitwasyon nga gihubit sa ubos mahimong makopya og gamay nga lahi tungod sa default nga non-materialization CTE. Kini nga kinaiya mahimong ibalik pinaagi sa pagtino sa yawe MATERIALIZED.

Daghang "mga kamatuoran" sa limitado nga bokabularyo

Atong buhaton ang usa ka tinuud nga buluhaton sa aplikasyon - kinahanglan naton magpakita usa ka lista umaabot nga mga mensahe o aktibong mga buluhaton uban sa mga nagpadala:

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

Sa abstract nga kalibutan, ang mga tagsulat sa buluhaton kinahanglan nga parehas nga ipanghatag sa tanan nga mga empleyado sa among organisasyon, apan sa tinuud Ang mga buluhaton moabut, ingon nga usa ka lagda, gikan sa usa ka limitado nga gidaghanon sa mga tawo - "gikan sa pagdumala" ngadto sa hierarchy o "gikan sa mga subcontractor" gikan sa silingang mga departamento (mga analista, tigdesinyo, marketing, ...).

Atong dawaton nga sa atong organisasyon sa 1000 ka tawo, 20 lang ka awtor (kasagarang mas gamay pa) ang nagtakda ug mga buluhaton alang sa matag espesipikong tigpasundayag ug Atong gamiton kini nga kahibalo sa hilisgutanaron mapadali ang "tradisyonal" nga pangutana.

Generator sa script

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

Atong ipakita ang katapusang 100 ka mga buluhaton alang sa usa ka piho nga tigpatuman:

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;

[tan-awa sa explain.tensor.ru]

Nahitabo kana 1/3 nga total nga oras ug 3/4 nga pagbasa Ang mga panid sa datos gihimo lamang aron pangitaon ang tagsulat 100 ka beses - alang sa matag output nga buluhaton. Apan nahibal-an nato nga taliwala niining gatusan 20 ra ang lainlain - Posible ba nga gamiton kini nga kahibalo?

hstore-diksiyonaryo

Atong pahimuslan tipo sa hstore aron makamugna og "diksyonaryo" nga key-value:

CREATE EXTENSION hstore

Kinahanglan lang nato ibutang ang ID sa tagsulat ug ang iyang ngalan sa diksyonaryo aron mahimo namong makuha gamit kini nga yawe:

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

[tan-awa sa explain.tensor.ru]

Gigasto sa pagkuha og impormasyon bahin sa mga tawo 2 ka beses nga gamay nga oras ug 7 ka beses nga gamay nga pagbasa sa datos! Dugang sa "bokabularyo", ang nakatabang usab kanamo nga makab-ot kini nga mga resulta mao ang bulk nga pagbawi sa rekord gikan sa lamesa sa usa ka pass gamit = ANY(ARRAY(...)).

Mga Entries sa Talaan: Serialization ug Deserialization

Apan unsa man kon kinahanglan natong tipigan dili lang ang usa ka text field, kondili usa ka tibuok nga entry sa diksyonaryo? Niini nga kaso, ang abilidad sa PostgreSQL makatabang kanato tagda ang usa ka entry sa lamesa isip usa ka bili:

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

Atong tan-awon kung unsa ang nahitabo dinhi:

Among gikuha p isip alyas sa full person table entry ug nagtigom ug usa ka laray kanila.
kini ang han-ay sa mga rekording gi-recast ngadto sa usa ka han-ay sa text strings (tawo[]::text[]) aron ibutang kini sa hstore nga diksyonaryo isip han-ay sa mga bili.
Sa diha nga kita makadawat og usa ka may kalabutan nga rekord, kita gikuha gikan sa diksyonaryo pinaagi sa yawe isip usa ka text string.
Nagkinahanglan mi og text mahimong usa ka klase nga kantidad sa lamesa tawo (alang sa matag lamesa usa ka tipo sa parehas nga ngalan ang awtomatiko nga gihimo).
"Palapad" ang gi-type nga rekord sa mga kolum gamit (...).*.

json nga diksyonaryo

Apan ang ingon nga usa ka limbong nga among gipadapat sa ibabaw dili molihok kung wala’y katugbang nga tipo sa lamesa nga buhaton ang "paghulma". Eksakto nga parehas nga sitwasyon ang motungha, ug kung atong sulayan nga gamiton usa ka laray sa CTE, dili usa ka "tinuod" nga lamesa.

Sa kini nga kaso sila makatabang kanato mga gimbuhaton alang sa pagtrabaho kauban ang 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;

Kinahanglan nga hinumdoman nga kung gihubit ang target nga istruktura, dili naton malista ang tanan nga mga natad sa gigikanan nga hilo, apan ang mga kinahanglan gyud naton. Kung kita adunay usa ka "lumad" nga lamesa, nan mas maayo nga gamiton ang function json_populate_record.

Maka-access gihapon mi sa diksyonaryo kausa, pero Ang json-[de]serialization nga gasto medyo taas, busa, makatarunganon nga gamiton kini nga pamaagi lamang sa pipila ka mga kaso kung ang "matinud-anon" nga CTE Scan nagpakita sa iyang kaugalingon nga mas grabe.

Pagsulay sa performance

Mao nga, nakakuha kami duha ka paagi aron ma-serialize ang datos sa usa ka diksyonaryo - hstore/json_object. Dugang pa, ang mga han-ay sa mga yawe ug mga kantidad sa ilang kaugalingon mahimo usab nga mabuhat sa duha ka paagi, nga adunay internal o eksternal nga pagkakabig sa teksto: array_agg(i::text) / array_agg(i)::text[].

Atong susihon ang pagka-epektibo sa lainlaing mga lahi sa serialization gamit ang usa ka sintetikong pananglitan - serialize lain-laing mga numero sa mga yawe:

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;

Sa PostgreSQL 11, hangtod sa gibanabana nga gidak-on sa diksyonaryo nga 2^12 nga mga yawe Ang serialization sa json nagkinahanglan og gamay nga panahon. Niini nga kaso, ang labing epektibo mao ang kombinasyon sa json_object ug "internal" type nga pagkakabig array_agg(i::text).

Karon atong sulayan nga basahon ang bili sa matag yawe 8 ka beses - human sa tanan, kung dili nimo ma-access ang diksyonaryo, nan nganong gikinahanglan kini?

Evaluation script: pagbasa gikan sa diksyonaryo

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;

Ug... hapit na nga adunay 2 ^ 6 nga mga yawe, ang pagbasa gikan sa usa ka diksyonaryo sa json nagsugod sa pagkawala sa daghang mga higayon pagbasa gikan sa hstore, kay jsonb ang sama nga mahitabo sa 2^9.

Katapusan nga mga konklusyon:

kung kinahanglan nimo buhaton AMBAY sa daghang nagbalikbalik nga mga rekord — mas maayo nga gamiton ang "diksyonaryo" sa lamesa

kung ang imong diksyonaryo gipaabut gamay ug dili ka makabasa ug daghan gikan niini - mahimo nimong gamiton ang json[b]

sa tanang ubang mga kaso hstore + array_agg(i::text) mahimong mas epektibo

Source: www.habr.com

Mga Antipattern sa PostgreSQL: atong maigo ang bug-at nga JOIN sa usa ka diksyonaryo

Daghang "mga kamatuoran" sa limitado nga bokabularyo

hstore-diksiyonaryo

Mga Entries sa Talaan: Serialization ug Deserialization

json nga diksyonaryo

Pagsulay sa performance

Idugang sa usa ka comment Отменить ответ