🥇PostgreSQL Antipatterns: tamaan natin ang mabigat na SUMALI sa diksyunaryo

Ipinagpapatuloy namin ang serye ng mga artikulo na nakatuon sa pag-aaral ng mga hindi kilalang paraan upang mapabuti ang pagganap ng "tila simple" na mga query sa PostgreSQL:

Huwag mong isipin na hindi ko masyadong gusto ang SUMALI... :)

Ngunit madalas kung wala ito, ang kahilingan ay lumalabas na mas produktibo kaysa dito. Kaya ngayon susubukan natin tanggalin ang resource-intensive JOIN - gamit ang diksyunaryo.

Simula sa PostgreSQL 12, ang ilan sa mga sitwasyong inilarawan sa ibaba ay maaaring medyo naiiba dahil sa default na non-materialization CTE. Maaaring ibalik ang pag-uugaling ito sa pamamagitan ng pagtukoy sa susi MATERIALIZED.

Maraming "katotohanan" sa limitadong bokabularyo

Magsagawa tayo ng isang tunay na gawain sa aplikasyon - kailangan nating magpakita ng isang listahan mga papasok na mensahe o mga aktibong gawain sa mga nagpadala:

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

Sa abstract na mundo, ang mga may-akda ng gawain ay dapat na pantay na ibinahagi sa lahat ng empleyado ng aming organisasyon, ngunit sa katotohanan ang mga gawain ay nagmumula, bilang panuntunan, mula sa isang medyo limitadong bilang ng mga tao - "mula sa pamamahala" hanggang sa hierarchy o "mula sa mga subcontractor" mula sa mga kalapit na departamento (analyst, designer, marketing, ...).

Tanggapin natin na sa ating organisasyon ng 1000 katao, 20 may-akda lamang (karaniwan ay mas kaunti pa) ang nagtakda ng mga gawain para sa bawat partikular na tagapalabas at Gamitin natin ang kaalaman sa paksang itopara mapabilis ang "tradisyonal" na query.

Generator ng 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);

Ipakita natin ang huling 100 gawain para sa isang partikular na tagapagpatupad:

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;

[tingnan sa explain.tensor.ru]

Ito ay lumiliko ang na 1/3 kabuuang oras at 3/4 na pagbabasa ang mga pahina ng data ay ginawa lamang upang hanapin ang may-akda ng 100 beses - para sa bawat gawaing output. Ngunit alam natin na kabilang sa mga daan-daang ito 20 lang ang iba - Posible bang gamitin ang kaalamang ito?

hstore-dictionary

Samantalahin natin uri ng hstore para makabuo ng "diksyonaryo" na key-value:

CREATE EXTENSION hstore

Kailangan lang naming ilagay ang ID ng may-akda at ang kanyang pangalan sa diksyunaryo upang maaari naming i-extract gamit ang key na ito:

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

[tingnan sa explain.tensor.ru]

Ginugol sa pagkuha ng impormasyon tungkol sa mga tao 2 beses na mas kaunting oras at 7 beses na mas kaunting data na nabasa! Bilang karagdagan sa "bokabularyo", ang nakatulong din sa amin na makamit ang mga resultang ito ay bulk record retrieval mula sa talahanayan sa isang solong pass gamit = ANY(ARRAY(...)).

Mga Entry sa Talahanayan: Serialization at Deserialization

Ngunit paano kung kailangan nating i-save hindi lamang isang field ng teksto, ngunit isang buong entry sa diksyunaryo? Sa kasong ito, makakatulong sa amin ang kakayahan ng PostgreSQL ituring ang isang entry sa talahanayan bilang isang solong halaga:

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

Tingnan natin kung ano ang nangyayari dito:

Kinuha namin p bilang isang alias sa buong person table entry at nagtipon ng isang hanay ng mga ito.
Ito ang hanay ng mga pag-record ay na-recast sa isang hanay ng mga string ng teksto (tao[]::text[]) upang ilagay ito sa diksyunaryo ng hstore bilang isang hanay ng mga halaga.
Kapag nakatanggap kami ng kaugnay na talaan, kami kinuha mula sa diksyunaryo sa pamamagitan ng susi bilang isang text string.
Kailangan namin ng text maging isang halaga ng uri ng talahanayan tao (para sa bawat talahanayan ng isang uri ng parehong pangalan ay awtomatikong nilikha).
"Palawakin" ang nai-type na tala sa mga column gamit (...).*.

diksyunaryo ng json

Ngunit ang gayong trick na inilapat namin sa itaas ay hindi gagana kung walang kaukulang uri ng talahanayan upang gawin ang "paghahagis". Eksakto ang parehong sitwasyon ay babangon, at kung susubukan naming gamitin isang CTE row, hindi isang "tunay" na talahanayan.

Sa kasong ito tutulungan nila tayo mga function para sa pagtatrabaho sa 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;

Dapat tandaan na kapag inilalarawan ang target na istraktura, hindi namin mailista ang lahat ng mga field ng source string, ngunit ang mga talagang kailangan namin. Kung mayroon kaming isang "katutubong" talahanayan, pagkatapos ay mas mahusay na gamitin ang function json_populate_record.

Minsan pa rin kaming nag-access sa diksyunaryo, ngunit json-[de]serialization cost ay medyo mataas, samakatuwid, makatwirang gamitin lamang ang paraang ito sa ilang mga kaso kapag ang "tapat" na CTE Scan ay nagpapakita ng sarili nitong mas malala.

Pagsubok sa pagganap

Kaya, nakakuha kami ng dalawang paraan para i-serialize ang data sa isang diksyunaryo − hstore/json_object. Bilang karagdagan, ang mga arrays ng mga key at value mismo ay maaari ding mabuo sa dalawang paraan, na may panloob o panlabas na conversion sa text: array_agg(i::text) / array_agg(i)::text[].

Suriin natin ang pagiging epektibo ng iba't ibang uri ng serialization gamit ang isang purong sintetikong halimbawa - i-serialize ang iba't ibang numero ng mga susi:

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

Iskrip ng pagsusuri: 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, hanggang sa humigit-kumulang isang sukat ng diksyunaryo na 2^12 key Ang serialization sa json ay tumatagal ng mas kaunting oras. Sa kasong ito, ang pinaka-epektibo ay ang kumbinasyon ng json_object at "internal" na uri ng conversion array_agg(i::text).

Ngayon subukan nating basahin ang halaga ng bawat key ng 8 beses - pagkatapos ng lahat, kung hindi mo ma-access ang diksyunaryo, kung gayon bakit ito kailangan?

Iskrip sa pagsusuri: pagbabasa mula sa diksyunaryo

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;

At... humigit-kumulang na na may 2^6 na key, ang pagbabasa mula sa isang json dictionary ay nagsisimulang mawalan ng maraming beses pagbabasa mula sa hstore, para sa jsonb ang parehong nangyayari sa 2^9.

Mga huling konklusyon:

kung kailangan mong gawin ito SUMALI sa maraming umuulit na mga tala — mas mainam na gumamit ng "diksyonaryo" ng talahanayan

kung inaasahan ang iyong diksyunaryo maliit at hindi ka gaanong magbabasa mula dito - maaari mong gamitin ang json[b]

sa lahat ng iba pang mga kaso hstore + array_agg(i::text) ay magiging mas epektibo

Pinagmulan: www.habr.com

PostgreSQL Antipatterns: pindutin natin ang mabigat na JOIN gamit ang isang diksyunaryo

Maraming "katotohanan" sa limitadong bokabularyo

hstore-dictionary

Mga Entry sa Talahanayan: Serialization at Deserialization

diksyunaryo ng json

Pagsubok sa pagganap

Magdagdag ng komento Kanselahin ang sumagot