PostgreSQL Antipatterns: tiyeni tigwire JOIN yolemera ndi mtanthauzira mawu

Timapitiliza nkhani zotsatiridwa pophunzira za njira zodziwika bwino zowongolera magwiridwe antchito a mafunso "owoneka ngati osavuta" a PostgreSQL:

• mbiri yosowa idzafika pakati pa JOIN
• Sisyphean JOIN arrays
• zovulaza JOIN ndi OR
• CTE JOIN CTE

Musaganize kuti sindimakonda JOIN kwambiri ... :)

Koma nthawi zambiri popanda izo, pempho limakhala lopindulitsa kwambiri kuposa nalo. Ndiye lero tiyesa chotsani JOIN yogwiritsa ntchito kwambiri - kugwiritsa ntchito mtanthauzira mawu.

Kuyambira ndi PostgreSQL 12, zina mwazinthu zomwe zafotokozedwa pansipa zitha kupangidwanso mosiyana chifukwa cha CTE yosasinthika yopanda zinthu. Khalidweli litha kubwezeretsedwanso pofotokoza fungulo MATERIALIZED.

Zambiri "zowona" m'mawu ochepa

Tiyeni titenge ntchito yeniyeni yogwiritsira ntchito - tiyenera kusonyeza mndandanda mauthenga obwera kapena ntchito zogwira ntchito ndi otumiza:

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

M'dziko losamvetsetseka, olemba ntchito ayenera kugawidwa mofanana pakati pa antchito onse a bungwe lathu, koma zenizeni ntchito zimabwera, monga lamulo, kuchokera kwa anthu ochepa - "kuchokera kwa oyang'anira" kapena "kuchokera kwa ma contract ang'onoang'ono" ochokera kumadipatimenti oyandikana nawo (openda, okonza mapulani, otsatsa, ...).

Tiyeni tivomereze kuti m'gulu lathu la anthu 1000, olemba 20 okha (nthawi zambiri ngakhale ochepera) amayika ntchito kwa woimba aliyense komanso Tiyeni tigwiritse ntchito chidziwitso cha phunzirolikufulumizitsa funso "zachikhalidwe".

Script jenereta

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

Tiyeni tiwonetse ntchito 100 zomaliza za wotsogolera wina:

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;

[onani pa explain.tensor.ru]

Izo zikutanthauza kuti 1/3 nthawi yonse ndi kuwerenga 3/4 masamba a deta adapangidwa kuti afufuze wolemba nthawi 100 - pa ntchito iliyonse yotulutsa. Koma ife tikudziwa kuti pakati pa mazana awa 20 okha osiyana - Kodi ndizotheka kugwiritsa ntchito chidziwitsochi?

hstore-dikishonale

Tiyeni titengerepo mwayi hstore mtundu kupanga "dictionary" chinsinsi:

CREATE EXTENSION hstore

Timangofunika kuyika ID ya wolemba ndi dzina lake mumtanthauzira mawu kuti titha kuchotsa pogwiritsa ntchito 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;

[onani pa explain.tensor.ru]

Anathera kufunafuna zambiri za anthu 2 nthawi yocheperako komanso nthawi 7 zochepa zomwe zimawerengedwa! Kuphatikiza pa "mawu", zomwe zidatithandizanso kukwaniritsa zotsatirazi zinali kubweza zolemba zambiri kuchokera patebulo mu chiphaso chimodzi pogwiritsa ntchito = ANY(ARRAY(...)).

Zolemba patebulo: Kusamutsa ndi Kuchotsa

Koma bwanji ngati sitiyenera kusunga gawo limodzi lokha, koma zolemba zonse mumtanthauzira mawu? Pankhaniyi, kuthekera kwa PostgreSQL kudzatithandiza sungani cholembera patebulo ngati mtengo umodzi:

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

Tiyeni tiwone zomwe zinali kuchitika apa:

1. Tinatenga p ngati dzina lolowera patebulo la munthu wathunthu nasonkhanitsa unyinji wa iwo.
2. izi zojambulira zidasinthidwanso ku mndandanda wa zingwe zolembedwa (munthu[]::malemba[]) kuti aziyika mu dikishonale ya hstore ngati mndandanda wamakhalidwe.
3. Tikalandira mbiri yokhudzana ndi izi, ife kutulutsa mtanthauzira mawu ndi kiyi ngati chingwe cholemba.
4. Tikufuna malemba sinthani kukhala mtengo wamtundu wa tebulo munthu (pa tebulo lililonse mtundu wa dzina lomwelo limapangidwa zokha).
5. "Onjezani" zolemba zotayidwa kukhala mizati pogwiritsa ntchito (...).*.

json dikishonale

Koma chinyengo chotere monga momwe tagwiritsira ntchito pamwambapa sichingagwire ntchito ngati palibe mtundu wa tebulo womwe ungachite "kuponya". Ndendende mkhalidwe womwewo udzauka, ndipo ngati tiyesera kugwiritsa ntchito mzere wa CTE, osati tebulo "weniweni"..

Pamenepa adzatithandiza ntchito zogwirira ntchito ndi 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;

Tikumbukenso kuti pofotokoza ndondomeko chandamale, sitingathe kulemba minda yonse ya gwero chingwe, koma okhawo amene timafuna kwenikweni. Ngati tili ndi tebulo la "mbadwa", ndiye kuti ndi bwino kugwiritsa ntchito ntchitoyi json_populate_record.

Timapezabe mtanthauzira mawu kamodzi, koma json- [de] mitengo ya serialization ndiyokwera kwambiri, choncho, ndizomveka kugwiritsa ntchito njirayi pokhapokha ngati "woona mtima" CTE Scan ikudziwonetsera yokha.

Kuyeserera

Chifukwa chake, tili ndi njira ziwiri zosinthira deta kukhala mtanthauzira mawu - hstore/json_object. Kuphatikiza apo, masanjidwe a makiyi ndi zikhalidwe zawo zitha kupangidwanso m'njira ziwiri, ndikusintha kwamkati kapena kwakunja kukhala mawu: array_agg(i::malemba) / array_agg(i)::malemba[].

Tiyeni tiwone momwe mitundu yosiyanasiyana ya serialization imathandizira pogwiritsa ntchito chitsanzo chopangidwa - sungani makiyi osiyanasiyana:

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

Script yowunika: 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;

Pa PostgreSQL 11, mpaka pafupifupi kukula kwa mtanthauzira mawu wa 2^12 makiyi kusindikiza ku json kumatenga nthawi yochepa. Pankhaniyi, chothandiza kwambiri ndi kuphatikiza kwa json_object ndi kutembenuka kwa mtundu wa "mkati". array_agg(i::text).

Tsopano tiyeni tiyese kuwerenga mtengo wa kiyi iliyonse nthawi 8 - pambuyo pake, ngati simukupeza mtanthauzira mawu, ndiye chifukwa chiyani ikufunika?

Zolemba zowunika: kuwerenga kuchokera mudikishonale

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;

Ndipo ... kale pafupifupi ndi 2^6 makiyi, kuwerenga kuchokera mu dikishonale ya json kumayamba kutaya kangapo kuwerenga kuchokera ku hstore, chifukwa jsonb zomwezo zimachitika pa 2^9.

Zotsatira zomaliza:

• ngati muyenera kuchita LOWANI ndi zolemba zambiri zobwereza - Ndi bwino kugwiritsa ntchito "dictionary" ya tebulo
• ngati dikishonale yanu ikuyembekezeka zazing'ono ndipo simudzawerenga zambiri kuchokera pamenepo - mutha kugwiritsa ntchito json[b]
• muzochitika zina zonse hstore + array_agg(i::mawu) zidzakhala zothandiza kwambiri

Source: www.habr.com