🥇 Antipatterns PostgreSQL: buail sinn an trom Thig còmhla ris an fhaclair

Tha sinn a’ leantainn air adhart leis an t-sreath de artaigilean a tha coisrigte do bhith a’ sgrùdadh dhòighean air nach eil mòran eòlach air coileanadh cheistean PostgreSQL “a tha coltach gu sìmplidh” a leasachadh:

Na smaoinich nach toil leam JOIN cho mòr... :)

Ach gu tric às aonais, tha an t-iarrtas a 'tionndadh a-mach gu bhith gu math nas cinneasaiche na leis. Mar sin an-diugh feuchaidh sinn faigh cuidhteas JOIN dian-ghoireasan - a' cleachdadh faclair.

A’ tòiseachadh le PostgreSQL 12, dh’ fhaodadh cuid de na suidheachaidhean a tha air am mìneachadh gu h-ìosal a bhith air an ath-riochdachadh beagan eadar-dhealaichte air sgàth neo-stuthachadh bunaiteach CTE. Faodar an giùlan seo a thoirt air ais le bhith a 'sònrachadh an iuchair MATERIALIZED.

Tòrr “fìrinn” ann am briathrachas cuibhrichte

Gabhaidh sinn gnìomh tagraidh fìor - feumaidh sinn liosta a thaisbeanadh teachdaireachdan a tha a’ tighinn a-steach no gnìomhan gnìomhach le luchd-cuiridh:

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

Anns an t-saoghal eas-chruthach, bu chòir ùghdaran gnìomh a bhith air an sgaoileadh gu cothromach am measg luchd-obrach na buidhne againn, ach ann an da-rìribh tha gnìomhan a 'tighinn, mar riaghailt, bho àireamh gu math cuingealaichte de dhaoine - “bho riaghladh” suas an rangachd no “bho fho-chunnradairean” bho roinnean faisg air làimh (luchd-anailis, luchd-dealbhaidh, margaidheachd, ...).

Gabhaidh sinn ris anns a’ bhuidheann againn de 1000 neach, nach eil ach 20 ùghdar (mar as trice eadhon nas lugha) a’ suidheachadh ghnìomhan airson gach cluicheadair sònraichte agus Cleachdamaid an eòlas cuspair seogus a’ cheist “traidiseanta” a luathachadh.

Gineadair sgriobt

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

Nach seall sinn na 100 gnìomh mu dheireadh airson neach-tiomnaidh sònraichte:

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;

[sealladh aig explain.tensor.ru]

A rèir coltais, tha sin 1/3 ùine iomlan agus 3/4 leughaidhean chaidh duilleagan dàta a dhèanamh a-mhàin gus an t-ùghdar a lorg 100 uair - airson gach gnìomh toraidh. Ach tha fios againn air sin am measg nan ceudan sin dìreach 20 eadar-dhealaichte - A bheil e comasach an t-eòlas seo a chleachdadh?

hstore-dictionary

Gabhamaid brath seòrsa hstore gus prìomh luach “faclair” a ghineadh:

CREATE EXTENSION hstore

Feumaidh sinn dìreach ID an ùghdair agus ainm a chuir anns an fhaclair gus an urrainn dhuinn an uairsin tarraing a-mach leis an iuchair seo:

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

[sealladh aig explain.tensor.ru]

Air a chosg air fiosrachadh fhaighinn mu dhaoine 2 uair nas lugha de ùine agus 7 tursan nas lugha de dhàta air a leughadh! A bharrachd air “briathrachas”, b’ e an rud a chuidich sinn na toraidhean sin a choileanadh ath-chlàradh mòr-chlàran bhon chlàr ann an aon pas a’ cleachdadh = ANY(ARRAY(...)).

Inntrigeadh Clàr: Serialization agus Deserialization

Ach dè ma dh’fheumas sinn chan e a-mhàin aon raon teacsa a shàbhaladh, ach inntrigeadh slàn san fhaclair? Anns a 'chùis seo, cuidichidh comas PostgreSQL sinn làimhseachadh clàr a-steach mar aon luach:

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

Bheir sinn sùil air na bha a’ dol an seo:

Ghabh sinn p mar alias don inntrigeadh clàr làn neach agus chruinnich e sreath diubh.
seo chaidh an raon de chlàran ath-dhealbhadh gu sreath de shreathan teacsa (neach [] :: text []) gus a chuir ann am faclair hstore mar raon luachan.
Nuair a gheibh sinn clàr co-cheangailte, bidh sinn air a tharraing bhon fhaclair le iuchair mar sreang teacsa.
Feumaidh sinn teacsa tionndaidh gu luach seòrsa clàr neach (airson gach clàr thèid seòrsa den aon ainm a chruthachadh gu fèin-ghluasadach).
“Leudaich” an clàr clò-sgrìobhte gu colbhan a’ cleachdadh (...).*.

faclair json

Ach chan obraich a leithid de chleas mar a chuir sinn an sàs gu h-àrd mura h-eil seòrsa bùird co-fhreagarrach ann airson an “tilgeadh”. Dìreach èiridh an aon suidheachadh, agus ma dh'fheuchas sinn ri chleachdadh sreath CTE, chan e clàr “fìor”..

Anns a 'chùis seo cuidichidh iad sinn gnìomhan airson a bhith ag obair le 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;

Bu chòir a thoirt fa-near, nuair a tha sinn a 'toirt cunntas air an structar targaid, chan urrainn dhuinn liosta a dhèanamh de na raointean uile den t-sreang stòr, ach dìreach an fheadhainn a tha a dhìth oirnn. Ma tha clàr “dùthchasach” againn, tha e nas fheàrr an gnìomh a chleachdadh json_populate_record.

Gheibh sinn cothrom air an fhaclair aon turas fhathast, ach json-[de]tha cosgaisean sreathachaidh gu math àrd, mar sin, tha e reusanta an dòigh seo a chleachdadh a-mhàin ann an cuid de chùisean nuair a tha an “onarach” CTE Scan a’ nochdadh fhèin nas miosa.

Dèan deuchainn air coileanadh

Mar sin, fhuair sinn dà dhòigh air dàta a chur ann am faclair - hstore/json_object. A bharrachd air an sin, faodar na h-innealan iuchraichean agus luachan fhèin a chruthachadh ann an dà dhòigh, le tionndadh a-staigh no a-muigh gu teacsa: array_agg(i:: text) / array_agg(i):: text[].

Feuch an dèan sinn sgrùdadh air èifeachdas diofar sheòrsaichean sreathachaidh a’ cleachdadh eisimpleir a tha dìreach synthetigeach - serialize diofar àireamhan de iuchraichean:

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

Sgriobt measaidh: 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;

Air PostgreSQL 11, suas ri timcheall air meud faclair de iuchraichean 2^12 bheir sreathachadh gu json nas lugha de ùine. Anns a 'chùis seo, is e am fear as èifeachdaiche am measgachadh de json_object agus tionndadh seòrsa "a-staigh". array_agg(i::text).

A-nis feuchaidh sinn ri luach gach iuchair a leughadh 8 tursan - às deidh a h-uile càil, mura faigh thu cothrom air an fhaclair, carson a tha feum air?

Sgriobt measaidh: leughadh bho fhaclair

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;

Agus ... mu thràth timcheall air le iuchraichean 2^6, bidh leughadh bho fhaclair json a’ tòiseachadh a’ call iomadh uair leughadh bho hstore, airson jsonb tha an aon rud a 'tachairt aig 2^9.

Co-dhùnaidhean deireannach:

ma dh'fheumas tu a dhèanamh Thig còmhla ri grunn chlàran ath-aithris - tha e nas fheàrr “dictionary” a’ bhùird a chleachdadh

ma tha dùil ris an fhaclair agad beag agus cha leugh thu mòran uaith - faodaidh tu json[b] a chleachdadh

anns a h-uile cùis eile hstore + array_agg(i:: text) bidh e nas èifeachdaiche

Source: www.habr.com

Antipatterns PostgreSQL: bhuail sinn an trom Thig còmhla ri faclair

Tòrr “fìrinn” ann am briathrachas cuibhrichte

hstore-dictionary

Inntrigeadh Clàr: Serialization agus Deserialization

faclair json

Dèan deuchainn air coileanadh

Cuir beachd ann freagairt a chur dheth