🥇Antipatterns PostgreSQL: биёед бо луғат ба ҚИРМИШИ вазнин зарба занем

Мо силсилаи мақолаҳоро идома медиҳем, ки ба омӯзиши роҳҳои каммаълум барои беҳтар кардани иҷрои дархостҳои "ба назар оддӣ" PostgreSQL бахшида шудаанд:

Фикр накунед, ки ман ин қадар ҲАМРОХ шуданро дӯст намедорам... :)

Аммо аксар вақт бидуни он, дархост назар ба он хеле самараноктар мешавад. Пас, имрӯз мо кӯшиш мекунем аз ресурсхои серталаб халос шаванд - бо истифода аз луғат.

Аз PostgreSQL 12 сар карда, баъзе ҳолатҳое, ки дар зер тавсиф шудаанд, метавонанд бо сабаби он каме фарқ кунанд. пешфарз ғайридавлатӣ моддӣ CTE. Ин рафторро тавассути муайян кардани калид баргардонидан мумкин аст MATERIALIZED.

Бисёр "фактҳо" дар луғати маҳдуд

Биёед як вазифаи воқеии барномаро гирем - мо бояд рӯйхатро нишон диҳем паёмҳои воридотӣ ё вазифаҳои фаъол бо ирсолкунандагон:

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

Дар ҷаҳони абстрактӣ, муаллифони вазифаҳо бояд дар байни ҳамаи кормандони ташкилоти мо баробар тақсим карда шаванд, аммо дар асл вазифахо, чун коида, аз шумораи хеле махдуди одамон меоянд - "аз менеҷмент" боло дар зинанизом ё "аз зерпудратчиён" аз шӯъбаҳои ҳамсоя (таҳлилгарон, тарроҳон, маркетинг, ...).

Биёед қабул кунем, ки дар ташкилоти 1000 нафари мо танҳо 20 муаллиф (одатан аз ин ҳам камтар) барои ҳар як иҷрокунандаи мушаххас вазифа гузоштаанд ва Биёед аз ин дониши фанн истифода кунемки суръ-ати «анъанавии» тезонда шавад.

Генератори скрипт

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

Биёед 100 вазифаи охиринро барои иҷрокунандаи мушаххас нишон диҳем:

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;

[нигаред дар explain.tensor.ru]

Ин рӯй медиҳад 1/3 вақти умумӣ ва 3/4 хониш Саҳифаҳои маълумот танҳо барои ҷустуҷӯи муаллиф 100 маротиба - барои ҳар як вазифаи баромад сохта шудаанд. Вале мо медонем, ки дар байни ин садхо танҳо 20 гуногун — Оё ин донишро истифода бурдан мумкин аст?

hstore-лугат

Биёед бартарӣ гирем навъи hstore барои тавлиди калид-арзиши "лугат":

CREATE EXTENSION hstore

Мо танҳо бояд шахсияти муаллиф ва номи ӯро дар луғат гузорем, то мо бо истифода аз ин калид истихроҷ кунем:

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

[нигаред дар explain.tensor.ru]

Барои гирифтани маълумот дар бораи шахсон сарф мешавад 2 маротиба камтар вақт ва 7 маротиба камтар хондани маълумот! Илова ба «захираи луғат», он чизе, ки ба мо барои ба даст овардани ин натиҷаҳо кӯмак кард дарёфти сабти оммавӣ аз ҷадвал дар як ағбаи истифода = ANY(ARRAY(...)).

Вурудҳои ҷадвал: Сериализатсия ва Десериализатсия

Аммо чӣ мешавад, агар мо на танҳо як майдони матн, балки тамоми вурудро дар луғат захира кунем? Дар ин ҳолат, қобилияти PostgreSQL ба мо кӯмак мекунад вуруди ҷадвалро ҳамчун арзиши ягона баррасӣ кунед:

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

Биёед бубинем, ки дар ин ҷо чӣ рӯй дод:

гирифтем p ҳамчун тахаллус ба вуруди ҷадвали пурраи шахс ва як қатор онҳоро ҷамъ овард.
ин силсилаи сабтҳо аз нав сабт карда шуданд ба массиви сатрҳои матнӣ (person[]::text[]) барои ҷойгир кардани он дар луғати hstore ҳамчун массиви арзишҳо.
Вақте ки мо сабти дахлдор мегирем, мо аз луғат бо калид кашида шуд ҳамчун сатри матн.
Ба мо матн лозим аст ба арзиши навъи ҷадвал табдил диҳед шахс (барои ҳар як ҷадвал навъи ҳамон ном ба таври худкор сохта мешавад).
Сабти чопшударо ба сутунҳо "васеъ кунед" (...).*.

луғати json

Аммо чунин ҳилае, ки мо дар боло татбиқ кардем, кор намекунад, агар ягон намуди ҷадвал барои анҷом додани "кастинг" мавҷуд набошад. Махз хамин вазъият ба амал меояд ва агар мо кушиш кунем, ки истифода барем як сатри CTE, на ҷадвали "воқеӣ".

Дар ин ҳолат онҳо ба мо кӯмак мекунанд функсияҳо барои кор бо 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;

Бояд қайд кард, ки ҳангоми тавсифи сохтори мақсаднок, мо наметавонем ҳамаи майдонҳои сатри сарчашмаро номбар кунем, балки танҳо онҳоеро, ки ба мо воқеан лозиманд, номбар кунем. Агар мо ҷадвали "модарӣ" дошта бошем, он гоҳ беҳтар аст, ки функсияро истифода барем json_populate_record.

Мо то ҳол ба луғат як бор дастрасӣ пайдо мекунем, аммо хароҷоти json-[de]сериализатсия хеле баланд аст, аз ин рӯ, истифодаи ин усул танҳо дар баъзе мавридҳо оқилона аст, вақте ки Скании "боинсоф" CTE худро бадтар нишон медиҳад.

Санҷиши иҷроиш

Ҳамин тавр, мо ду роҳи силсилаи маълумотро ба луғат гирифтем - hstore/json_object. Илова бар ин, массивҳои калидҳо ва арзишҳо метавонанд бо ду роҳ бо табдили дохилӣ ё берунӣ ба матн тавлид карда шаванд: array_agg(i::text) / array_agg(i)::text[].

Биёед самаранокии намудҳои гуногуни сериализатсияро бо истифода аз мисоли сирф синтетикӣ тафтиш кунем - рақамҳои гуногуни калидҳоро силсилавӣ кунед:

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

Скрипти арзёбӣ: силсиласозӣ

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;

Дар PostgreSQL 11, то тақрибан андозаи луғат аз 2^12 калид сериализатсия ба json вақти камтар мегирад. Дар ин ҳолат, аз ҳама самаранок омезиши json_object ва табдилдиҳии навъи "дохилӣ" мебошад array_agg(i::text).

Акнун биёед кӯшиш кунем, ки арзиши ҳар як калидро 8 маротиба хонем - охир, агар шумо ба луғат дастрасӣ надоред, пас чаро он лозим аст?

Скрипти арзёбӣ: хондан аз луғат

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;

Ва... аллакай тахминан бо калидҳои 2^6, хондан аз луғати json якчанд маротиба гум мешавад хондан аз hstore, барои jsonb ҳамин тавр рӯй медиҳад 2^9.

Хулосаҳои ниҳоӣ:

агар ин корро кардан лозим бошад Бо сабтҳои такрории сершумор ҲАМРОХ шавед — «лугат»-и чадвалро истифода бурдан бехтар аст

агар луғати шумо интизор бошад хурд аст ва шумо аз он чизи зиёде намехонед - шумо метавонед json [b] -ро истифода баред

дар ҳама ҳолатҳои дигар hstore + array_agg (i::text) самарабахштар мешавад

Манбаъ: will.com

PostgreSQL Antipatterns: биёед бо луғат JOIN-и вазнинро пахш кунем

Бисёр "фактҳо" дар луғати маҳдуд

hstore-лугат

Вурудҳои ҷадвал: Сериализатсия ва Десериализатсия

луғати json

Санҷиши иҷроиш

Илова Эзоҳ Cancel ҷавоб